Results 1 to 1 of 1
2009-01-22, 17:33 #1
- Join Date
- Feb 2001
- Yilgarn region of Toronto, Ontario
- Thanked 0 Times in 0 Posts
Fx using non-adjacent arguments (Excel 2003 et al)
(later) while composing this message I tried parentheses.
This seems to do the trick!
This would drive me crazy were I not already so:
I can't work out how to specify a set on non-adjacent cells when using the Function-Picker ("fx" tool).
The attached workbook contains two blocks of data.
In the first block I defined a range "Rank1" and assigned three cells to that name.
I use the Fx tool and the F3 function key to obtain the range name, and as you can see, the cells in column E reflect the ranking of the three (fake) subtotals.
I need to generate RANK function calls for many more blocks of data, (programmatically in VBA) without using range names.
In the second block of data, I select cell E20.
I use the Fx tool and call up RANK.
For the Number argument I click on cell D20, for that contains the next data value I'd like to rank.
For the Ref argument I use Ctrl-Click to select each one of the cells D20,D27,D32,D39,D44 in turn.
(I want to know how D20 ranks amongst its fellow "subtotals")
The generated formula is in error: =RANK(D20,D20,D27,D32,D39,D44)
I've tried various separators with no success - colons, semi-colons, ...