MTD Formula

[dwool40]

I currently have the following formula to calculate a month to date average.
=(SUM(D47,J47,P47,V47)/4)/C47

The problem is I have to change the /4 to /1, /2, etc at the end of each week. Is there a formula that would calculate the month to date without me having to make the change every week?

[63falcondude]

Hello and welcome to the forum.

If I am understanding correctly, how about this:

=AVERAGE(D47,J47,P47,V47)/C47

[AliGW]

Welcome to the forum!

Yes, there will be, but without seeing your layout and the data in those cells, it's impossible to advise fully.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Roel Jongman]

without an example file I put my money on

replace 4 with..

Please Login or Register  to view this content.

that would give you 1 upto day 7 2 upto day 14 etc

[dwool40]

I attempted to attach but it will not navigate.

[63falcondude]

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[dwool40]

This counts the blank weeks as 0 and doesn't give a true MTD average. I need it to average at the end of each week when I add the data.

[dwool40]

Column AC. If you scroll down to February, you will see where it is currently /1. I have to change it each week.

[63falcondude]

Have you tried the formula suggested in post #2?

It returns the same thing as the formula that you have in there now but does not require updating.

[dwool40]

I did but for some reason, it divides by 4 on the Region Total row. Works on all the other rows.

[Roel Jongman]

Had a look at your workbook.

you uploaded a sheet that has all weeks filled I assume that if you have the februari file it will have now 3 blank weeks and 1 filled (by end of this week)


for AC column you can use the average function

Please Login or Register  to view this content.

if any of the weeks is blank it will automaticly know to devide by 3 or 2 depending on how many weeks are filled.

[dwool40]

If you scroll down you will see Feb. I think I figured it out. I had to use IF to keep the cells blank so that they wouldn't be counted in the average.

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.