View original (French)

Multiplication with empty cell

Joffccm -  
diablo13800 Posted messages 3469 Registration date   Status Member Last intervention   -
Hello,

I’m coming to you after desperately scouring Google and trying dozens of things that don’t work. It’s my first time posting on the forum, but your messages have often helped me, so I’m sure you’ll know how to respond to my distress once again! Lol

My problem is the following:

I created a relatively simple table: 5 rows of values (E5:E9), 3 rows of formulas underneath to reach the results I need (E10:E12). My cell E10 must contain a multiplication across each cell in the range E5:E9. The issue is that among the 5 value rows, some cells may be empty. So I’d like to use a formula that ignores empty cells if I multiply E5*E6*E7*E8*E9; or treat them as equal to 1.

I tried with the IF(ISBLANK()) formula, but I didn’t get very far… My cell E10 keeps displaying 0 whenever a cell is empty.

I’m using Excel 2016, I don’t know if that can help you.

Thanks in advance to anyone who will try to help,

Joff

Configuration: Mac OS X (10.10.5) / Safari 9.0.1

2 answers

  1.
    PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello

    Try this

    =IF(E5=0,1,E5)*IF(E6=0,1,E6)*IF(E7=0,1,E7)*IF(E8=0,1,E8)*IF(E9=0,1,E9)

    --
    It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the mason - one always learns from one's mistakes
    1
    1. Joffccm
       
      SPLENDID! It works perfectly! I think my laziness prevented me from imagining a solution like this... Haha, thank you very much Philou10120!
      0
  2. diablo13800 Posted messages 3469 Registration date   Status Member Last intervention   1 872
     
    Hello,

    You can also do it like this:
    =PRODUCT(IF(E5:E9<>"";E5:E9;1))
    1