# Need some help with an IF THEN statement (nested) please

#### 2dejulio

##### New Member
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000; min-height: 15.0px}</style>I need help in writing an if then statement in Excel. My first time trying and not having any success.

I’m building an expense summary where a vendors cost will be based off how much my company spends on advertising.

If the spend (line 31 in the spend_cost image) falls between the range in the table (line 24-27 in the rate_table image), the cost will be reflected in cells G21 through GXX (in the spend_cost image).

https://www.dropbox.com/s/xeshyavokjem9xy/rate_table.jpg?dl=0

https://www.dropbox.com/s/7zc7uvk0p88u5hd/spend_cost.jpg?dl=0

I’ve been battling this for hours and making zero progress. For better or worse, I’m working on a mac which doesn’t appear to have the capability to use and ‘IFS’ statement.

Any help would be hugely appreciated!

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### 2dejulio

##### New Member
figured out how to post the images

#### Perpa

##### Well-known Member
figured out how to post the images

2dejulio,
Welcome to the forum. The following formula uses 4 IF statements.
Not sure where you want to put the result, but for the sake of discussion I assumed it would be in cell C32:
Code:
``[FONT=Calibri][SIZE=3][COLOR=#000000]=if(C\$31<=16499,2495,if(and(C\$31>16499,C\$31<=24999),C\$31*.1, if(and(C\$31>24999,C\$31<=74999),C\$31*.125,if(and(C\$31>74999,C\$31<=124999),C\$31*.1,"\$124999 Exceeded"))))[/COLOR][/SIZE][/FONT]``
Then just copy the formula across row 32. If Mac can't use nested IFs, then you can stack each IF in the same column, ie. cell C32 =if(C\$31<=16499,2495,"") where the "" leaves the cell blank, and the next IF cell C33= if(and(C\$31>16499,C\$31<=24999),C\$31*.1,""), and so on.

Perpa

#### 2dejulio

##### New Member
Thanks Perpa!

When I plug this in it doesn't appear to recognize anything past the first statement (returns the '2495' value to each cell).

#### Perpa

##### Well-known Member
Thanks Perpa!

When I plug this in it doesn't appear to recognize anything past the first statement (returns the '2495' value to each cell).

2dejulio,
I'm no Mac guru...but you might try my second suggestion...
Enter the formulae in the specified cells and copy across.

cell C32=if(C\$31<=16499,2495,"") cell C33 =if(and(C\$31>16499,C\$31<=24999),C\$31*.1,"")
 cell C33 = if(and(C\$31>16499,C\$31<=24999),C\$31*.1,"")

<tbody>
</tbody>

cell C34=if(and(C\$31>24999,C\$31<=74999),C\$31*.125,"")
cell C35=if(and(C\$31>74999,C\$31<=124999),C\$31*.1,"")

Good luck!

Perpa
<o></o>
Excel 2007
B
C
D
E
F
G
H
I
J
K
L
30
???????
31
Spent
10656
13086
12483
19395
28015
33762
25221
11418
124000
125000
32
2495
2495
2495
2495
33
1939.5
34
3501.875
4220.25
3152.625
35
12400

<tbody>
</tbody>
Sheet1

#### AhoyNC

##### Well-known Member
@2dejulio- All the data in your example for row 31 has a value less than 16499, so the result would be 2495 (or 2475 as per your example).

Replies
1
Views
437
Replies
1
Views
479
Replies
4
Views
678
Replies
2
Views
589
Replies
4
Views
861

1,191,197
Messages
5,985,231
Members
439,952
Latest member
djharter

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