Check a range for one or more non-numeric values (or blanks)

[DashboardNovice]

In the attached file, I am using cell the value in cell H2 to define the number of days to take an average of, from column G.

See cell H1706. If you go 10 days back, you will be referencing the range in the red box. I am trying to build in a test to check if the last x number of cells (10 in this case due to the value in H2) contain all numbers or not. If they do contain all numbers, then proceed with taking the average. If one or more of those cells contains a blank, a text or anything else other than a number, then the formula in column H would not calculate an average and would just return a "".

I tried a SUMPRODUCT with a nested ISNUMBER function and this didn't work.

In short, calculate an average for the last x number of days only if all of those cells in column G contain numbers, otherwise return a "".

Can anybody point me in the right direction please?

 

[NARAYANK991]

Hi ,

I am not sure I have understood you , but you can use either the COUNT function or the COUNTBLANK function. Using the COUNT function , you can have :

=IF(COUNT(OFFSET(G1714,-$H$2+1,,$H$2))=$H$2, your average formula , "")

The above formula will be in H1714.

Narayan

 

[DashboardNovice]

Hi ,

I am not sure I have understood you , but you can use either the COUNT function or the COUNTBLANK function. Using the COUNT function , you can have :

=IF(COUNT(OFFSET(G1714,-$H$2+1,,$H$2))=$H$2, your average formula , "")

The above formula will be in H1714.

Narayan

I felt like I was overlooking something more simple. This makes sense. I will give it a try. Thank you.