Results 1 to 6 of 6
  1. #1
    New Lounger
    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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%.

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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

Posting Permissions

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