AverageIF

[Bahaa]

I have cells where i need to calculate average but if text was included in certain cells, then i need the formula to show error or "please fill number" so to get alert that number is missing and need to be filled

 

[jasonb75]

Try something on the lines of

=IF(COUNT(range)=5,AVERAGE(range),"error message")

where = 5 refers to the number of cells in the range that should contain numbers.

 

[Bahaa]

I started with this
=AVERAGEIF($G$5:$BF$5,">=0") then incase text entered in cells then i need the formula to turn into "please fill in number instead of text" so the average will be calculated properly. Plus i need to eliminate error #NA or #Div in formula as well

Appreciate if anyone can help.

 

[Bahaa]

Try something on the lines of

=IF(COUNT(range)=5,AVERAGE(range),"error message")

where = 5 refers to the number of cells in the range that should contain numbers.

Thanks Jason, but dont need to limit myslef with only 5 cells, with your formula i need to fill numbers in all my 52 cells to calculate average. can you help over this ?

 

[jasonb75]

5 was just an example, you can change it to 52 to match your range.

 

[Bahaa]

5 was just an example, you can change it to 52 to match your range

Yes i know, but i do not want to be obliged to fill the cell with number cuz this will be filled every week and do not need to change it every time i want to fill the coming 5 next cells. Example your formula covers only 5 cells, if i change it to 52 and did not fill the remaining 47 cells then the formula will give the error message.

 

[jasonb75]

Try

=IF(COUNT(range)=COUNTA(rng),AVERAGE(range),"text found")

This will check that all cells either contain a number or are empty.
You may still get an error if all are empty or there are no numbers other than 0, if that happens then this will mask the #Div/0 error

=IF(COUNT(range)=COUNTA(rng),IFERROR(AVERAGE(range),"No numbers"),"Text found")

 

[Bahaa]

Try

=IF(COUNT(range)=COUNTA(rng),AVERAGE(range),"text found")

This will check that all cells either contain a number or are empty.
You may still get an error if all are empty or there are no numbers other than 0, if that happens then this will mask the #Div/0 error

=IF(COUNT(range)=COUNTA(rng),IFERROR(AVERAGE(range),"No numbers"),"Text found")

Thanks Jason