How to get average value for specific month in specific year?

[AnonUser]

Hello. I am new to Calc. I tried searching for the answer, but nothing worked. Here is my question:
I have:

• A cell (A4) with a specific date (DD.MM.YYYY).
• A column (D) with dates (DD.MM.YYYY) in chronological order.
• A column (G) with various numerical values in no particular order.
  I want to determine the average value of those cells in the column G that have corresponding dates in the column D which have the same month AND year as the specific date in the cell A4. How do I do this?
  I would appreciate your help.

[Zizi64]

Please upload an ODF type sample file here.

• Are those “dates” numeric values or they are strings only?
• Do you know the function AVERAGEIF()?
• Have you tried the functions YEAR() and MONTH() for the numerical dates in a helper column?

[JohnSUN]

Try

=SUMPRODUCT(AVERAGEIFS(G1:G1000;YEAR(D1:D1000);YEAR($A$4);MONTH(D1:D1000);MONTH($A$4)))

[AnonUser]

Thank you! This worked, it has been solved now.

[Villeroy]

In Calc, how to create a summary table based on categories?

The table in your example_1.ods is not a normalized table. With a normalized table you have plenty of options at hand. Pivot_Mont_Person_Category.ods (67.1 KB) has a normalized table in columns A through D. One row of column labels. Actual data rows below this label row. One column of dates. One column of category names. One column of person names. One column of decimals. The header row has auto-filter buttons. The table can be sorted by any of the 4 columns without spoiling the informa…

[eeigor]

Done
NOTE: You didn’t specify which range to average: the whole range or just the range that fits the condition. In this example, the one that corresponds to the condition.
G3: =AVERAGEIF(D6:D21;B3;G6:G22)

get-avg-by-condition.ods (19.3 KB)
NOTE2: Change the Format Code in G3 to 0.00 to get the fractional part (61.50 instead of 62).

[AnonUser]

Thank you also, I have one more question though. Is it possible for me to specify another cell (For example H2) with a specific date (DD.MM.YYYY) and make it so that the calculation of the monthly average does not include any of the Value columns cells that have a date that goes beyond the specified date in H2?
So for example the function calculates the average of all July 2022 values (month and year of specified date A4), but it does not use any of the values that are beyond 10th of July 2022 (date specified in H2).
This probably sounds very simple, but I am new to this and still trying to get the hang of it all. Thank you for helping.

[eeigor]

A new solution for the new conditions.
G4: =AVERAGEIFS(G7:G22;D7:D22;">="&$B$2;D7:D22;"<="&$B$3)

get-avg-by-criteria.ods (23.3 KB)

[eeigor]

@AnonUser, mark it as a solution if it works for you.

[AnonUser]

I am testing it right now.