Multiplying where a field may be blank

Elizabeth Aird

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)))

Hollie Green

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))),"")

Elizabeth Aird

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

Genevieve P.

Hey @Elizabeth Aird

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

Elizabeth Aird

Still getting an incorrect argument..

Genevieve P.

Hey @Elizabeth Aird

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

Elizabeth Aird

I did finally get it to work.. thank you!!!