+ Reply to Thread
Results 1 to 9 of 9

Average formula to deal with blanks in sub calculations

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

    Average formula to deal with blanks in sub calculations

    Hello all,

    Thank you for looking at this post!

    I'm attempting to make a formula that will negate the need for multiple sub calculation formulas. I'm trying to wrap up a couple of calculations in one formula, but running into issues when trying to calculate blank cells. The average function (just looking at cells) ignores blanks and just uses the average of actual numbers provided, but when I try to make all of these calculations in one cell, the average function doesn't recognize "" as a blank, or to ignore. Take a look at the example and let me know if you can think of any ways to help deal with these blanks.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,800

    Re: Average formula to deal with blanks in sub calculations

    What answer are you expecting?

    I used =AVERAGE(C14:D21) and got 105.96%
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

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

    Re: Average formula to deal with blanks in sub calculations

    Quote Originally Posted by FDibbins View Post
    What answer are you expecting?

    I used =AVERAGE(C14:D21) and got 105.96%
    Thanks for the reply. I'm attempting to make the formula without the use of those sub calculations. So creating a formula in D24 just using the table as reference. The sub calculations are really just for reference.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,800

    Re: Average formula to deal with blanks in sub calculations

    I understand that, but what answer are you expecting?

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,556

    Re: Average formula to deal with blanks in sub calculations

    Hmm, C23 is average of derived value...

    Should it be... (110+7.56+100+5)/(110+100) = 105.98%?

    If above assumption is correct...
    In D24: =SUMPRODUCT(((D3:D4<>"")*($B$3:$B$4)+D3:D4))/SUMIF(D3:D4,"<>",$B$3:$B$4)

    Copy to D23: You'll get 105.98%.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

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

    Re: Average formula to deal with blanks in sub calculations

    Quote Originally Posted by FDibbins View Post
    I understand that, but what answer are you expecting?
    I'm just trying to get the same result as the first average function without using the sub calculations. So 105.94% in C24, and 107.27% in D24

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

    Re: Average formula to deal with blanks in sub calculations

    Quote Originally Posted by CK76 View Post
    Hmm, C23 is average of derived value...

    Should it be... (110+7.56+100+5)/(110+100) = 105.98%?

    If above assumption is correct...
    In D24: =SUMPRODUCT(((D3:D4<>"")*($B$3:$B$4)+D3:D4))/SUMIF(D3:D4,"<>",$B$3:$B$4)

    Copy to D23: You'll get 105.98%.
    Thanks for the help! This formula succeeds work finding the average of the first two Parts % of target, but doesn't work when trying to scale the formula to look at all the parts. Any idea what's wrong?

    =AVERAGE(D14:D21) = 105.77%

    =SUMPRODUCT(((D3:D10<>"")*($B$3:$B$10)+D3:D10))/SUMIF(D3:D10,"<>",$B$3:$B$10) = 105.96%

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,556

    Re: Average formula to deal with blanks in sub calculations

    Because, as I explained in the post...

    Average of individual derived value = AVERAGE(Calculation 1, Calculation 2, ....)

    Is different from what I did. Which is calculating overall derived value for specified period.

    Ex:
    AVERAGE(106.87%,105.00%) = 105.94% (Average of... % of Target Part1 & 2)
    (110+7.56+100+5)/(110+100) = 105.98% (Average % of target over Part1 & 2)

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,800

    Re: Average formula to deal with blanks in sub calculations

    As CK76 points out, it is mathematically incorrect to take an average of averages (a % is simply an average, portrayed as such)

+ 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] Ignore blanks formula not giving correct average based on weights
    By MSExcelLnr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-08-2017, 05:36 PM
  2. Replies: 7
    Last Post: 09-12-2017, 01:44 PM
  3. Problem with NORMSDIST function; how to deal with blanks?
    By raperm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-27-2017, 12:20 PM
  4. Average formula for last 15 days excluding blanks
    By jimbob23 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-22-2014, 11:50 AM
  5. [SOLVED] Making Average() formula ignore months with too much blanks
    By Mantask in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2013, 05:07 PM
  6. Average Formula of Cells Over Certain Value Excludes Blanks
    By larzep in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2012, 08:36 PM
  7. Replies: 1
    Last Post: 01-25-2011, 10:37 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