Results 1 to 1 of 1
  1. #1
    Platinum Lounger
    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, ...
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts