I need to write an IF statement that will tell me the following:
If A1 is = 1 but <2, return 92.5%
If A1 is = 2 but <3, return 95%
If A1 is =3, but <4, return 97.5%
If A1 is 4 return 100%

Can someone please help?

Nested IF statements formula

How the formula works

1. The first IF statement checks if A1 is greater than or equal to 1. If it is, then it moves on to the next IF statement. If it's not, then it returns an empty cell ("").
2. The second IF statement checks if A1 is less than 2. If it is, then it returns 92.5% (which is 0.925 as a decimal). If it's not, then it moves on to the next IF statement.
3. The third IF statement checks if A1 is less than 3. If it is, then it returns 95% (which is 0.95 as a decimal). If it's not, then it moves on to the next IF statement.
4. The fourth IF statement checks if A1 is less than 4. If it is, then it returns 97.5% (which is 0.975 as a decimal). If it's not, then it returns 100% (which is 1 as a decimal).

So, if A1 is 1.5, the formula will return 92.5%. If A1 is 2.5, the formula will return 95%. If A1 is 3.5, the formula will return 97.5%. And if A1 is 4.5 or greater, the formula will return 100%.

Greater then *OR equal* to 4 returns 100:

=LOOKUP(A1,{0,1,2,3,4;0,92.5,95,97.5,100})
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"charlie" wrote in message
...
I need to write an IF statement that will tell me the following:
If A1 is = 1 but <2, return 92.5%
If A1 is = 2 but <3, return 95%
If A1 is =3, but <4, return 97.5%
If A1 is 4 return 100%

Can someone please help?

just start at the top. Untested

=if(a14,1,if(a1=3,.975,if(a1=2,.95,if(a1=1,.92 5,"")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"charlie" wrote in message
...
I need to write an IF statement that will tell me the following:
If A1 is = 1 but <2, return 92.5%
If A1 is = 2 but <3, return 95%
If A1 is =3, but <4, return 97.5%
If A1 is 4 return 100%

Can someone please help?

One way:

=IF(COUNT(A1),IF(A1=4,100%,IF(A1=3,97.5%,IF(A1= 2,95%,IF(A1=1,92.5%,"")))),"")

Format as Percentage 1 decimal place.

--
Biff
Microsoft Excel MVP


"charlie" wrote in message
...
I need to write an IF statement that will tell me the following:
If A1 is = 1 but <2, return 92.5%
If A1 is = 2 but <3, return 95%
If A1 is =3, but <4, return 97.5%
If A1 is 4 return 100%

Can someone please help?

That's exactly what I needed!!! Thanks so much!

"Don Guillett" wrote:


just start at the top. Untested

=if(a14,1,if(a1=3,.975,if(a1=2,.95,if(a1=1,.92 5,"")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"charlie" wrote in message
...
I need to write an IF statement that will tell me the following:
If A1 is = 1 but <2, return 92.5%
If A1 is = 2 but <3, return 95%
If A1 is =3, but <4, return 97.5%
If A1 is 4 return 100%

Can someone please help?

You didn't say what you want to happen if A1 is less than 1. Assuming you
can't have such values, this formula will work for the numbers you posted...

=0.9+MIN(0.025 * INT(A1),1)

where you would format the cell as Percentage. If numbers less than 1 are
possible and you want such values to be 0, then use this formula instead...

=MAX(0,0.9+MIN(0.025 * INT(A1),1))

where you can replace the first 0 with whatever fractional value less than
0.925 that you want.

--
Rick (MVP - Excel)


"charlie" wrote in message
...
I need to write an IF statement that will tell me the following:
If A1 is = 1 but <2, return 92.5%
If A1 is = 2 but <3, return 95%
If A1 is =3, but <4, return 97.5%
If A1 is 4 return 100%

Can someone please help?