# Mulitple IF cell

#### Grinch

##### New Member
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### T. Valko

##### Well-known Member
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

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

#### Grinch

##### New Member
if less than 4.9 then 0

or b7*0

#### T. Valko

##### Well-known Member
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

##### Well-known Member
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))))

Thanks!!!!!

#### Marcelo Branco

##### MrExcel MVP
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

##### Well-known Member

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?

#### Marcelo Branco

##### MrExcel MVP
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.

All the best

M.

Last edited:

Replies
0
Views
156
Replies
1
Views
456
Replies
7
Views
205
Replies
4
Views
323
Replies
3
Views
105

1,191,559
Messages
5,987,290
Members
440,089
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back