SUMPRODUCT first N even rows in a column (with criteria) then AVERAGE

[jocknetstone]

Hey there, I’m new. I am trying to average the total minutes of first N -- in this case, 3 -- movies watched by a customer. Please see my sheet (remove spaces in link):

Please Login or Register  to view this content.

In column A, you can see the extracted numbers from the minutes texts so that it will add up. Column B is where the movie titles along with their duration can be marked complete by the customer using the dropdown in column C. The formula, found in cell E14, I’ve used to average the said minutes is:

Please Login or Register  to view this content.

However, I received an error saying the “AVERAGE caused a divide by zero error.”

Appreciate any help,
Jock

[AliGW]

Welcome to the forum.

Is this a Google Sheets or Excel query? I am presuming the former.

[janmorris]

heres a different approach that is less complex:

basically, do a SUM(range)/COUNT(range)

this way there is no need for any arrays.

[AliGW]

Sorry for off-topic interjection:

Although there is no official rule regarding this behavioru, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

[janmorris]

although google sheets are not downloaded, they are accessed, i understand what you are trying to say.

[AliGW]

Not quite! Thanks for adding the requested detail, however you still need to provide the link!!!

[jocknetstone]

AliGW

Thanks it is indeed the former as linked in my post. For some reason the tags didn’t show up.

janmorris

Yep I initially tried those functions, but they did not work for my use case: It results in 0, and there is no argument that meets a specific criterion.

[janmorris]

i have duplicated the sheet, reformatted the layout, and provided two working solutions.

unless you specifically need the text to read "xx minutes", then you can just use col E, but if you really must show "xx minutes" then use col B and C.

note: the sheet was NOT downloaded, the link is in the OP.

[janmorris]

the average of the first N (example 3) movies watched is now working

using an ArrayFormula

Please Login or Register  to view this content.

if you want to change the amount, for example the first 4 movies, then you need to change the code a follows:

Please Login or Register  to view this content.

if you want the LAST 3 movies watched, then change SMALL to LARGE:

Please Login or Register  to view this content.