Ranking data in columns (non-consecutive cells) 1

[Cordury2]

I have a summary tab with subtotals in every 5th cell. I have a very simple formula of:

=rank(I20,$I$20,$I$25,$I$30,$I$35,$I$40,$I$45,1)

And I keep getting an error, will Excel not allow a user to use the rank formula in non adjacent cells?

Thanks,

 

[byundt]

Try creating a named range for I20, I25, I30 etc. You can then use this named range in the RANK function.

1) Open the Insert...Name...Define menu item
2) Enter a name like MySubtotals in the the "Names in workbook" field
3) Enter a formula like the following in the "Refers to" field
=$I$20,$I$25,$I$30,$I$35,$I$40,$I$45

You may now use the formula:
=RANK(I20,MySubtotals,1)

Brad

 

[Cordury2]

Brad,

Thanks for the tip, it worked perfectly!

Thanks again,
Cord