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 ?Try something on the lines of
where = 5 refers to the number of cells in the range that should contain numbers.Excel Formula:=IF(COUNT(range)=5,AVERAGE(range),"error message")
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.5 was just an example, you can change it to 52 to match your range
=IF(COUNT(range)=COUNTA(rng),AVERAGE(range),"text found")
=IF(COUNT(range)=COUNTA(rng),IFERROR(AVERAGE(range),"No numbers"),"Text found")
Thanks JasonTry
This will check that all cells either contain a number or are empty.Excel Formula:=IF(COUNT(range)=COUNTA(rng),AVERAGE(range),"text found")
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
Excel Formula:=IF(COUNT(range)=COUNTA(rng),IFERROR(AVERAGE(range),"No numbers"),"Text found")