Nested Greater than or less than if statements

[charlie]

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?

 

[RagDyeR]

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!
=====================================================


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?

 

[Don Guillett]

just start at the top. Untested

=if(a1>4,1,if(a1>=3,.975,if(a1>=2,.95,if(a1>=1,.925,"")))

 

[T. Valko]

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.

 

[charlie]

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

just start at the top. Untested

=if(a1>4,1,if(a1>=3,.975,if(a1>=2,.95,if(a1>=1,.925,"")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Rick Rothstein]

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.