SOLVED

Simple formula not working

Copper Contributor

I have a spreadsheet created by someone else that was full of formulas that didn't work and I am trying to correct them.  I have hit a simple formula that is making my head spin:

 

=IF(K16=0,"",(L16+(M16*1.5)+(N16*2)))

 

K16=8, L16=8, M16=0, N16=0

 

If M16 is blank, it doesn't work.  I get a #VALUE! error.  If there is a number in it, the formula returns the correct answer.

 

What am I missing?

2 Replies
best response confirmed by SergeiBaklan (MVP)
Solution

@linco1805 

The formula should work if M16 is really blank. But if it contains a formula that returns an empty string "", you'll get an error.

Perhaps this?

 

=IF(K16=0,"",L16+N(M16)*1.5+N(N16)*2)

 

or

 

=IF(K16=0,"",SUMPRODUCT(L16:N16,{1,1.5,2}))

@HansVogelaar 

 

Thank you!  The empty string was my problem!

1 best response

Accepted Solutions
best response confirmed by SergeiBaklan (MVP)
Solution

@linco1805 

The formula should work if M16 is really blank. But if it contains a formula that returns an empty string "", you'll get an error.

Perhaps this?

 

=IF(K16=0,"",L16+N(M16)*1.5+N(N16)*2)

 

or

 

=IF(K16=0,"",SUMPRODUCT(L16:N16,{1,1.5,2}))

View solution in original post