+ Reply to Thread
Results 1 to 6 of 6

Picking out average minimum values controlled by 2 different variables

  1. #1
    Registered User
    Join Date
    10-28-2022
    Location
    Uppsala
    MS-Off Ver
    MS365 2209
    Posts
    3

    Picking out average minimum values controlled by 2 different variables

    Hi, I am designing a hydrology sheet where you put in average discharge values from time series and then get the results from different parameters, I am not very experienced in excel though. The time series shows discharge values for every day in ten years.

    Now, I want to calculate average minimum value for every MONTH in the time series.
    Ideal would be a formula that picks out the minimum value for all january MONTHS for all YEARS in the time series and then calculate the average value out of those.

    However I am uncertain how to do this, and since I have a lot of columns in the sheet I would like to avoid adding too many columns, the ideal would be if the formula could just use the existing columns without the need to create new columns with data of minimum values for every single month or similar.

    Now, I want to calculate average minimum value for the month of january in the time series.

    Currently, my formula looks like this:

    =MIN(FILTER($E$78:$E$4095;COUNTIF(C63;$I$78:$I$4095)))

    Where
    E = Discharge values from time series
    C = The actual month I want to know the minimum value of
    I = Month for corresponding discharge value in the time series

    so...
    =MIN(FILTER($"values from time series";COUNTIF("month of interest";"corresponding months in time series")))

    Besides the mentioned columns/variables; I also have a column with the current year in the time series.

    The problem with my formula is that it picks out of the minimum value for ALL january months together in the time series, when I want to know the AVERAGE minimum value for the month of january.

    Can I solve this problem by just changing my formula, without creating a lot of columns with new data?
    Simplified excel example sheet for modification is attached. (the formula should handle much longer data series and be used for all months in the actual sheet)


    I also add a picture of my actual excel sheet to provide information about how much data is actually in the sheet if it helps but I think the description above probably describes my problem better.
    excelproblem.jpg
    Attached Files Attached Files
    Last edited by Eastby91; 10-28-2022 at 05:34 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,359

    Re: Picking out average minimum values controlled by 2 different variables

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

    Administrative Note:

    Is your forum profile showing the version of Excel that you need this to work for? 2209 is just a release number ...

    Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

    The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

    Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-28-2022
    Location
    Uppsala
    MS-Off Ver
    MS365 2209
    Posts
    3

    Re: Picking out average minimum values controlled by 2 different variables

    Thank you for the information, I will post a simplified excel sheet wich can be modified according to the instructions.
    However, since it is hevily simplified in data coverage, I let the picture from the real excel sheet still be visible here to provide context of the large data coverage.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: Picking out average minimum values controlled by 2 different variables

    Can I solve this problem by just changing my formula, without creating a lot of columns with new data?
    I can envision how it could be done by adding one column.
    1. The added column is populated using: =IF(C7=MINIFS(C$7:C$20,D$7:D$20,D7,E$7:E$20,E7),C7,"")
    2. The average minimum discharge formula could be: =AVERAGEIFS(F$7:F$20,D$7:D$20,C3)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    10-28-2022
    Location
    Uppsala
    MS-Off Ver
    MS365 2209
    Posts
    3

    Re: Picking out average minimum values controlled by 2 different variables

    Ok! I have since gone on with another solution which creates more columns, but this simplifies the problem a lot. Thanks!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: Picking out average minimum values controlled by 2 different variables

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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] Solver: minimum value of two variables that result in x
    By mucura in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 02-03-2022, 07:37 AM
  2. Chart Template Not Picking All Variables
    By Excel-Access in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-08-2019, 06:58 PM
  3. Picking Closest Date from List - With Date Minimum
    By JPolvCB in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-05-2013, 12:55 AM
  4. calculate average from an array with controlled length
    By Christopher83 in forum Excel General
    Replies: 7
    Last Post: 02-07-2012, 06:06 AM
  5. Picking more than one minimum value from a column
    By vkartikv in forum Excel General
    Replies: 4
    Last Post: 09-06-2011, 06:21 PM
  6. How to calculate the average of minimum values
    By gideone in forum Excel General
    Replies: 9
    Last Post: 08-31-2011, 03:56 AM
  7. Finding a minimum with solver and 3 variables
    By BCITgirl in forum Excel General
    Replies: 2
    Last Post: 12-05-2010, 05:16 AM

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