testing for below, between, above in one formula

[JamesPW]

Im trying to write a formula that does one thing if the number is below a low test value, does a second thing if the number is between a low and high test values and a third if the number is above the high test value. It's for an electrical billing formula

	$/kWh	cost@Usage
		40 kWh	80 kWh	319 kWh
Rate first 50 kWh	$0.33	cost1 40@.33=13.2	50@.33=16.5	50@.33=16.5
Rate 51-200 kWh	$0.38	cost2 0@.38=0	80-50=30@.38=11.4	200-50@.38=57
Rate above 200 kWh	$0.43	cost3 0@.43=0	0@.43=0	319-200@.43=51.17
Total	Total=$@rate1+$@rate2+$@rate3	$13.20	$27.90	$124.67

Needs to be a single formula that gives cost1, cost2, cost3 plus total at all levels of usage.
I can use an IF(AND(>50,<200), TRUE,FALSE) will give me the "between" # but not the correct low and high in the same equation.
Thanks in advance

 

[MARK858]

Maybe something like the below

Book3
	A	B	C	D	E
1		$/kWh	cost@Usage
2			40	80	319
3	Rate first 50 kWh	$0.33	cost1 40@.33=13.2	50@.33=16.5	50@.33=16.5
4	Rate 51-200 kWh	$0.38	cost2 0@.38=0	80-50=30@.38=11.4	200-50@.38=57
5	Rate above 200 kWh	$0.43	cost3 0@.43=0	0@.43=0	319-200@.43=51.17
6	Total	Total=$@rate1+$@rate2+$@rate3	$13.20	$27.90	$124.67
Sheet2

Cell Formulas
Range	Formula
C6:E6	C6	=SUMPRODUCT(--(C2>{0;50;200}), (C2-{0;50;200}), {0.33;0.05;0.05})

 

[Bosquedeguate]

The first there rows calculate the Cost with hardcode values.

Book2
	A	B	C	D	E
1	kWh Use	$/kWh
2	40	$13.20
3	80	$27.90
4	319	$124.67
5	Rate first 50 kWh	$0.33	cost1 40@.33=13.2	50@.33=16.5	50@.33=16.5
6	Rate 51-200 kWh	$0.38	cost2 0@.38=0	80-50=30@.38=11.4	200-50@.38=57
7	Rate above 200 kWh	$0.43	cost3 0@.43=0	0@.43=0	319-200@.43=51.17
8			Calculate Cost using hard coded kWH use breakpoints and rates
9	Total	Total=$@rate1+$@rate2+$@rate3	$13.20	$27.90	$124.67
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=IF(A2>=201,(A2-200)*0.43+150*0.34+50*0.33,IF(A2>=51,(A2-50)*0.38+50*0.33,A2*0.33))
B4	B4	=IF(A4>=201,(A4-200)*0.43+150*0.38+50*0.33,IF(A4>=51,(A4-50)*0.38+50*0.33,A4*0.33))

The second example uses the combination of a lookup table and basically the same formula, but using all indexes into the lookup table.
This way if/when the rates change or the kWh usage breakpoint changes your calculation should not change; all you need to do is update the table.

Lookup Tables (named ranges kWhUse and kWhRate)

Book2
	H	I
1	kWh Use	kWh Rate
2	0	0.33
3	51	0.38
4	201	0.43
Sheet1

Book2
	A	B
13	Calc Cost Using Index Lookup of Named Ranges
14	Test Usage	Cost Calc
15	40	13.20
16	51	16.88
17	80	27.90
18	319	124.67
Sheet1

Cell Formulas
Range	Formula
B15:B18	B15	=IF(A15>=INDEX(KWHUse,3),(A15-INDEX(KWHUse,3)+1)*INDEX(KWHRate,3)+(INDEX(KWHUse,3)-INDEX(KWHUse,2))*INDEX(KWHRate,2)+(INDEX(KWHUse,2)-1)*INDEX(KWHRate,1),IF(A15>=INDEX(KWHUse,2),(A15-INDEX(KWHUse,2)+1)*INDEX(KWHRate,2)+(INDEX(KWHUse,2)-1)*INDEX(KWHRate,1),A15*INDEX(KWHRate,1)))

Named Ranges
Name	Refers To	Cells
KWHRate	=Sheet1!$I$2:$I$4	B15:B18
KWHUse	=Sheet1!$H$2:$H$4	B15:B18

Using the formula provided by MARK858 above with a lookup table

Book2
	J	K
1	KWHUse2	kWH Rate Inc
2	0	0.33
3	50	0.05
4	200	0.05
Sheet1

Book2
	A
14	Test Usage
15	40
16	51
17	80
18	319
Sheet1

Book2
	D
14	SumProduct Formula
15	13.20
16	16.88
17	27.90
18	124.67
Sheet1

Cell Formulas
Range	Formula
D15:D18	D15	=SUMPRODUCT(--(A15>=KWHUse2), (A15-KWHUse2), KWHRateInc)

Named Ranges
Name	Refers To	Cells
KWHRateInc	=Sheet1!$K$2:$K$4	D15:D18
KWHUse2	=Sheet1!$J$2:$J$4	D15:D18

 

[MARK858]

If I was using a Lookup rather than hard coding the values then I would use a formula for the differential rate as well, something like the below.
E2:G2 is a number with the custom format of 0" Kwh", C3:C5 and E6:G6 are numbers formatted as currency

Book3
	A	B	C	D	E	F	G
1			$/kWh		cost@Usage
2					40 Kwh	80 Kwh	319 Kwh
3	Rate first 50 kWh	0	$0.33	0.33	cost1 40@.33=13.2	50@.33=16.5	50@.33=16.5
4	Rate 51-200 kWh	50	$0.38	0.05	cost2 0@.38=0	80-50=30@.38=11.4	200-50@.38=57
5	Rate above 200 kWh	200	$0.43	0.05	cost3 0@.43=0	0@.43=0	319-200@.43=51.17
6	Total		Total=$@rate1+$@rate2+$@rate3		$13.20	$27.90	$124.67
Sheet2

Cell Formulas
Range	Formula
D3:D5	D3	=C3-C2
E6:G6	E6	=SUMPRODUCT(--(E2>$B$3:$B$5), (E2-$B$3:$B$5), $D$3:$D$5)

or using a Lookup table

Book3
	A	B	C	D	E	F	G	H	I
1		$/kWh	cost@Usage
2			40 Kwh	80 Kwh	319 Kwh		Greater than	Hourly Rate	Differential
3	Rate first 50 kWh	$0.33	cost1 40@.33=13.2	50@.33=16.5	50@.33=16.5			0
4	Rate 51-200 kWh	$0.38	cost2 0@.38=0	80-50=30@.38=11.4	200-50@.38=57		0	$0.33	$0.33
5	Rate above 200 kWh	$0.43	cost3 0@.43=0	0@.43=0	319-200@.43=51.17		50	$0.38	$0.05
6	Total	Total=$@rate1+$@rate2+$@rate3	$13.20	$27.90	$124.67		200	$0.43	$0.05
Sheet2

Cell Formulas
Range	Formula
H4:H6	H4	=B3
I4:I6	I4	=H4-H3
C6:E6	C6	=SUMPRODUCT(--(C2>$G$4:$G$6), (C2-$G$4:$G$6), $I$4:$I$6)