IF Greater Than/Less Than/Between Formula

[questionking]

I am trying to come up with a way to calculate the cost for a project. There is a sliding scale included with a lower percentage of cost for a higher value. For instance the first range is 0 to 500, so the first 500 would be multiplied by 1% to get a cost of 5, and so on. Starting, Ending, Percent and Value are hard values. Column E is jus the Difference multiplied by the percent. But the Difference column is where I'm having some trouble. My current formula in cell C2 is IF(B7>B2,B2,""). This runs me into trouble later though when that value changes as shown below, from 12,000 to 2,000. It doesn't pick up the 999.99 in cell C12 that I need to get there with the correct formula. Your

1. What do I need to change in my formula to capture this?

2. And is there a way to calculate this in one entire formula in the Cost column? Leaving just the Value, Percent and cost columns and getting rid of the rest?

 

[apgmin]

if cell C2=IF(B7>B2,B2,""), How come C3 shows 499.99, should it now show 1000, presuming the formula there is

=IF(B7>B3,B3,"")​

 

[questionking]

It's a sliding scale similar to taxation. The first 500 is at 1%. The second 500 is at .8% the next 4,000 is at .6% and anything above 5,000 is at .45%.

 

[KRice]

To your first question, try this:

MrExcel_problem-solving.xlsx
	A	B	C	D
1	Range
2	>	<=	MargRate %	MargAmt ($)
3	0	500	1.000%	5.000
4	500	1000	0.800%	4.000
5	1000	5000	0.600%	6.000
6	5000	9.99E+09	0.450%
7
8	Value	2000.00
9	Total cost	15.00
Sheet11

Cell Formulas
Range	Formula
B3:B5	B3	=A4
D3:D6	D3	=IF($B$8>$B3,($B3-$A3)*$C3,IF(($B$8-$A3)>0,($B$8-$A3)*$C3,""))
B9	B9	=SUM($D$3:$D$6)

 

[KRice]

And to your second question, the easiest approach is create a single expression that is the sum all of the formulas found in the cost column (and change the nulls to zeroes)...and then delete the cost column. For convenience, the formula is shown here:

MrExcel_problem-solving.xlsx
	A	B
10	Total cost	64.50
Sheet11

Cell Formulas
Range	Formula
B10	B10	=SUM(IF($B$8>$B3,($B3-$A3)*$C3,IF(($B$8-$A3)>0,($B$8-$A3)*$C3,0)),IF($B$8>$B4,($B4-$A4)*$C4,IF(($B$8-$A4)>0,($B$8-$A4)*$C4,0)),IF($B$8>$B5,($B5-$A5)*$C5,IF(($B$8-$A5)>0,($B$8-$A5)*$C5,0)),IF($B$8>$B6,($B6-$A6)*$C6,IF(($B$8-$A6)>0,($B$8-$A6)*$C6,0)))

 

[MARK858]

2. And is there a way to calculate this in one entire formula in the Cost column? Leaving just the Value, Percent and cost columns and getting rid of the rest?

Just a different method

Book1
	A	B	C
1	Value	12000	2000
2	Total cost	64.5	15
Sheet2

Cell Formulas
Range	Formula
B2:C2	B2	=SUMPRODUCT(--(B1>{0;500;1000;5000}), (B1-{0;500;1000;5000}), {0.01;-0.002;-0.002;-0.0015})

or for the first question

Book1
	A	B	C
1	Range
2	>	MargRate %
3	0	0.01	0.01
4	500	0.008	-0.002
5	1000	0.006	-0.002
6	5000	0.0045	-0.0015
7
8	Value	2000	12000
9	Total cost	15	64.5
Sheet3

Cell Formulas
Range	Formula
C3	C3	=B3
C4:C6	C4	=B4-B3
B9:C9	B9	=SUMPRODUCT(--(B8>$A$3:$A$6), (B8-$A$3:$A$6), $C$3:$C$6)

 

[questionking]

Forgot about sumproduct option, thank you for your help! Saving this for future use!

 

[questionking]

Multiple Ifs I gotcha, thank you for your help, this is very much appreciated and helpful! That makes it a lot cleaner than having numerous columns, and values all over the place like I previously had, thank you again!