Dude needs help w/averaging monthly data..

[The.Dude]

Dude here..

I'm running MS 13 and trying to insert a formula that will calculate the monthly average for my data for an entire column (approx. 600 entries), tried a variety of formula's & keep getting error messages. I found one at exceljet.net "average-by-month" (sorry I can't post link yet) that is almost identical to what the Dude is trying to accomplish on a smaller scale, and created an exact replica of it, but get 0's in cells G5, 6, & 7? The formula I've been using is =AVERAGEIFS(D5:D12,C5:C12,">="&F5,C5:C12,"<="&EOMONTH(F5,0))

Did I miss a step? Is there something in Column E that the I neglected to include?

Any help is very much appreciated.

[63falcondude]

Hey Dude, welcome to the forum.

From one dude to another, I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

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"

[The.Dude]

Hey Dude, welcome to the forum.

From one dude to another, I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

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"

It says I can’t upload until I have more posts.

[Sam Capricci]

I think two posts is enough, it is to prevent the spammers. BTW Dude, please change your post title to something more appropriate to your problem rather than needs help w/formula, I'd say that 99% of those who come to this forum need help with a formula. Think in terms of using search, what words would you search on for the same problem?

[The.Dude]

Good suggestion on the thread name, done. I'll try to attach a copy of the workbook so you guys can see. Still won't allow me to attach a link but if you google "average by month exceljet" it's the 1st link. My test sheet is a carbon copy of that.

[The.Dude]

Here is a copy of my workbook.. I have two tabs.

1st tab "Sheet 1" is for practice.. it's set up just like the excel jet I mentioned.

the 2nd tab "Beta" is the actual project I'm working on. In that, I need Cell AB7 to give me the 'overall average' (column U) for all of the monthly data, which is an average of cells G - T in each row. This a beta though, once it's working I will transfer all of my January data to the sheet and there will literally be approx. 600 when I'm done, approx. 50 +/- each month of the year.

Assuming this works, then cells AB7:AB18 will then feed numbers into cells Y7:Y18 and populate the graph in cell V5 each month with the monthly average without any additional copy/pasting on my part.

In my beta tab I was able to get cell AB7 to average G6:T6, but no other rows. That's when I created a duplicate of the exceljet formula to work out the bugs. Best I can tell, it's something w/the date tabs/formats. I added a formula to generate the date by name in column A, which helped, but still no dice.

[63falcondude]

Put 1/1/2018 in cell F5, 2/1/2018 in F6, and 3/1/2018 in cell F7 (you can format these dates to show however you'd like).

Then in cell G5, you can use this:
=AVERAGEIFS(D:D,C:C,">="&F5,C:C,"<="&EOMONTH(F5,0))

[The.Dude]

that gave me zero averages.. for sheet 1.. the January average (G5) should be 5.5, February 7.5 (G6) & March 9.5 (G7)

[63falcondude]

Make sure that you clear out the other formulas in the same cell.

Expand the formula bar and you'll see what I'm talking about.

[Sam Capricci]

63FD, I thought that would work too but it didn't for me either.
Oh, I see another what appeared to be hidden formula in the formula bar below. Cleaver that you saw it.

[The.Dude]

F*CKING A Man!!! Now to see if I can get that to work in my beta sheet

[63falcondude]

Oh, I see another what appeared to be hidden formula in the formula bar below.

I was so confused as to why it wasn't working so I evaluated the formula then saw it!

[The.Dude]

hmm, ok... it works like a charm on my dummy sheet, but not on the beta?! In that I need the same thing to happen, column B Date, Column U Overall average.. in Cell AA7.

[Sam Capricci]

if I'm understanding you if you double click on the corner of the cell that you put 63FD's formula into that should extend it down and overwrite the extra formula in those cells.
make sure you change your months to 1/1/2018 and 2/1/2018 (using =EDATE(first month,1) should work for you, you can format the month as Jan by going to custom format and mmm

[63falcondude]

Yea, you should be able to use this in AB7:

=AVERAGEIFS(U:U,B:B,">="&AA7,B:B,"<="&EOMONTH(AA7,0))

After changing AA7 to 1/1/2019, AA8 to 2/1/2019, etc.

One thing worth noting is that you are taking the average of averages here.
Unless all of the averages have the same amount of figures being averaged (e.g. row 6 has 14, row 7 has 8, etc.), taking the average of averages will likely lead to incorrect/misleading results.

You might be better off using this in AB7:

=SUMPRODUCT((B$6:B$293>=AA7)*(B$6:B$293<=EOMONTH(AA7,0))*(G$6:T$293))/SUMPRODUCT((B$6:B$293>=AA7)*(B$6:B$293<=EOMONTH(AA7,0))*(G$6:T$293<>""))

[The.Dude]

..........

[The.Dude]

I think that did it 63FD!! testing again to make sure

[The.Dude]

I think that did it... wow, completely overlooked the sumproduct function. great catch though that the average of averages would give inaccurate data. I got so lost in the formula's I overlooked it.

[63falcondude]

Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[The.Dude]

Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Odd, I had to restart my computer and lost the 3-4 rows of data.. now it doesn’t work? Going to tinker with it a little & see what’s going on.

[The.Dude]

back in business, must have deleted something or changed it by accident.

[The.Dude]

After occupying various administrative buildings this morning, I did manage to find a way to micturate on the formula 69falcondude created. The formula itself is fine.. but if some deadbeat were to mistakenly put data out of order.. let say in the middle of row 28 (without a date in column A).. realize the error & just delete the data, it creates some kind of an error for seemingly the entire sheet and 69's formula's displays a #value! error. It seems this is what yours truly did late yesterday.

I created another workbook so I don't lose the work & formula.. but I can see this being a problem down the road. I've tried protecting the cells that contain the formula, but that it still created the error. Any thoughts?

[The.Dude]

Bump......

[63falcondude]

I don't understand the issue that you are experiencing.

If you type data into a cell and then delete that data, nothing should change.

[The.Dude]

It's the dreaded spacebar.. that's what is causing it. While entering and updating data if I hit the spacebar anywhere between columns E-T, then it leaves a space in the cell which then corrupts the formula. I think what my formula needs is an =IF(ISerror(formula)),"-",Formula similar to the one in H3 which is =IF(ISERROR(AVERAGE(H4:H4995)),"-",AVERAGE(H4:H4995)).

The problem is I have admittedly have fat fingers and sometimes the undo function won't restore the formula regardless of how far back I go. I now realize that hitting delete vs undo is preferred but if I share this with others (which I plan on doing) I can absolutely foresee this happening.

[The.Dude]

I've tried creating the IF(ISERROR formula to correct that.. but get a message that says there are too many arguments.

[63falcondude]

If the problem is the possibility of putting in an incorrect entry, you don't adjust the formula for that, you use Data Validation to not allow an incorrect entry such as a space.

[The.Dude]

which is basically an internal drop down list within the cell(s) correct?

[63falcondude]

That is one thing that Data Validation offers.

Another is creating a rule that gives you a warning or error message when an unwanted entry is entered.

[The.Dude]

That is one thing that Data Validation offers.

Another is creating a rule that gives you a warning or error message when an unwanted entry is entered.

How do you do that? Meaning create that rule?

[63falcondude]

Let's say that you want to create a Data Validation rule that will only allow for numbers ≥ 0 in G6:T293.

Highlight G6:T293 > Data > Data Validation > Allow: Decimal > Data: greater than or equal to > Minimum: 0 > OK