Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate Average and Total on Budget Spreadsheet

    Hi, I am interested in being able to calculate an average and a total based upon the total number of “Actual” spend YTD. This spreadsheet is used for budgeting. It contains an “actual” amount that was spent during the month as well as future forecasted amounts. I’d like to be able to:
    1. When an amount is Actual for say Jan-March (throughout the year obviously) calculate a Total amount (based up “actual” spend) and an “Average” amount based upon “Actual” Spend. When it is “Actual” I type in the word “Actual” in Row 2 for that month.
    2. For example, if Jan-March Actuals were say $300K, in Column A display $300,000 and in Column B display $100,000. This would be determined by if the word “Actual” were entered in row 2.
    3. For both do this throughout the year.
    4. I am attaching a sample of what I am looking for.

    Many thanks!
    Mitch
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,496
    Thanks
    33
    Thanked 63 Times in 59 Posts
    If row 9 contains the ACTUAL amounts, try the following formula in A8: =SUMPRODUCT(("Actual"=G2:R2)*G8:R8)

    In B8: =IFERROR(A8/COUNTIF(G2:R2,"Actual"),0)

  3. #3
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks!

    Quote Originally Posted by kweaver View Post
    If row 9 contains the ACTUAL amounts, try the following formula in A8: =SUMPRODUCT(("Actual"=G2:R2)*G8:R8)

    In B8: =IFERROR(A8/COUNTIF(G2:R2,"Actual"),0)
    Perfect. Thanks so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •