[SOLVED] Only average if cells contain a certain word [closed]

asked 2019-03-05 09:31:57 +0100

Laniakea gravatar image

updated 2020-09-26 11:57:21 +0100

Alex Kemp gravatar image


In this example I'd like F7 to calculate the average time of the range D5:D29 only if the cell left to it contains the word "Monday" i. e. C7 shall average all times on mondays.

How do I go about solving this?

image description

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Laniakea
close date 2019-03-14 08:54:49.829353


I think a Pivot Table with a grouping by Weekday might give you the result you need.

robleyd gravatar imagerobleyd ( 2019-03-05 09:46:29 +0100 )edit

Also see AVERAGEIF (like =AVERAGEIF(C5:C29;"Monday";D5:D29)) - but that would only work if your column C actually contains texts, not dates formatted using NNN.

Generally, what @robleyd recommends is better.

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-05 10:22:23 +0100 )edit