Unable to resolve #VALUE! being displayed

[oomfh]

I am linking many worksheets - using formulas that display blanks instead of 0s.

I can't determine why this formula displays #VALUE! where:
=IF(+B6+G6+L6+Q6+V6+AA6+AF6=0,"",+B6+G6+L6+Q6+V6+AA6+AF6)

Where Q6 is:
=IF(+'7Jan%'!K6=0,"",+'7Jan%'!K6)

Where K6 is blank

I think K6 is the culprit, but I want that cell to be blank when the value is 0.

 

[MrKowz]

Try:

<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>Worksheet 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">=IF(<font color="Blue">SUM(<font color="Red">B6,G6,L6,Q6,V6,AA6,AF6</font>)=0,"",SUM(<font color="Red">B6,G6,L6,Q6,V6,AA6,AF6</font>)</font>)
</td></tr></tbody></table></td></tr></table><br />

 

[oomfh]

Thank you MrKowz,

Ugh, this is cascading . . . this also gives me #VALUE!

=IF(+AK7/AL7=0,"",AK7/AL7)

 

[MrKowz]

The problem is that you can't perform mathematical operations on string functions, so that formula would have to be:

<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>Worksheet 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">=IF(<font color="Blue">OR(<font color="Red">AK7="",AL7=""</font>),"",AK7/AL7</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Scott Huish]

You can perform mathematical functions on strings as long as the string represents a number, the math will coerce it.


=IF(AND(COUNT(AK7:AL7)=2,N(AL7)),AK7/AL7,"")