#VALUE! Error

[tony123]

Hi,
I'm receiving a #VALUE error. If I put a zero in the offending field the error goes away, but putting a zero screws up my average total. The fields are an average, where some days there is no information and should be left blank. Why does Excel insist I put a zero in a field?
Thanks.

 

[MrKowz]

Can you please provide the formula you are using, as well as your input data and desired output?

 

[bryonwoods30]

you can use this formula for the average, remember to push control + shift + enter to enter the formula as an array.

=AVERAGE(IF(range <>0,range))

 

[tony123]

=AVERAGE(L95:L99)
This formula works fine on the page it is on.
When called from another page it gives me the error.

An example would be:

m 5
t 3
w 7
t
f 2
AVG = 4
It is forcing me to put a zero in instead of leaving t blank, thus changing my average to 3 instead of 4.

The suggested formula =AVERAGE(IF(range <>0,range)) gives me a #NAME error

 

[MrKowz]

I can't replicate that on my end, but try using:

<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120"></th><td style="text-align:left">{=AVERAGE(<font color="Blue">IF(<font color="Red">L95:L99=0,L95:L99</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[tony123]

Thanks.
I'll give that formula a try.