Electrical bills and solar energy

Joined
Jul 16, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,463
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,928
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,928
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 
Joined
Jul 16, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi

Thanks for you reply
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
    mr excel.com 01 st aug.JPG
    132.5 KB · Views: 3

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,928
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,203
Messages
5,704,926
Members
421,372
Latest member
Jamie11

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top