Calculating Percentage based on Dynamic Cells

Hi Everyone,

Need a small help

I need to calculate the percentage based on self populating score. however I want excel/formula to ignore any cells which has "NA" in it so that it calculates the percentage based only on the cells having Numbers.

Now these Cells which contain Numbers might or might not contain numbers based on the "IF" function which decides wether the cell will have numbers or will remain "blank" or have "NA"

To understand better please have a look at the enclosed sample (that would explain my need better)

Thanks In advance

From XL ' s help :

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

So, your NA problem has already been solved by the makers of XL

If you want to disregard the 0 values try

=average(if(your_range<>0,your_range,false)) entered as an array function

( in this formula negative values are taken into account)

The average of 10 and -5 is 2.5, or 250%. If the numbers in B6 and B5 were 10% and -5%, then the result of the formula =AVERAGE(B6:B8) would be (correctly) 2.5%.