1. ## 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

2. 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. ## Thanks!

Originally Posted by kweaver
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
•