Need a formula

[pattigail1960]

I need a formula for the following example:
If A1 = 0.01 to 4.00, then B1 = 0.5
If A1 = 4.01 to 8.0, then B1 = 1.0
If A1 = 8.01 to 12.0, then B1 = 1.5
If A1 = 12.01 to 16.0, then B1 = 2.0
If A1 = 16.01 to 20.0, then B1 = 2.5
If A1 = 20.01 to 24.0, then B1 = 3.0
If A1 = 24.01 to 28.0, then B1 = 3.5
If A1 = 28.01 to 32.0, then B1 = 4.0
If A1 = 32.01 to 35.99, then B1 = 4.5
If A1 = 36.0 or greater, then B1 = 5.0

Thank you to anyone who can help - I am not proficient enough w/Excel to
even know if that many if/then's can be met.

 

[Ron Rosenfeld]

I need a formula for the following example:
If A1 = 0.01 to 4.00, then B1 = 0.5
If A1 = 4.01 to 8.0, then B1 = 1.0
If A1 = 8.01 to 12.0, then B1 = 1.5
If A1 = 12.01 to 16.0, then B1 = 2.0
If A1 = 16.01 to 20.0, then B1 = 2.5
If A1 = 20.01 to 24.0, then B1 = 3.0
If A1 = 24.01 to 28.0, then B1 = 3.5
If A1 = 28.01 to 32.0, then B1 = 4.0
If A1 = 32.01 to 35.99, then B1 = 4.5
If A1 = 36.0 or greater, then B1 = 5.0

Thank you to anyone who can help - I am not proficient enough w/Excel to
even know if that many if/then's can be met.

You do not specify what you want to occur if A1 < 0.01, or if A1 is between 4
and 4.01, etc.

I made some assumptions, but if you want to specify, you should do so.

Set up a table someplace with these entries:

0 0.5
4 1
8 1.5
12 2
16 2.5
20 3
24 3.5
28 4
32 4.5
36 5

I used the range I1:J10

Then enter this formula:

B1: =IF(A1="","",VLOOKUP(A1,$I$1:$J$10,2))
--ron

 

[T. Valko]

Try this:

=IF(A1>=36,5,INT(CEILING(A1/4,1))/2)

 

[David Biddulph]

You've left a number of ranges undefined, such as <0.01, between 4 and 4.01,
between 8 and 8.01, and so on up to between 32 and 32.01, then strangely
between 35.99 and 36, but guessing at what you might have wanted for some of
those, try
=IF(A1<=0,"undefined",IF(A1>=36,5,CEILING(A1,4)/8))

The answer to your footnote is that you can't use that many nested IFs, at
least in Excel 2003.
Another option worth looking at is VLOOKUP.

 

[Shane Devenshire]

Hi,

In 2007 you could use a nested if, but why bother, since other solutions are
so simple.

Excel 2003 allows 7 level deep nesting
Excel 2007 allows 64 level deep nesting

 

[T. Valko]

=IF(A1>=36,5,INT(CEILING(A1/4,1))/2)

Since we're using the CEILING function the INT function becomes redundant.

=IF(A1>=36,5,CEILING(A1/4,1)/2)

 

[pattigail1960]

That works perfectly! Thank you!

Try this:

=IF(A1>=36,5,INT(CEILING(A1/4,1))/2)

 

[T. Valko]

You're welcome. Thanks for the feedback!