Multiplying where a field may be blank

✭✭✭✭

Hi!

I am multiplying 3 columns + multiplying the next 3 columns to get a sum of the total cost. However, here is an instance where "1455 REP Gallons Used" is blank. I am getting the old Invalid Operation now.

Would "if error" fix this and if so, where would it go?

=IF(ISBLANK([1455 MU Gallons used]@row), "", IF([1455 MU Gallons used]@row = 0, "", ([1455 MU LBS PER GALLON]@row * [1455 MU Gallons used]@row * [1455 MU Price per lb (\$)]@row) + ([1455 REP lbs per Gallon]@row * [1455 REP Gallons used]@row * [1455 REP Price per lb (\$)]@row)))

Tags:

• ✭✭✭✭✭✭

It depends on what you want the result to be in these types of scenarios. You can make easily make the total cost column be blank by using.

=IFERROR(IF(ISBLANK([1455 MU Gallons used]@row), "", IF([1455 MU Gallons used]@row = 0, "", ([1455 MU LBS PER GALLON]@row * [1455 MU Gallons used]@row * [1455 MU Price per lb (\$)]@row) + ([1455 REP lbs per Gallon]@row * [1455 REP Gallons used]@row * [1455 REP Price per lb (\$)]@row))),"")

• ✭✭✭✭

It gave me a "invalid argument" when I add that.

• Employee

Try this:

=IF(OR([1455 MU Gallons used]@row = "", [1455 MU Gallons used]@row = 0), 0, ([1455 MU LBS PER GALLON]@row * [1455 MU Gallons used]@row * [1455 MU Price per lb (\$)]@row)) + ([1455 REP lbs per Gallon]@row * [1455 REP Gallons used]@row * [1455 REP Price per lb (\$)]@row)

It looks to see if Gallons Used is blank or 0 and if it is, makes this 0 + the rest of the formula. Otherwise if it's not blank or 0, it does your multiplications + the rest of the formula.

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭

Still getting an incorrect argument..

• Employee

Is it possible you may have put a parentheses in the wrong place? Would you mind posting a screen capture of it open in your sheet, like so:

Cheers,

Genevieve