Nested Greater than or less than if statements

  • Thread starter Thread starter charlie
  • Start date Start date
C

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?
 
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?
 
just start at the top. Untested

=if(a1>4,1,if(a1>=3,.975,if(a1>=2,.95,if(a1>=1,.925,"")))
 
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.
 
That's exactly what I needed!!! Thanks so much!

Don Guillett said:
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)
Click to expand...
 
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.
 
Back
Top