ERR:510 If function

[Henry_P_Burt_Burt]

The formula =IF(B10>1;=AVERAGE(B6:B10)) gives me ERR:510. If I put the AVERAGE function in another cell and reference it, the IF function works correctly, but I can not embed the AVERAGE function.

[karolus]

Remove the = in Front of AVERAGE

[Henry_P_Burt_Burt]

When I remove the =, I get a #value! error.

[Lupp]

First get a clear idea of what you want.
Even without the syntactically wrong “=”, your formula is an incomplete alternative. The specification tells that this not is prohibited, but that there is a kind of automatic completion to the effect that the missing ELSE-part is treated as if containing FALSE().
Don’t rely on such automatisms, but always create complete versions when using the IF() function: =IF(condition; THENexpression; ELSEexpression)
Let’s assume what you wanted was
=IF(B10>1;AVERAGE(B6:B10);"B10 should be more than 1.")
How can that produce a #VALUE! error? Without knowing your specific case from an attached example sheet, I only can tell that this should indicate that one of the cells in the range B6:B10 is containing a formula returning that error in advance. If so, the error would propagate to the result of your formula.
To make everything clear, you need to attach an .ods file showing the issue for you.

[Henry_P_Burt_Burt]

Sample.ods (15.0 KB)
I have tried with and without the else completed, makes no difference.b6:B10 contain only numbers no formulas. I believe I have attached a sample ods file; I used upload?

[joshua4]

Your sample ODS has B36 instead of, I assume, B15. After using B15 I seem to get the 5-day rolling average just as expected simply by copying down (or up) your formula. No #VALUE? errors.

That is, =IF(B15>0,AVERAGE(B11:B15),0) seems to work.

That said, you might be wanting =IF(NOT(ISBLANK(B15)),AVERAGE(B11:B15),0) if it is possible for a day to have a real 0 value.

If you don’t want ‘ugly’ zeros down the end of your column, you could consider =IF(NOT(ISBLANK(B15)),AVERAGE(B11:B15),""). This comes with a minor caveat if you are going to be using this column for further computation. You can also do a cell format that hides zero, but that seems even more dangerous to me.

[Lupp]

I wouldn’t guess what that sheet actually is expected to return.
What I can see is

1. There is no “#VALUE!” error reported.
2. The one formula (cell C15) showing an uncommon errror has the wrong “=” in front of the AVERAGE() expression in the THENexpression position. This fundamental error was obvious from the beginning (hinted by @karolus).
3. The error messages “#DIV/0!” in the cell range C20:C24 simply state that there is no average of an empty set of values. A sum (0 in this case) can’t be divided by the number 0 of “no numeric values at all”.