How to multiply a range while ignoring zero (0) values?

English
#1

Since I don’t understand the formatting, let’s just look at a single column.

10
5
0

If I do =PRODUCT(A1:A2) then I would get 50. Great!
But if I do =PRODUCT(A1:A3) then I would get 0. BAD!

(Trying to multiply everything together, besides 0’s).

Attempts!

I tried using the IF statement, but it doesn’t seem to use ranges.

I tried =PRODUCT(NOT(<1(A1:A3)),) but that throws error 510.

I tried =PRODUCT(A1:A3>0) but that’s an error sadly. (I really thought it would ignore 0’s).

=SUM(A1A2) works, but =SUM(A1A3) gives 0.

=SUMIF(A1:A3,">0") seemed to work for addition, but =SUMIF(A1:A3,">0",A1*A3) threw an error.

SUMPRODUCT ended up just doing addition instead of multiplying.

I even tried formatting the cells to be blank if they were 0, but it still counted as a 0.

Potential PERSONAL solution

The only way I see right now is if I can get all my cells to change a 0 to a 1, but my cells are like this:

=INDIRECT(“Addons!”&CHAR(COLUMN()+64-4)&$B9+1)

And of coure the “Addons!” is a sheet that I am referencing, and I have like 6 other sheets in these cells, so I would have to manually adjust the formula 6 more times, and spread it across 20 different lines.

That said, even if I solve that, it doesn’t solve the greater question of this post.

EDIT:

Found an ALTERNATIVE solution to my PERSONAL problem:

=IF(INDIRECT(“Addons!”&CHAR(COLUMN()+64-4)&$B7+1)>0,INDIRECT(“Addons!”&CHAR(COLUMN()+64-4)&$B7+1),1)

Kinda wish I could just create a local variable so I could just do variable = NDIRECT(“Addons!”&CHAR(COLUMN()+64-4)&$B7+1)

So I could then just do IF(variable>0,variable,1)

Keep in mind that this solution doesn’t actually solve the post’s main title. Please find a solution for the people in the future that look up this same question but have a different problem that my PERSONAL solution won’t solve!

#3

That’s the correct result. Why “bad”?

Is there a reasion for the exception?
To return a kind of message instead of the mathematical result may be acceptable for one or another reason.
I can’t imagine a justification for treating the 0 as if it was 1.
Of course you can do it, but why should you treat 1E-20 as is, but 0 as 1?
See
disask111130veryStrangeMultiplication.ods (13.6 KB)
Don’t do it.

1 Like
#5

Well values like 0.5 means you reduce the output by half, and that’s intended (Haven’t seen any penalty as bad as 1E-20 yet), but 0 is just a fault with adding the data. Think of it as dividing by 0 in this situation, it’s not the result that I want.

#2

=PRODUCT(IF(A1:A3<>0,A1:A3,1))

making sure you use Ctrl+Shift+Enter with Array Functions

image

2 Likes
#4

So that does seem to work when I place it in a single cell, but if I highlight the cell, and click and drag that bottom right corner to the cell to the right, it doesn’t shift any of the values, and whenever I try to change the new cell, it throws a pop-up menu saying: “You cannot change only part of an array.”

All I am trying to do is go to the new cell and change the A1:A3 to B1:B3.

#6

You wouldn’like to enter every factor (or a respective reference) into your formula like in =IF(A1=0;1;A1)*IF(A2=0;1;A2)*...*IF(A100=0;1;A100) so you will need to accept that the accumulating function PRODUCT() must be used forcing array-evaluation. This comes with disadvantages.
If you insist on your mathematically deprecated way of treating zeros in a product, you need to use a helper column making the distinction.
See:
disask111130veryStrangeMultiplication_2.ods (17.2 KB)

#7

I think there was a misunderstanding.

example
example1150×506 52.4 KB

Looking at the picture, I added a new column. I simply wanted to click and drag the bottom right arrow thingy of the one formula to create a second formula to the right, and then edit the new formula to look at the second column.

For some reason that popup PREVENTS me from updating the COPIED cell.

I’m currently dealing with around 20 columns, so having to manually set up the formula for each column seems weird since this can be done with other formulas.

#8

See
disask111130veryStrangeMultiplication_3.ods (21.5 KB)

Please don’t insert images, but attach example files!

1 Like
#9

No idea why you don’t recommend the final solution personally, but it seems to work perfectly for me.

Thanks for the help! <3

#10

Don’t know what you call the “final solution”.
And I do not recommend any solution for any task which actually is a mistake in itself.

#11

D7 but dragging the fill handle with ctrl.

I know you say you don’t recommend it, but it works. Everything else I tried didn’t work.

Your other solutions are just too complicated and requires me to make massive changes to my sheets.

If it works it works.

#12

In this case, it is possible to convert the formula as an array by wrapping it with:
=SUMPRODUCT(PRODUCT(IF(A1:A3<>0,A1:A3,1)))
without need Ctrl+Shift+Enter

1 Like
#13

:open_mouth: ! BEAUTIFUL!
The perfect solution.

Thank you so much! :3

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.