• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel formula for large data|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Excel formula for large data|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Excel formula for large data
sp_PrintTopic sp_TopicIcon
Excel formula for large data
Avatar
Payal Pachory
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 11, 2019
sp_UserOfflineSmall Offline
1
September 11, 2019 - 2:35 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Payal Pachory
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 11, 2019
sp_UserOfflineSmall Offline
2
September 11, 2019 - 2:38 pm
sp_Permalink sp_Print

Forgot to mention I cannot use pivot tables or vba for this. 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
3
September 12, 2019 - 9:32 am
sp_Permalink sp_Print

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

Avatar
Payal Pachory
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 11, 2019
sp_UserOfflineSmall Offline
4
September 12, 2019 - 10:38 am
sp_Permalink sp_Print

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?

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
September 12, 2019 - 11:44 am
sp_Permalink sp_Print

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)

Avatar
Payal Pachory
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 11, 2019
sp_UserOfflineSmall Offline
6
September 12, 2019 - 2:16 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
September 12, 2019 - 9:33 pm
sp_Permalink sp_Print

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

Avatar
Payal Pachory
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 11, 2019
sp_UserOfflineSmall Offline
8
September 13, 2019 - 2:16 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
September 13, 2019 - 7:50 pm
sp_Permalink sp_Print

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.

Avatar
Payal Pachory
Member
Members
Level 0
Forum Posts: 7
Member Since:
September 11, 2019
sp_UserOfflineSmall Offline
10
September 16, 2019 - 1:51 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31888
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.