Mulitple IF cell

[Grinch]

Here's my deal. Here's what I want but don't know how to formulate it.

If E7 is greater than 4.9, then B7 * .4
If E7 is greater than 5.9, then B7 * .6
If E7 is greater than 6.9, then B7 * .8
If E7 is greater than 7.9, then B7 * 1

Thanks in advance for your help... I'm losing my mind.

 

[T. Valko]

Here's my deal. Here's what I want but don't know how to formulate it.

If E7 is greater than 4.9, then B7 * .4
If E7 is greater than 5.9, then B7 * .6
If E7 is greater than 6.9, then B7 * .8
If E7 is greater than 7.9, then B7 * 1

Thanks in advance for your help... I'm losing my mind.

What should happen if E7 is less than or equal to 4.9?

 

[Grinch]

if less than 4.9 then 0

 

[Grinch]

or b7*0

 

[T. Valko]

or b7*0

Here's one way...

=B7*IF(E7>7.9,1,IF(E7>6.9,0.8,IF(E7>5.9,0.6,IF(E7>4.9,0.4,0))))

 

[pedie]

not sure but try something like this...
'=IF(E7<5.9,B7*0.4,IF(AND(E7>5.9,E7<6.9),B7*0.6,IF(AND(E7>6.9,E7<7.9),B7*0.8,IF(E7>7.9,B7*1,0))))

 

[Grinch]

Thanks!!!!!

 

[Marcelo Branco]

Hi,

I'm a little late for this party , but maybe this

=B7*LOOKUP(E7,{-9.99999999999999E+307;5;6;7;8},{0;0.4;0.6;0.8;1})


M.

 

[pedie]

Hi,

I'm a little late for this party , but maybe this

=B7*LOOKUP(E7,{-9.99999999999999E+307;5;6;7;8},{0;0.4;0.6;0.8;1})


M.

Hey Marcelo, was looking around and got caught up with your formula...

Could you please explain the concept on how this formula works?
Thanks in advance.

 

[Marcelo Branco]

Hi Pedie,

I'm trying to do a simple LOOKUP.

The 1st vector is where the lookup_value (E7) match a lower or equal value and second vector is the result_vector.

I put the first value as -9.99999999999999E+307 because this is smallest value Excel can handle. So if E7, for example, is - 10000 it would match with the first instance and pick 0 (zero) in the result_vector.

And so on for other values. For example 7.9 (a limit value) matches with 7 (lower than 7.9) that is the 4th element and returns the corresponding 4th element of the result_vector = 0.8.

Hope i made myself clear.

All the best

M.