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

 

[GlennUK]

Try this formula:

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

in cell O2, copied down.

 

[dantheram]

Try this formula:

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

in cell O2, copied down.

i'm not entirely sure what this does but it doesn't work.

i need the ranking to change as the data in column c does.

for example:

c d e
001a 1 001a
001b 4 001c
001c 2 001b
002a 1 002a
002b 4 002b
002c 5 002c
003a 1 003a
003b 7 003c
003c 6 003b

where column e contains a rank formula (ranking value in d) which is dynamic based on what is in column c

 

[GlennUK]

All of the data items in column C are unique ... how do expect the ranking to work?

 

[dantheram]

i'm not entirely sure what this does but it doesn't work.

i need the ranking to change as the data in column c does.

for example:

c d e
001 1 1
001 4 2
001 2 4
002 1 1
002 4 4
002 5 5
003 1 1
003 7 6
003 6 7

where column e contains a rank formula (ranking value in d) which is dynamic based on what is in column c

sorry, i got distracted when i wrote out the above, i have now edited it.

 

[GlennUK]

You say that you used my formula ... well, that would have worked, assuming you changed the ranges accordingly. Show your version of my formula.

 

[dantheram]

You say that you used my formula ... well, that would have worked, assuming you changed the ranges accordingly. Show your version of my formula.

And their within lies the problem, your formual is perfect if your data set is small.

What i should have made a little clearer is that i need the range to change when the value in column c changes - automatically. Because to manually put in the ranges would take ages.

apologies

 

[GlennUK]

And I say that my formula ranks according to the value in column C regardless of range ... you specify the full range in the formula, and it does the calculations automatically ... that's what you asked for, and that's what I provided. Please try it.

 

[dantheram]

And I say that my formula ranks according to the value in column C regardless of range ... you specify the full range in the formula, and it does the calculations automatically ... that's what you asked for, and that's what I provided. Please try it.

you are entirely right! as i didn't understand the formula i couldn't deploy it correctly.

Can you tell me how it works so i can use it in the future, please?

Also, is it possible to rank the numbers with lowest ranked at 1 instead of highest?

once again, many thanks!

 

[GlennUK]

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.