Formula ignore values

[Brett_Lee]

Here is the formula I have:
{MLI (Sq. Ft.)}*1.50+{Bluemaxx AC Re-New (Ounces)}103+{Attic Air Seal (Energy Saver)}.32+{H20 Wrap}*75+{Power Perfect Box}*1500+{Solar Fan}*785+{NEST Thermostat}*349

It is working perfectly when all of the fields are filled out. However, if there is on single field that isn’t filled out, I get an #ERROR!. How do I bypass, or ignore a blank field. I want the formula to calculate regardless if they’re all filled out or only one is filled out.

[Andy_Lin1]

You’ll want to wrap each “field” with an IF function:
Original: {Field} * Multiplier + ...
New: IF( {Field} , {Field} * Multiplier ) + ...

Giving something like this:

IF( {Bluemaxx AC Re-New (Ounces)} , {Bluemaxx AC Re-New (Ounces)} * 103 ) +
IF( {Attic Air Seal (Energy Saver)} , {Attic Air Seal (Energy Saver)} * 32 ) +
IF( {H20 Wrap} , {H20 Wrap} * 75 ) +
IF( {Power Perfect Box} , {Power Perfect Box} * 1500 ) +
IF( {Solar Fan} , {Solar Fan} * 785 ) +
IF( {NEST Thermostat} , {NEST Thermostat} * 349 )

Hope that helps!

[Brett_Lee]

Brilliant! I love smart people like you! :smiling_face_with_halo: