Log In
Register
Home
September 11, 2019
Offline
Hi Mynda,
First, thanks heaps for returning back to me.
On the query:
I have 19 scenarios of household electricity consumption data in one sheet showing half hourly consumption (kwh) (from 12am to 12pm from 1 to 31 days. Peak consumption is monday to Friday from 7am to 11pm and off-peak consumption is from 11pm to 7am and Saturday and Sunday all day. My dates are in the second sheet running from 1/1/2020 to 31/12/2023. I have tried using sumproduct, weekday formula but I am wrong.
In the attached file, please see HouseholdUsageData (kWh) and Workings tab. In the working tab please refer to cell F90 (coded in red). The formula is that row is wrong. It is assuming the days in HouseholdUsageData as weekday, whereas I want to exclude Saturday and Sunday usages from 7am to 11pm and add them to the off-peak consumption data.
Also, I need to build data tables for the matrix (plan selected and scenario) and then build top 3 cheapest plans. I seem to be making some mistake out there
Can you help?
regards
Please let me know
Cheers
September 11, 2019
Offline
Forgot to mention I cannot use pivot tables or vba for this.
July 16, 2010
Offline
Hi Payal,
The WEEKDAY function requires a date (serial number), you're providing it with a day of the month number. It cannot distinguish which day of the week the number 1 falls on without the context of month and year i.e. the full date.
The modern form of SUMPRODUCT is SUMIFS.
Mynda
September 11, 2019
Offline
Thanks I know. I tried using sumproduct function with indirect and weekday but I cannot get it right.
Somehow I need to link the month and year - Jan-20 in the workings tab with the householdusage data day tab to give me a range which gives me dates as 1/1/2020 to 31/1/2020 and converts it into a weekday.
this is the formula I have which is incorrect.
=-(SUMPRODUCT(--(WEEKDAY('HouseholdUsageData (kWh)'!$A$2:$A$1489,2)>Assumptions!$G$34),--('HouseholdUsageData (kWh)'!$AB$2:$AB$1489>Assumptions!$G$39),--('HouseholdUsageData (kWh)'!$AB$2:$AB$1489<=Assumptions!$H$39)*INDIRECT("'"&$D$76&"'!"&L$83)))
Can you assist please?
July 16, 2010
Offline
You can try using the DATE function to build the date inside the WEEKDAY function e.g.
--(WEEKDAY(DATE(YEAR(L7),MONTH(L7),'HouseholdUsageData (kWh)'!$A$2:$A$1489),2)<=Assumptions!$G$34)
September 11, 2019
Offline
Thank you so so much. Worked like a charm! I complicated it in my head.
I need to build scenarios for different plans and consumption scenarios. Was using data tables for that in the assumptions tab but I am getting the same values. Basically I need to get a snapshot of all the combinations so that I can build an interactive dashboard for the costs.
Is it because the householdconsumption data (input) and the workings (output) are in a separate tab? It is just not working!
Appreciate all your help on this.
regards
July 16, 2010
Offline
Hi Payal,
I doubt it has anything to do with the data being on separate tabs. You haven't been specific about what isn't working.
Mynda
September 11, 2019
Offline
My apologies. I should have been more specific. I have built 2 single variable data table - for a selected scenario, what is the average monthly cost for each of the plans.(2) For a selected plan, what is the average monthly cost for each of the scenarios. I am getting stuck in the combination matrix - 2 variable data table.
The 2 single variable data table are working fine.
I have built the data tables in the assumptions tab. Please find attached the assumptions page with the data table structure (row 98). I have scenarios in the column and the plans in the row. At the intersection i have =Workings'!F73 (linking to average monthly cost). What i am looking for is for various plans, I can find the monthly average cost for each of the scenarios. Where am I making the mistake? Please let me know if you need any more information. Unfortunately, I am not able to upload the full file.
Cheers
Payal
July 16, 2010
Offline
Hi Payal,
The 2 variable tables require two inputs that are numbers, as explained here. The variable row 98 contains text. This needs to contain numbers. Likewise column D. See example attached.
Mynda
P.S. If you have further question unrelated to these ones, please start a new thread so that others can also find the answers easily.
September 11, 2019
Offline
Thanks Mynda. I know it has to be a number. But if we put a reference number which links to the names of the plans and consumption pattern it works. I got it finally. I was linking the wrong cells for column and row input.
Thank you heaps for all your help on this. You guys are brilliant.
Cheers
1 Guest(s)
