Average data, excluding Wkly Totals

[jedilefty]

Hello,

Can I please get some help averaging some series of data? I have a series of data listed below. How can I get a monthly average, while ignoring the "End of WK Total?" The date range goes all the way to the end of the month.

Date:	Data1	Data2
5/1
5/2
5/3
End of WK Total
5/6
5/7
5/8
5/9
5/10
End of WK Total

 

[Vogateer]

A bit of a hack, but this should get rid of those End of WK Total fields, assuming that A3:A12 are your dates, and B3:B12 are the amounts you want to average:

=AVERAGEIF(A3:A12, "<"&TODAY()+1,  B3:B12)

But the better solution would be to get rid of all those End of WK Totals altogether, and get them from a PivotTable or some other formula if you need them. Mixing subtotals in with your data is always going to cause more problems than it's worth.

 

[Burrgogi]

Hello,

Can I please get some help averaging some series of data? I have a series of data listed below. How can I get a monthly average, while ignoring the "End of WK Total?" The date range goes all the way to the end of the month.

Date:	Data1	Data2
5/1
5/2
5/3
End of WK Total
5/6
5/7
5/8
5/9
5/10
End of WK Total

When posting your question on the forum, it would be helpful to other members here to know what columns & rows we're dealing with. Assuming that your dates in located in Col. A and your row of data begins at row 2. Use this formula:

=AVERAGEIFS(A2:A28,">=5/1/2024",A2:A28,"<=5/31/2024")

You need to enter that as an array formula. Copy & paste it into the cell where you want the average number displayed then press Ctrl + Shift + Enter.

 

[Fluff]

You need to enter that as an array formula.

There is no need to use Ctrl Shift Enter for that formula.

 

[Burrgogi]

There is no need to use Ctrl Shift Enter for that formula.

Oops, my bad. Thanks for the correction.

 

[Burrgogi]

Corrected formula:

=AVERAGEIFS(B2:B28, A2:A28,">=5/1/2024",A2:A28,"<=5/31/2024")

 

[jedilefty]

Corrected formula:

=AVERAGEIFS(B2:B28, A2:A28,">=5/1/2024",A2:A28,"<=5/31/2024")

Thanks!! This worked and my spreadsheet if finally completed!!

 

[jedilefty]

Corrected formula:

=AVERAGEIFS(B2:B28, A2:A28,">=5/1/2024",A2:A28,"<=5/31/2024")

Is there a way to ignore cells with "0"?

 

[Burrgogi]

Is there a way to ignore cells with "0"?

Which column(s) are those 0's contained in?

 

[Burrgogi]

Nevermind, try this formula:

=AVERAGEIFS(B2:B28, A2:A28,">=5/1/2024",A2:A28,"<=5/31/2024", C2:C28, ">0")

I don't know where those zeros are contained in so you'll have to adjust the formula as necessary. i.e. C2:C28