Trouble with Excel cell display (formatting)

[Phil115]

I have made a spreadsheet in Excel to keep a record of, and a weekly average of my blood pressure and glucose levels. The daily test results are entered in columns. In the cells that average the column results for the week, I am having difficulty with the displayed result.

This is the original formula, which hides error messages within the averaging cell.
This seems to work fine, and the error messages are hidden.
=IF(ISERROR(AVERAGE(C8,C12,C16,C20,C24,C28,C32)),"",(AVERAGE(C8,C12,C16,C20,C24,C28,C32)))

When I attempt to round the result to a whole number by using the addition here,
=ROUND(IF(ISERROR(AVERAGE(C6,C10,C14,C18,C22,C26,C30)),"",AVERAGE(C6,C10,C14,C18,C22,C26,C30)),0)
I get a “#VALUE” error in the averaging cell.

Can anyone tell me what I am doing wrong, or if there is a better way to do this?
Thanks in advance!

 

[Sweenster]

Hi,

You are trying to round a string ("") when IsError = True.
Moving Round() to just the false part should do the trick

e.g. =IF(ISERROR(AVERAGE(C6,C10,C14,C18,C22,C26,C30)),"",ROUND(AVERAGE(C6,C10,C14,C18,C22,C26,C30),0))

Regards
Mark

 

[Phil115]

That did it!

Thanks a lot for the fix, and the lesson in formula logic!

Phil