Results 1 to 6 of 6
Thread: Conditional formula (2000)

20040303, 22:59 #1
 Join Date
 Jul 2003
 Location
 Washington, District Of Columbia
 Posts
 17
 Thanks
 0
 Thanked 0 Times in 0 Posts
Conditional formula (2000)
From a list of items having different values, I am attempting to create a formula that will allow me return a value (100) for the item having the maximum value while at the same time, dividing the other values in the list by the maximum value.
Please help so I can get home in time to take in a run!
Thank you!

20040303, 23:07 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Conditional formula (2000)
Say that your list is in A1:A10. In cell B1, enter this formula:
=A1/MAX($A$1:$A$10)
and format B1 as a percentage. Next, fill down from B1 to B10. For the cell in A1:A10 containing the maximum value, the corresponding cell in B1:B10 will contain the value 1, displayed as 100%.

20040304, 16:45 #3
 Join Date
 Jul 2003
 Location
 Washington, District Of Columbia
 Posts
 17
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Nested Function (2000)
Thank you, Hans. It worked.
Here's another part of my great learning opportunity, very readily applied to work. I believe the solution to this problem is a nested function.
From an item with an assigned value in miles, I want to assign a score of 1, if the distance is less than or equal to 50 miles; a score of .5, if the distance is between 51 to 75 miles; etc. (a total of 4 separate scores).
Thank you.

20040304, 17:01 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Nested Function (2000)
You could do it with an IF, but in this case I would tend to use a Lookup (You didn't detail in all the numbers):
It makes updating easier, can have more than 7 values, and the table can be hidden on a separate sheet if desired.
Steve
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom></td><td align=right valign=bottom>75</td><td valign=bottom>=VLOOKUP(D1,$A$1:$B$4,2)</td><td align=center valign=bottom>2</td><td align=right valign=bottom>51</td><td align=right valign=bottom>0.5</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>3</td><td align=right valign=bottom>76</td><td valign=bottom>??</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>4</td><td valign=bottom>??</td><td valign=bottom>??</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td></table>

20040304, 17:10 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Nested Function (2000)
You can use nested functions, but the formula soon becomes unwieldy, and there is a limit to the number of nested functions. With the distance in A1, this would be the nested formula (to be adapted):
=IF(A1<=50,1,IF(A1<=75,0.5,IF(A1<=100,0.25,0.1)))
For a large number of categories, you can use a small auxiliary table and a combination of INDEX and MATCH. See attached workbook.

20040304, 18:17 #6
 Join Date
 Jul 2003
 Location
 Washington, District Of Columbia
 Posts
 17
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Nested Function (2000)
Thanks Hans and Steve,
I used the nested function since I only had four arguments. It worked well. I have used the lookup capability before, but not the index/match combo. I'm sure I will have a requirement for the ladder down the line.
Thank you both for the great and invaluable assistance.
Have a great day.