Blank cells not working as zero (0) in Math column

Hi folks,

Working with Math column to create a simple Sum I discovered this rare problem when some cells are blank (empty). My formula is:
Total= Qty1*Price1+ Qty2*Price2+ Qty3*Price3

Everything is fine when all cells have some value but, if there is (are) some blank cell(s) in the formula, the result is completely crazy and funny. See my screenshot please

image

The workaround could be to use the What-If-Else tool but I will have to create more new columns and the APP would load more unnecessary data for this issue. My real APP and formula has 16 tags (variables) and don’t want to create these dummy variables by this bug.

Thanks in advance for the help and patch!

Saludos

Sooo empty columns are considered a “neutral” value. Effectively neutral * X == X and neutral + X == X.

To get what you want you could change your formula to (Qty1+0)*Price1 + (Qty2+0)*Price2 + (Qty3+0)*Price3

No yet @Jason, the same wrong results are shown!

image

I supposed your solution days ago but it didn’t work for me. I tested:

 1) (Q1*P1)*1+ (Q2*P2)*1+ (Q3*P3)*1`
 2) (1*Q1)*P1+ Q2*(1*P2)+ (1*Q3)*(1*P3)`
 3) (0+Q1)*(0+P1)+ (0+Q2)*(0+P2)+ (0+Q3)*(0+P3)`

but no luck!.. another tip?

Gracias de nuevo

Let me get with the math column guru and see what gives.

1 Like

This is a great option. You could also use an array formula in your spreadsheet to perform the same task.

Yes, I know it can be done in my GS but I wanted to perform in Data Editor to improve APP speed to users.

Any news about it @Jason?

Saludos

I believe we’re doing the wrong thing here. The default for multiplication shouldn’t be 1. We’ll fix that.

What you can do right now if you don’t want to wait is to make an If-Then-Else column for quantity, like this:

IF Quantity is empty THEN 0 ELSE Quantity

1 Like

Gracias @Mark

How much will you last to fix it? I can wait 1-2 days more.
I’d rather this than create 16 new IF-Then-Else columns and complicate my APP. I try to keep it light and simple.

Bye

The earliest it’ll land is Thursday.

1 Like

Thanks @Mark, now the rocket is fine!!

Saludos

1 Like