Hello all!
I use this formula to return data from a large spread sheet, it was designed to accomidate the posibilty of blank rows being randomly inserted and I thought it worked fine until I started looking very closly.
It finds and averages the data points for a list of employees stored in a named range at $A$2 What I discovered is that some of the cells this formula reads may be blank or contain a dash, causing the average to be wrong.. How can I get it to ignore blank cells?
{=SUMPRODUCT(IFERROR(ISNUMBER(MATCH(ROW('Month-3'!$B$1:$B$6003),MATCH(INDIRECT($A$2),'Month-3'!$B$1:$B$6003,0)+MATCH($AJ13,'Month-3'!$A$1:$A$6003,0)-MATCH($AJ$2,'Month-3'!$A$1:$A$6003,0),0))*'Month-3'!E$1:E$6003,0))/SUMPRODUCT(--(IFERROR(MATCH(INDIRECT($A$2),'Month-3'!$B$1:$B$6003,0)>0,0)))}
Anything you can do would be so appreciated!
In the sample work book cell G13 needs to return the average of the highligted cells on sheet "Month-3"
Bookmarks