Hi ,

The principle is quite straightforward ; since the AVERAGE or SUM function itself ignores text values when averaging or summing , we need to introduce a mathematical operation to generate the error ; when a text value and a number are added , subtracted , multiplied or divided , Excel will return an error value ; you need to take care that when multiplying or dividing use 1 for the number , and when adding or subtracting use 0 for the number.

The second point you need to remember is that when the SUM or AVERAGE function is used on its own , you do not need to array enter it , since when you pass an array to the SUM function , as in =SUM(A1:A5) , Excel knows the parameter is an array , and returns the correct result. But when you carry out a mathematical operation as in :

=SUM((A1:A5)*1)

you need to array enter it because the first operand is an array , while the second is a scalar value , just 1. If you do not array enter it , Excel will carry out the multiplication only on A1 , and not on the others ; once you array enter it , using CTRL SHIFT ENTER , Excel does A1*1 + A2*1 + A3*1 + A4*1 + A5*1 ; where any of the cells contains text , this mathematical operation will generate an error.

Narayan