Need formula - fast!

[AlpineResident]

I have been unable to construct a single IF statement to handle these conditions:

IF B3 value >6 return 100%
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
IF B3 Value >6 but < 12 return .66
<o> </o>
IF b3 value >12 but < 24 return .33
<o> </o>
IF B3 value >24 return 0

Please assist. Thanks for your help!

 

[BiocideJ]

I have been unable to construct a single IF statement to handle these conditions:

IF B3 value <=6 return 100%
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
IF B3 Value >6 but <= 12 return .66
<o></o>
IF b3 value >12 but <= 24 return .33
<o></o>
IF B3 value >24 return 0

Please assist. Thanks for your help!

I am assuming you meant the prevoius instead of >6 which would give formula
=IF(B3>24,0,IF(B3>12,0.33,IF(B3>6,0.66,1)))

 

[Jonmo1]

Welcome to the board..

Try

=LOOKUP(B3,{0,6,12,24},{1,0.66,0.33,0})

 

[pkrishna]

I think your first condition should be

IF B3 <6 return 100%

This will do it, but there are surely better ways of doing it.

=IF(B3>24,0,IF(B3>12,0.33,IF(B3>6,0.66,1)))

For the case where B3=6, B3=12, B3=24, it returns values of 100%, 66%, 33% respectively.

You may have to use >=, or <= conditions to fine tune those situations.

 

[Robert Mika]

Slighlty diffrent that jonmo to give .66 >12
=LOOKUP(B3,{0,6,13,25},{1,0.66,0.33,0})

 

[pkrishna]

Yes, this is a better way of doing it. Make sure you type the formula in the cell, and NOT HIT ENTER - but -- HIT CTRL-SHIFT-ENTER

 

[kevinb11]

Assuming you meant B1<6 is 100%

Excel Workbook
	B	C	D
1	3	100%	IF B3 value >6 return 100%
2
3	7	0.66	IF B3 Value >6 but
4
5	15	0.33	IF b3 value >12 but
6
7	30	0	IF B3 value >24 return 0
Sheet4

 

[Jonmo1]

Yes, this is a better way of doing it. Make sure you type the formula in the cell, and NOT HIT ENTER - but -- HIT CTRL-SHIFT-ENTER

The lookup formulas do not require CSE entry.

 

[BiocideJ]

Welcome to the board..

Try

=LOOKUP(B3,{0,6,12,24},{1,0.66,0.33,0})

This is likely the best solution so long as you will never have negative values. That is really the only draw-back in my mind to using Lookups is you have to make sure you supply a first value that is below any possible value in the lookup cell (B3)

I suppose to that end, if you expect you will ever have negative values you could always use this...
=LOOKUP(B3,{-1E+99,6,12,24},{1,0.66,0.33,0})

-1E99 is -100000000.... (99 0's after the one) so basically a ridiculously negative value.

 

[AlpineResident]

Thanks, all!

I'll try the various combinations you suggested. My workaround until I heard from you experts was to use a vlookup table.

I appreciate the quick responses - very impressive!