Array Entered formula not working fine

[paritoshmehta]

Hi ,

I am using two formulas which are identical, to calculate the averag
using an array entered formula:

1)
=AVERAGE(IF((QE_NAME= G3 )*ISNUMBER(SCORE),SCORE))

2)
=AVERAGE(IF((TL_NAME= "h48")*ISNUMBER(SCORE),SCORE))

the first formula is working fine but the secong one is showing th
#n/a error.... dont know why..

checked the TL_NAME range, it is fine.....

does someone have any idea why this is happening?

 

[Vasant Nanavati]

Why is "h48" in quotes but not G3?

 

[paritoshmehta]

oops.... there are no quotes.... also, both of them are entered usin
ctrl+shift+ente

 

[Domenic]

Hi ,

I am using two formulas which are identical, to calculate the average
using an array entered formula:

1)
=AVERAGE(IF((QE_NAME= G3 )*ISNUMBER(SCORE),SCORE))

2)
=AVERAGE(IF((TL_NAME= "h48")*ISNUMBER(SCORE),SCORE))

the first formula is working fine but the secong one is showing the
#n/a error.... dont know why..

checked the TL_NAME range, it is fine.....

does someone have any idea why this is happening??

Make sure that your named range TL_NAME covers the same number of cells as
your named range SCORE.

Hope this helps!

 

[Peo Sjoblom]

Most likely because TL_NAME is not the same size as SCORE

 

[Daniel.M]

Hi,

Either
1. Arrays TL_Name and SCORE not of same size
2. One of the cells of TL_NAME contains an error value (#N/A) itself.

Comparing #N/A with a number produces #NA. Averaging a series of values where
ONE is #N/A produces #N/A.

Maybe:
=AVERAGE(IF(NOT(ISERROR(TL_NAME)),IF((TL_NAME= H48)*ISNUMBER(SCORE),SCORE)))

And change your first formula as well

Regards,

Daniel M.