Need help writing formula to take 6-month average of data, pulling data using a code

[BoneyLit]

Excel Help.xlsx

Hello,

I am trying to figure out how to write a formula that takes the average of the most recent 6 months of column I, "Cost per Unit," while pulling the data based on column A, "Meter Number."

The goal is to be able to just edit the search criteria for different meter numbers to then pull the 6 month average without having to write separate formulas for each Meter's set of data.

I realize my explanation might not be the best and apologize in advance for this but any help would be much appreciated!

Sincerely,
BoneyLit

[etaf]

EDIT Note this goes back 6 months from the latest date for each meter and includes a 7 month - so beware on dates and use - just in case anyone else searching for a similar solution

how about
=AVERAGEIFS(I:I,A:A,L3,C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))

BUT I get a DIV error on 1 of the meters - just looking into - no dates form 6 mths ago

So just checking

also using column C as the date - so not sure what you want last 6 months based on

EDIT - UPDATE

OK if you want 6mth from the lates date for that meter - how about
=AVERAGEIFS(I:I,A:A,L3,C:C,">="&DATE(YEAR(MAXIFS(C:C,A:A,L3)),MONTH(MAXIFS(C:C,A:A,L3))-6,DAY(MAXIFS(C:C,A:A,L3))))

we find the MAX date for column C for that meter and then take 6mths away

[Glenn Kennedy]

or:
=UNIQUE(A2:A54)

and

=AVERAGE(TAKE(FILTER($D$2:$D$54,$A$2:$A$54=M23),6))

assuming your O365 is up to date...

[BoneyLit]

Hi Glenn,

Thanks so much for your help! This works excellently. You are a gentlemen and a scholar and I appreciate you very much!

Sincerely,
BoneyLit

[BoneyLit]

Hi Etaf,

Let me start out by saying if I spent the rest of my life trying to figure out that formula, I'd die a very disappointed man. That said, your formula is top notch and thanks so much for your help!

Sincerely,
BoneyLit

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[etaf]

you are welcome
our formulas do give different results - i think
which i have been looking into - also Leaning TAKE() - thanks glen

ignore my formula - seems to be wrong - average for 1 meter, taking 7 readings and not six cannot work out why
so use glens formula


figured it out,
column C for meter reading 7491408 has 7 readings
starting with 2/2/22
if you go back 6 months from the latest reading in column c
which is 2/8/22 - that is 2/2/22 - so thats why i included that in the 6 months reading

may not be what you want to include - but as i said , Glen is using the last 6 readings, where as i was using the last 6 months in column C which is the end of the period and not what you wanted

needed to get to bottom of it - so anyone else searching the forum will know NOT to use my results

[BoneyLit]

Ok I didn't notice that but thanks for the extra info and for clearing that up! Your formula was still nice to me so I appreciate your help my friend. All the best!!!

Sincerely,
BoneyLit

[etaf]

you are welcome