Formula solution

[dantheram]

I need help with a formula:

i have column C filled with a identifyer which changes as you scroll down. For example rows 2 through to 20 maybe mnt001 and rows 21 through to 40 maybe mnt0002.

In column O i have a formula that ranks the indentifyer amongst the other mnt001 values or other mnt002 values in column C.

The problem:

When the mnt value changes, mnt001 to mnt002 for example, my formula will not recognise this and, thus, rank the value against the array for the wrong mnt value. Is there a way i can get the rank array to move depending on changing mnt values.

Current formula:

=+RANK(N2,$N$2:$N$42,1)

Where N2:N42 needs to change dependant on value of N2.

Thanks

 

[dantheram]

If you look at the formula, it simply calculates how many cells in a certain category are larger than the current cell:

=SUMPRODUCT(--($C$2:$C$42=C2)*--($N$2:$N$42>N2))+1

broken down:
$C$2:$C$42=C2 - where the category is the same as the one for current row
$N$2:$N$42>N2 - generate TRUE where the corresponding values are greater than current row value ( ALTER THIS TO BE < TO RANK TO BE 1 FOR SMALLEST )
the formula uses -- to coerce the TRUEs to be 1, and the FALSEs to be zero, and does an array multiply to get the answer.

I hope that helps.

That really does help a lot!

i'm loathed to include any formula i don't understand because it really does not help when you wnat to make changes.

Many thanks again,

Dan