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

Report a Bug
1

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

2

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

3

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

4

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

1 Like
5

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

6

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

7

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
8

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

9

The earliest it’ll land is Thursday.

1 Like
10

Thanks @Mark, now the rocket is fine!!

Saludos

1 Like
11

Hi @Mark and @Jason

I think we have an original problem’s new brother here:

If I try to sum these columns and some ones are empty (blank), the result is wrong

Qt1*Pr1 + Qt2*Pr2 + 1

image

this example can guide you to understand the cases with problems:

image

the trick works:

IF {MyColumn} is empty THEN 0 ELSE {MyColumn}

but I have more than 10 columns and don´t want to duplicate these data dummy every time I find this case. Can it can be fixed and avoid the workaround?

Thanks!

Feliz dĂ­a.

12

What is it you’re actually trying to do here, and what’s the result you expect?

13

something like this Mark:

image
image821×303 11.2 KB

In my case, the formula doesn’t sum +1

Gracias!

14

That might be the right thing in your case, but wrong in others. Maybe Glide is trying to be a little too smart here.

In any case, a hack that works right now is to write 1 as, for example 1*1.

We might revisit all of this when we learn more.