Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If MAX then formula (2003)

    I've attached the sheet I need this formula for - each column named RANK - need to sum the salary and the benefits and display a ranking from 1 to 11, 1 being the highest combined salary and benefits for each step.

    Thanks again!

    Aunt Linda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If MAX then formula (2003)

    You'll have to insert extra columns. See the attached workbook - I've done two of the columns. You can hide the columns with the sums if you like.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If MAX then formula (2003)

    After I posted this question, I found the "rank" function, but couldn't figure it out - as usual. How did you get the absolute references to Column E? I'm sure you used the rank function - help me understand how I should have gone about it. If you didn't use the function, (yes, I'm rambling) did you make the absolute references using F4?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If MAX then formula (2003)

    The formula in F2 is

    =RANK(E2,E$2:E$12)

    I entered the reference by pointing at it; Excel put it in the formula as E2:E12 (i.e. relative references). I wanted the column reference to be relative, so that the formula could easily be copied to other columns, but I wanted the row reference to be absolute, so that the formula would still refer to rows 2 through 12 if I filled down the formula. To this end, I pressed the function key F4 twice - the first time, the reference became $E$2:$E$12, then E$2:E$12. If I had continued pressing F4, it would have become $E2:$E12, then E2:E12 again.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If MAX then formula (2003)

    Thanks - I know about F4 - just wondered if you used the Rank function.

    Again - many thanks.

    Aunt Linda

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If MAX then formula (2003)

    If you select one of the cells in F2:F12, you'll see the formula in the formula bar. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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