Electrical bills and solar energy

New Member
Hi everyone

I am creating a formula but it does not work well,to calculate different prices based at what time the electricity has being consumed in order to compare with cost of salr energy.I need to create a formula to compare differente prices,and at the moment, it does work but only with one condition, but not with three conditions (A1,A2,A3) I do appreciate any help

COLUMN A COLUMN B

A B

A1 Price 1 : 5\$ Between 1 and 4 in the morning
A2 Price 2 : 10\$ Between 4 and 7 in the morning
A3 Price3 : 8 \$ Between 7 and 10 in the morning

Hour AM Consuption Kwh
A4 1 100
A5 2 150
A6 3 300
A7 4 90
A8 5 400
A9 6 300
A10 7 200
A11 8 90
A12 9 80
A13 10 70
A14 11 100
A15 12 50

I need to get the total price per hour

If the hour Column A (A4:A) is between 0 and and 4 , multiply cells in column B (B4) (100 kwh,150,300...etc) by A1 (5 dollars)

If the hour A4:A is between 4 and 7 , multiply column B B4:B (100 kwh ,150,300...etc)) by A2 (10 dollars)

If the hour A4:A is between 7 and 10 , multiply column B B4:B (100kwh,150,300...etc) kwh) by A3 (8 dollars)

I am using formula
=IFS(A:1:A>0,B4*\$A\$1;B4:B<4 but I am having issues , and if i use with if same issues with ifs, if and formula if (and), I spent 03 hours but definitely I can not do it alone.

Thanks for any help

JJ

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

alansidman

Well-known Member
Book3
ABCDEFGHIJ
1HourRate
215
3HourConsumptionCost410
4110050078
5215075094
633001500
7490900
854004000
963003000
1072001600
11890720
12980320
131070280
1411100400
151250200
Sheet1
Cell Formulas
RangeFormula
C4:C15C4=VLOOKUP(A4,\$I\$2:\$J\$5,2,TRUE)*B4

Dave Patton

Well-known Member
If you just need the total, try the sumproduct.

T202107a.xlsm
ABCDEFG
1HourRate
2Total14,17014,17015
3HourConsumptionCost410
4110050078
5215075094
633001500
7490900
854004000
963003000
1072001600
11890720
12980320
131070280
1411100400
151250200
5b
Cell Formulas
RangeFormula
C2C2=SUM(C4:C15)
D2D2=SUMPRODUCT(LOOKUP(A4:A15,F2:G5),B4:B15)
C4:C15C4=VLOOKUP(A4,\$F\$2:\$G\$5,2,TRUE)*B4

Dave Patton

Well-known Member
I intended to post

T202107a.xlsm
ABCDEFG
1HourRate
2Total14,17014,17015
3HourConsumptionCost410
4110050078
5215075094
633001500
7490900
854004000
963003000
1072001600
11890720
12980320
131070280
1411100400
151250200
5b
Cell Formulas
RangeFormula
C2C2=SUM(C4:C15)
D2D2=SUMPRODUCT(LOOKUP(A4:A15,F2:G5),B4:B15)
C4:C15C4=LOOKUP(A4,\$F\$2:\$G\$5)*B4

New Member

Hi

I tried but id did not work, I am doing something wrong for sure.

My intention with this excel, is to be able to compare different rates, based on time.

Therefor just changing the rate price in j2:j5 i can get the rate for the whole month bases in previous consumptions.
I do not understand why there is a 9 in hour and rate 4 , is related to the true formula?Can you explaind it to me ?

I understand form 1 to 4 (I2;I3) the rate is 5 \$, ad form 4 until seven, the rate is 10 \$ and then 9 ???

Cheers

JJ

Attachments

• mr excel.com 01 st aug.JPG
132.5 KB · Views: 6

Dave Patton

Well-known Member
Try post #5
=SUMPRODUCT(LOOKUP(A4:A15,F2:G5),B4:B15)

You can save the post to a clean sheet.
Review the formula. Try Excel's formula Formula Evaluate

New Member

Hi

I was unbale to make it work and thew xl2bb does not work neither

This is the sheet

Any idea?

Dave Patton

Well-known Member
You can save the post #5 to a clean sheet.
The image of your sheet doesn't help.
The forum's tool XL2BB works fine with Excel 365.

Dave Patton

Well-known Member
You seem to have used new rates.
A calculation for 2 days is shown below.
Extend the range as necessary.
T202107a.xlsm
ABCDEFGHI
33HourRate
34HourConsumptionCost7.0598810.16
3510.9460.16110.08
3620.9250.16230.5
3730.9610.16
3840.940.16
3950.9620.16
4060.8720.16
4172.4050.16
4280.9780.16
4390.9030.16
44100.920.16
45110.8060.08
46121.1490.08
47130.9980.08
48140.9320.08
49150.9290.08
50160.480.08
51171.040.08
52181.0490.08
53190.8570.08
54200.8140.08
55210.8620.08
56221.0030.08
57231.0090.5
58240.8440.5
5910.9460.16
6020.9250.16
6130.9610.16
6240.940.16
6350.9620.16
6460.8720.16
6572.4050.16
6680.9780.16
6790.9030.16
68100.920.16
69110.8060.08
70121.1490.08
71130.9980.08
72140.9320.08
73150.9290.08
74160.480.08
75171.040.08
76181.0490.08
77190.8570.08
78200.8140.08
79210.8620.08
80221.0030.08
81231.0090.5
82240.8440.5
5b
Cell Formulas
RangeFormula
F34F34=SUMPRODUCT(LOOKUP(A35:A82,H34:I36),B35:B82)

Dave Patton

Well-known Member
The formula does not use the numbers in column C.
T202107a.xlsm
ABCDEFGHI
33HourRate
34HourConsumptionTotal7.0598810.16
3510.946110.08
3620.925230.5
3730.961
3840.94
3950.962
4060.872
4172.405
4280.978
4390.903
44100.92
45110.806
46121.149
47130.998
48140.932
49150.929
50160.48
51171.04
52181.049
53190.857
54200.814
55210.862
56221.003
57231.009
58240.844
5910.946
6020.925
6130.961
6240.94
6350.962
6460.872
6572.405
6680.978
6790.903
68100.92
69110.806
70121.149
71130.998
72140.932
73150.929
74160.48
75171.04
76181.049
77190.857
78200.814
79210.862
80221.003
81231.009
82240.844
5b
Cell Formulas
RangeFormula
D34D34=SUMPRODUCT(LOOKUP(A35:A82,H34:I36),B35:B82)

Replies
5
Views
161
Replies
5
Views
118
Replies
1
Views
36
Replies
8
Views
206
Replies
8
Views
164

1,148,257
Messages
5,745,709
Members
423,968
Latest member
CHHeights

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.

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