+ Reply to Thread
Results 1 to 6 of 6

How to work AVG (average) formula

  1. 12-07-2013, 03:41 PM #1
    Curious Dude
    Curious Dude is offline
    Forum Contributor
    Join Date
    12-07-2013
    Location
    Staten Island, New York
    MS-Off Ver
    Excel 2010
    Posts
    106

    How to work AVG (average) formula

    Hey everyone. I'm creating a spreadsheet of all my daily expenses.

    I'm trying to make a formula which will calculate an average of my daily expenses. I created an AVG formula in a cell at the end of the daily expenses total row.

    The problem is excel is taking the sum of my expenses and dividing by 31 (all the days of the month) rather than dividing by just the number of days which have occurred (in this case it's 6).

    Does anyone know how to work around this?
    Reply With QuoteRegister To Reply
  2. 12-07-2013, 03:48 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to work AVG (average) formula

    Maybe something like this...

    B2:AF2 = daily expenditures

    For the daily average:

    =SUM(B2:AF2)/DAY(NOW())
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 12-07-2013, 03:54 PM #3
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,882

    Re: How to work AVG (average) formula

    Here is an example of how =Average Works

    Data Range
    E
    F
    G
    H
    1
    5
    5
    2
    6
    6
    3
    7
    7
    4
    8
    8
    5
    0
    6
    0
    7
    8
    8
    8
    8
    8
    9
    9
    9
    10
    =Average(F1:F9)
    7.285714
    5.666667
    =Average(G1:G9)
    11
    12
    =sum(F1:F9)
    51
    51
    =sum(G1:G9)
    13
    =count(F1:F9)
    7
    9
    =COUNT(G1:G9)
    14
    =F12/F13
    7.285714
    5.666667
    =G12/G13
    15

    If you any thing other than a blank in the cells with no values, then Excel counts the cell as part of the calculation.
    Last edited by alansidman; 12-07-2013 at 03:56 PM.
    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
    Reply With QuoteRegister To Reply
  4. 12-07-2013, 03:58 PM #4
    Curious Dude
    Curious Dude is offline
    Forum Contributor
    Join Date
    12-07-2013
    Location
    Staten Island, New York
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: How to work AVG (average) formula

    I found the answer here: http://spreadsheets.about.com/od/exc...e-function.htm

    Thanks to everyone for responding!
    Reply With QuoteRegister To Reply
  5. 12-07-2013, 04:03 PM #5
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,882

    Re: How to work AVG (average) formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  6. 12-07-2013, 04:10 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to work AVG (average) formula

    Good deal. Thanks for the feedback!
    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. Formula to work out an exact average over an exact number
    By Sandyshirl in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-11-2013, 01:35 AM
  2. Help with a formula to work out average difference in cost.
    By Biskit in forum Excel General
    Replies: 1
    Last Post: 07-13-2012, 06:43 AM
  3. Why won't the average function work?!
    By jfarlow in forum Excel General
    Replies: 10
    Last Post: 08-15-2008, 07:12 AM
  4. Macro to work out max min and average
    By KRUDA79 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2007, 03:51 AM
  5. [SOLVED] Trying to get an IF/AND average to work
    By Sage in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-18-2006, 06:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1