+ Reply to Thread
Results 1 to 8 of 8

Average values in a column based on values of other columns

Hybrid View

  1. 09-29-2022, 09:01 AM #1
    will.sf
    will.sf is offline
    Registered User
    Join Date
    03-18-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20148) 64-bit
    Posts
    14

    Average values in a column based on values of other columns

    Hi, I'm hoping someone could help with finding a formula. I'd like to find the average in column A from when column B is > 0 to when column A is reducing in value and reaches the value of column C + 30 (44.09). This would be the average in column A row 5 to row 14, giving a result of 46.47. The attached Excel has everything set up with header and column values are setup in Name Manager

    Attachment 798382

    A B C
    1 Temperature3 SecondatFlowRate Ct
    2 38.60 0.00 14.09
    3 38.68 0.00 14.09
    4 38.68 0.00 14.09
    5 39.15 16.43 14.09
    6 42.12 16.91 14.09
    7 45.60 14.05 14.09
    8 49.08 15.98 14.09
    9 52.56 15.80 14.09
    10 53.47 14.97 14.09
    11 50.36 15.03 14.09
    12 47.25 14.97 14.09
    13 44.13 15.03 14.09
    14 41.02 15.00 14.09
    15 37.91 15.00 14.09
    16 34.82 14.97 14.09

    Many thanks,
    Will
    Reply With QuoteRegister To Reply
  2. 09-29-2022, 09:24 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,610

    Re: Average values in a column based on values of other columns

    Hi

    C+30 is reached in row 7 ?

    Could you please explain some more? This is not very clear to me. Thx
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 09-29-2022, 09:27 AM #3
    will.sf
    will.sf is offline
    Registered User
    Join Date
    03-18-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20148) 64-bit
    Posts
    14

    Re: Average values in a column based on values of other columns

    Yes, but the value is still increasing. I'm looking at the point where values in the coloumn are decreasing, which would make this row 14. This is what's making this one so tricky.
    Reply With QuoteRegister To Reply
  4. 09-29-2022, 09:40 AM #4
    dosydos
    dosydos is offline
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    MA, USA
    MS-Off Ver
    365(PC) V:2412
    Posts
    1,477

    Re: Average values in a column based on values of other columns

    what version excel are you using?
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
    Reply With QuoteRegister To Reply
  5. 09-29-2022, 09:43 AM #5
    will.sf
    will.sf is offline
    Registered User
    Join Date
    03-18-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20148) 64-bit
    Posts
    14

    Re: Average values in a column based on values of other columns

    I'm currently using:
    Microsoft Excel for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20148) 64-bit
    Reply With QuoteRegister To Reply
  6. 09-29-2022, 11:16 AM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Average values in a column based on values of other columns

    Quote Originally Posted by will.sf View Post
    I'm currently using:
    Microsoft Excel for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20148) 64-bit
    Please update your user profile to reflect this.
    Dave
    Reply With QuoteRegister To Reply
  7. 09-29-2022, 09:59 AM #7
    dosydos
    dosydos is offline
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    MA, USA
    MS-Off Ver
    365(PC) V:2412
    Posts
    1,477

    Re: Average values in a column based on values of other columns

    alright not the best solution but it seems to work. I am sure others will find a more practical approach.
    First i couldnt utilize your spreadsheet as it wouldnt open so i made one which means no named ranges.
    Second i utilized a helper column with the formula:
    Formula: copy to clipboard 
    =IF(AND(ISNUMBER(A1),A1>A2),A2<=$C$2+30,FALSE)

    Then i utilized the below formula to get average:
    Formula: copy to clipboard 
    =AVERAGE(INDIRECT("A"&MIN(IF($B$2:$B$16>0,ROW($B$2:$B$16),""))&":"&"A"&MIN(IF($E$2:$E$16=TRUE,ROW($E$2:$E$16),""))))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 09-29-2022, 10:12 AM #8
    will.sf
    will.sf is offline
    Registered User
    Join Date
    03-18-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20148) 64-bit
    Posts
    14

    Re: Average values in a column based on values of other columns

    Hi Dosydos, thanks very much for taking the time to find a solution. My main table of data is using PowerQuery, so I can work your first formula into that to produce the TRUE/FALSE column, then your second formula should fit nicely into the reporting sheet.
    Thanks and have a great day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Match Values Based on First Column then Between Values in Next Two Columns
    By ntsun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2017, 10:06 AM
  2. Count Unique values in Column based on values in other columns
    By dmschave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 11:06 AM
  3. Number of unique values in column A based on values in columns B and C
    By Byron720 in forum Excel General
    Replies: 1
    Last Post: 11-16-2013, 05:18 AM
  4. [SOLVED] If values in a column are equal, then need to calc average of values in other columns
    By engineerlady in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2013, 02:33 PM
  5. Need to calculate the average for a column, based on values in two other columns
    By mattr86 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2012, 11:26 AM
  6. Needing to average values of cells in one column based on matches in other columns
    By GDMiller in forum Excel General
    Replies: 0
    Last Post: 09-01-2010, 02:47 PM
  7. SUMIF - add values from one column based on values in two other columns
    By goodmike in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-30-2008, 08:04 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1