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

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

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

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

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

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

Jerry, that works also. Thanks a bunch!!

#### Posting Permissions

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