Originally Posted by
simjambra
... I will now try and get my nut around how you created that formula.
Best to break out formulae into component parts
First the AVERAGE array...
This will generate an array of values such that...
Where trainer value = A3 and Date = B$2 you will get the corresponding value from tots range
Where either of the above does not hold true FALSE is returned
End result ... you have an array of values to Average such as:
The Average will ignore the Boolean False (it's looking for numbers) so you effectively end up with an Average of 0.65 (0.5 + 0.8 / 2)
However, if you should end up with only FALSE values in your Array, eg:
The result is obviously #DIV/0!
(given you're dividing by 0 (no numbers))
The most common approach to ignore errors is to double evaluate... however you can (if you know the data type being returned) use an alternative approach...
Because we know we're returning a number we can use LOOKUP to return the biggest number from an array of 2 values... the 2 values are generated by:
So we end up with 2 values, 0 and the result of our AVERAGE array
The LOOKUP with BIGNUM (9.999etc....) will ensure that the last of the numbers listed is returned
(this utilises the binary search algorithm but I won't try and explain that (as I can't... very well))
So using our 2 examples where AVERAGE returns 0.65 and #DIV/0! respectively...
Whereas
Values in the array that do not match the criteria value (bignum) are ignored... so in the above the Error value is ignored and the 0 returned... thereby avoiding need to double evaluate... if the Average returns a numerical result the LOOKUP will return it, if it generates an error 0 is returned.
I hope that helps (some)
(Of course if you're running 2007 you can use IFERROR(AVERAGE(...),0) ... and even look into using AVERAGEIFS ... note neither are backwards compatible)
Bookmarks