If formula greater than a series of numbers

Thank you for viewing my post.

I am trying to calculate a set of If calculation for one cell but can't seem to get it right.

If A1 is greater than 105 through 109.9 then I would like B1 to = 1. If it is greater than 110, then B1 should = 2
=IF(A1>105-109.9,1,IF(A1>110,2))


Thank you,
-Newb

Do it like this in B1:

=IF(A1>=110,2,IF(A1>=105,1,0))

I've assumed you want zero for values of A that are less than 105.

Hope this helps.

Pete

Hello,

Thank you for replying to me. When I put in that formula, I get 2 in column B1 instead of 1. The number is 105%.

Thank you,
Newb

Originally Posted by Pete_UK

Do it like this in B1:

=IF(A1>=110,2,IF(A1>=105,1,0))

I've assumed you want zero for values of A that are less than 105.

Hope this helps.

Pete

---------- Post added at 09:39 AM ---------- Previous post was at 09:35 AM ----------

I tried both formulas, but the results for all cells come out to be 2. Even though a1 is listed as 105.0%, it still says 2.

Originally Posted by NBVC

Try:

=IF(A1>=110,2,IF(A1>=105,1,0)) you can nest more

or

=LOOKUP(A1,{105,110},{1,2}) you can insert more..

Shows #NA.......

if it is less than 105 it should be 0.

Thanks I used another method.

=IF(AND(I21<105%),"0",IF(AND(I21>=105%,I21<=109.9%),"1",IF(AND(I21>=110%),"2",)))

Originally Posted by newbie4

Thanks I used another method.

=IF(AND(I21<105%),"0",IF(AND(I21>=105%,I21<=109.9%),"1",IF(AND(I21>=110%),"2",)))

If Excel gets past the first IF (where your AND is not doing anything), then you don't need to test for I21 being greater than or equal to 105% as you have already tested for it being less than 105%. Similarly, you don't need to test for I21 being greater than or equal to 110%, as you have already tested for it being less than or equal to 109.9%. (Actually, there is thus a small range of between 109.9% and 110% exclusive that you do not test for). So, the following will do the job and be more efficient:

=IF(I21<105%,"0",IF(I21<=110%,"1","2"))

although I suspect you would prefer to have real numbers rather than text representations of numbers, so you can remove the double-quotes as well.

Hope this helps.

Pete