Results 1 to 6 of 6
Thread: If MAX then formula (2003)

20060216, 21:07 #1
 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

20060216, 21:41 #2
 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.

20060216, 21:48 #3
 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?

20060216, 21:55 #4
 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.

20060216, 22:07 #5
 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

20060216, 22:11 #6
 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>