I need help with a formula: i have column C filled with a identifier 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 identifier 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

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Formula help

Thread starter dantheram
Start date Aug 2, 2011

dantheram

Board Regular

Aug 2, 2011

I need help with a formula:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

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

></o

>
<o

></o

>
In column O i have a formula that ranks the identifier amongst the other mnt001 values or other mnt002 values in column C.<o

></o

>
<o

></o

>
The problem:<o

></o

>
<o

></o

>
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?<o

></o

>
<o

></o

>
Current formula:<o

></o

>
<o

></o

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

></o

>
<o

></o

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