+ Reply to Thread
Results 1 to 3 of 3

Average cells once another column is above certain value

  1. #1
    Forum Contributor
    Join Date
    02-13-2013
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    217

    Average cells once another column is above certain value

    Hey, In column A, I have numbers that increase from 0 to ~10

    Currently, I have a formula in O2 that averages the values in the D column, but it only starts averaging once the A column (Corresponding) is at least 8.

    So basically now I have:

    =average(d189:d1000)

    How can I make the d189 not something I have to type in? This scenario means in cell A189, the value was at least 8 (all values past continue to increase).

    Any idea how to do this?

  2. #2
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Average cells once another column is above certain value

    Sounds like you need an If statement and to use absolute values

    =If(SelectedCell>8,average($d$189:$d$1000),"")

    The first part of this formula checks if your selected cell is greater than 8. If the selected cell is greater than 8, the cell will display the average of d189 to d1000. If selected cell is less than 8, the cell will show as blank.

    The dollar signs make the value absolute, meaning if you drag down the formula, these cells will not change inside the formula.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Average cells once another column is above certain value

    Try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The Match finds the last row which was less than 8 then adds one. The Indirect takes 'D' and that number, to give you the starting reference for the average.
    This relies on the values in column A being in increasing numerical order, but I take your 'all values past continue to increase' comment to imply that.

    Hope that helps.
    Last edited by Aardigspook; 03-03-2017 at 04:27 PM.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

+ 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] Get average of some cells of a column If criteria in a different column with vba not worki
    By capson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2015, 09:47 AM
  2. Replies: 14
    Last Post: 06-14-2013, 09:00 PM
  3. Average the last 10 cells of a dynamic column but ignore blank cells & 0
    By mattadler22 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-13-2013, 11:37 PM
  4. [SOLVED] find blank cells in column and average the cells below
    By desibabuji in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-03-2013, 12:03 PM
  5. Average range of cells in column if values in adjacent column are equal
    By RyNye in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2012, 10:12 AM
  6. Replies: 2
    Last Post: 04-18-2012, 05:10 AM
  7. [SOLVED] Average the Last Five Cells in a Column
    By Warrior Princess in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-16-2005, 11:06 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