SOLVED

Calculating averages in a table, based on two criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1632521%22%20slang%3D%22en-US%22%3ECalculating%20averages%20in%20a%20table%2C%20based%20on%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1632521%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20with%20different%20type%20of%20values%20in%20columns%2C%20where%20the%20two%20first%20columns%20is%20a%20date%20and%20the%20day%20of%20the%20week.%3C%2FP%3E%3CP%3EI'm%20trying%20to%20calculate%20averages%20based%20on%20a%20specific%20weekday%20in%20a%20specific%20date%20range.%20For%20instance%3B%20I%20want%20to%20see%20the%20average%20of%20all%20Thursday's%20values%20during%20the%20last%204%20weeks%2C%20or%2C%20the%20average%20of%20the%20Sunday's%20values%20during%20last%20month.%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20able%20to%20create%20one%20of%20the%20two%2C%20i.e.%20the%20date%20range%20averages%2C%20or%20the%20weekday%20averages%2C%20but%20not%20the%20two%20in%20combination.%26nbsp%3B%3C%2FP%3E%3CP%3ESuggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1632521%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1632849%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20averages%20in%20a%20table%2C%20based%20on%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1632849%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F781266%22%20target%3D%22_blank%22%3E%40HenrikGd2o%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20AVERAGEIFS()%20or%20a%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1632988%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20averages%20in%20a%20table%2C%20based%20on%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1632988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20get%20DIVISION%2F0%20when%20using%20this.%20I%20need%20to%20enter%20a%20starting%20date%20and%20end%20date%20as%20a%20condition.%20However%2C%20I%20can't%20get%20it%20to%20accept%20a%20cell%20reference%20that%20should%20be%20higher%2Flower%20that%20a%20date%20specified%20in%20a%20cell%20outside%20the%20table.%20It%20is%20not%20allowed%20to%20enter%20%22%26gt%3B%22%20together%20with%20a%20cell%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a table with different type of values in columns, where the two first columns is a date and the day of the week, followed by several columns with different values (e.g. sales).

I'm trying to calculate averages based on a specific weekday in a specific date range. For instance; I want to see the average of all Thursday's values during the last 4 weeks, or, the average of the Sunday's values during last month. 

I'm able to create one of the two, i.e. the date range averages, or the weekday averages, but not the two in combination. 

Suggestions?

4 Replies
Highlighted

@HenrikGd2o 

Use AVERAGEIFS() or a pivot table.

 

Highlighted

@Detlef Lewin 

I only get DIVISION/0 when using this. I need to enter a starting date and end date as a condition. However, I can't get it to accept a cell reference that should be higher/lower that a date specified in a cell outside the table. It is not allowed to enter ">" together with a cell reference.

 

Highlighted
Best Response confirmed by HenrikGd2o (New Contributor)
Solution

@HenrikGd2o 

 It is not allowed to enter ">" together with a cell reference.

 


Sure it is allowed. Lookup the many tutorials on the web.

Use ">"&A2.

 

Highlighted

@Detlef Lewin 

I missed the '&' sign, and now it works.

 

Thank you!