excel 2016 value error_1

Frank .S
Frank .S used Ask the Experts™
on
** excel 2016 - value error_1 **
hi experts, could you please help with this worksheet error

excel 2016 value error_1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kesavan JeganarayananIT Consultant

Commented:
Why you need sum you are already using +

=IF((E36+K36)=0,"",(E36+K36))
Try:
=if((e36+k36)=0,"",E36+K36)
The Sum() function is used to sum a range of cells.
Rob HensonFinance Analyst

Commented:
I would suggest that either E36 or K36 has a text value which cannot be used in a sum.

E36 appears to have a number but what is in K36? It looks blank but if it is a formula that returns "" then it would be classed as text.

Looking at your other question, I assume you have this formula (or similar) in K36:
=IF(SUM(S7:S26)=0,"",SUM(S7:S26))

Because column K has no entries the SUM gives 0 so will give the "" which is causing the error in the comparison with E36.

You can get round it though, but the other way to what is suggested by the comments above. SUM function actually ignores text but by including the + within the sum it is doing the + first and getting an error. Try this instead:

=IF(SUM(E36,K36)=0,"",SUM(E36,K36))
Finance Analyst
Commented:
Alternatively, allow for the blanks:

=IF(OR(E36="",K36=""),"",E36+K36)

Or, in the example, if you wanted the result to be just the value of E36 then:

=IF(E36="",0,E36)+IF(K36="",0,K36)

If both are blank and you want blank as the result, rather than zero:

=IF(AND(E36="",K36=""),"",IF(E36="",0,E36)+IF(K36="",0,K36))
NoahHardware Tester and Debugger

Commented:
Hi there! :)

In addition to the points mentioned about your usage of SUM() above, I would recommend you put everything into an IFERROR formula. I find it a good practice.

Example: =IFERROR(IF(SUM(E36,K36)=0,"",SUM(E36,K36)),"NA")
Rob HensonFinance Analyst

Commented:
Usage of IFERROR is only good practice if you can be certain of the errors that could occur and have allowed for them. If not then it will mask all errors and you may not get the result you're expecting.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial