+ Reply to Thread
Results 1 to 7 of 7

Maximum Consumption for Material "Y" over Any 30-Day Period

  1. #1
    Registered User
    Join Date
    12-20-2022
    Location
    Las Vegas, Nevada
    MS-Off Ver
    365
    Posts
    4

    Maximum Consumption for Material "Y" over Any 30-Day Period

    Hello Experts,

    This is my first of (I'm sure of) many posts. My question today is, How do I calculate the max for a specific entity over a set window of time?

    In this case, I would like to calculate the maximum consumption/usage of a specific material over any 30-day window inside of the year. The first two points can be taken care of with a MAXIFS, but I don't understand how to have excel evaluate every 30-day instance for each material. Seems doable, I think I am just lacking the knowledge. Any advice would be appreciated!

    A small sheet with sample data should be attached.

    Best,
    Plenty o' Questions
    Attached Files Attached Files
    Last edited by Plenty o' Questions; 12-20-2022 at 11:45 PM. Reason: Completed

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Maximum Consumption for Material "Y" over Any 30-Day Period

    Since you have 365, you have Power Query available to you. I have created a parameter query coupled with a max grouping for each product for the thirty day period selected.

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Maximum Consumption for Material "Y" over Any 30-Day Period

    Maybe this:

    To get the maximum consumption of any 30 day period, enter this in a cell:

    =MAX(SUMIFS($C$2:$C$13,$B$2:$B$13,">="&B2:B13,$B$2:$B$13,"<="&B2:B13+30,$A$2:$A$13,F2))

    To get the date that the 30 day period starts, try this:

    =LET(
    a,SUMIFS($C$2:$C$13,$B$2:$B$13,">="&B2:B13,$B$2:$B$13,"<="&B2:B13+30,$A$2:$A$13,F2),
    b,MAX(a),
    c,MATCH(b,a,0),
    INDEX(B2:B13,c))

    See attached.
    Attached Files Attached Files
    Last edited by Gregb11; 12-20-2022 at 11:06 PM.

  4. #4
    Registered User
    Join Date
    12-20-2022
    Location
    Las Vegas, Nevada
    MS-Off Ver
    365
    Posts
    4

    Re: Maximum Consumption for Material "Y" over Any 30-Day Period

    That worked perfect, plus I learned something. You certainly don't get to say those in conjunction too often! Much appreciation, above and beyond Gregb11

  5. #5
    Registered User
    Join Date
    12-20-2022
    Location
    Las Vegas, Nevada
    MS-Off Ver
    365
    Posts
    4

    Re: Maximum Consumption for Material "Y" over Any 30-Day Period

    Thank you for taking your time to look at this. I think I need to brush up on my PowerQuery.

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Maximum Consumption for Material "Y" over Any 30-Day Period

    You're welcome, and thanks for the rep and feedback.

  7. #7
    Registered User
    Join Date
    12-20-2022
    Location
    Las Vegas, Nevada
    MS-Off Ver
    365
    Posts
    4

    Re: Maximum Consumption for Material "Y" over Any 30-Day Period

    Essentially made my Tuesday (albeit maybe it wasn't the world's best Tuesday), but that is the very least I could do.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] replace "," comma with "." period
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-11-2023, 05:03 PM
  2. Replies: 2
    Last Post: 09-24-2022, 12:36 PM
  3. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  4. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  5. Replies: 2
    Last Post: 08-10-2016, 03:31 PM
  6. Help Creating "Consumption Calculator"
    By jmd20 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2014, 12:01 AM
  7. Replies: 4
    Last Post: 11-17-2013, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1