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

[2dejulio]

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

 

[2dejulio]

figured out how to post the images

 

[Perpa]

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:

[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.
Hope this is helpful.
Perpa

 

[2dejulio]

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]

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]

@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).