Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested IF Statement With Percents (Office 2000-2003)

    I'm trying to write an IF statement that will enter an amount based on the percent in another cell. What is confusing me is how to say "between" in an IF statement. For instance, if cell B2 is between 95-100% the value returned would be $300. Also, I'm not very good at doing nested IF statements and I think that is what is needed. Below is the table of percent ranges and their values. Any help would be greatly appreciated.

    Thanks
    95-100% $300
    90-94.9%
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IF Statement With Percents (Office 2000

    Hi Sherry

    A VLOOKUP table is really the answer here. Are you familiar with them? If not, post back.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  3. #3
    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 IF Statement With Percents (Office 2000-2003)

    =IF(B2>=95%,300, IF(B2>=90%,250,IF(B2>=85%,200,IF(B2>=80%,150,0))))

    This could also be done with Vlookup and having that table in a range. I find these easier to maintain. For example if you have the table"

    <table border=1><td></td><td align=center>I</td><td align=center>J</td><td align=center valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom>2</td><td align=right valign=bottom>80%</td><td align=right valign=bottom>150</td><td align=center valign=bottom>3</td><td align=right valign=bottom>85%</td><td align=right valign=bottom>200</td><td align=center valign=bottom>4</td><td align=right valign=bottom>90%</td><td align=right valign=bottom>250</td><td align=center valign=bottom>5</td><td align=right valign=bottom>95%</td><td align=right valign=bottom>300</td></table>

    You could use:
    =VLOOKUP(B2,$I$1:$J$5,2)

    The lookup table will also not have the 7 nested IF limitation if the list gets longer...

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Nested IF Statement With Percents (Office 2000-2003)

    Sherry


    Is this what you are looking for (assuming the value is in A1):

    =if(A1>=95,300,if(A1>=90,250,if(A1>=85,200,if(A1>= 80,150,0))))
    Jerry

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IF Statement With Percents (Office 2000-2003)

    That works! Thanks Steve!! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IF Statement With Percents (Office 2000-2003)

    Jerry, that works also. Thanks a bunch!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

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