1. ## 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.

Thank you!

2. ## 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%.

3. ## 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.

4. ## 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>

5. ## 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.

6. ## 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.

#### Posting Permissions

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