Link to home
Start Free TrialLog in
Avatar of easycapital
easycapitalFlag for Macao

asked on

Dividing to column A by column B and some cells in column B contains zeros.

How to do it with the simplest and shortest formula.

Kind regards,
EC
Avatar of Carol Chisholm
Carol Chisholm
Flag of Switzerland image

=IFERROR(A1/B1, "n/a")
=IF(B1>0,A1/B1,"")
In case you have negative numbers
=IF(B1<>0,A1/B1, "")
Avatar of easycapital

ASKER

Formula should each row independently and then add each of those results. Eg. A1 / b1 + a2 / b2 + a3 b3 etc.
In C1 (or whatever you want your results in) you would put

=IF(B1>0,A1/B1,"")

In C2 you put =IF(B2>0,A2/B2,"") + C1 and then copy down.

Flyster
Hi Flyster.
I need to write one formula which is short and simple which does what I am requesting. I tried the standard sumproduct formula but that divides all the rows and of course I get an error as some rows on column B are equal to zero.
Use this macro. It assumes that there are no headings.


Sub DivideAndSum()
Dim lngLastRow As Long
Dim lngIndex As Long

lngLastRow = Range("A65536").End(xlUp).Row
If Cells(1, 2).Value <> 0 Then
    Cells(1, 3).Value = Cells(1, 1).Value / Cells(1, 2).Value
Else
    Cells(1, 3).Value = 0
End If
For lngIndex = 2 To lngLastRow
    If Cells(lngIndex, 2).Value <> 0 Then
        Cells(lngIndex, 3).Value = Cells(lngIndex, 1).Value / Cells(lngIndex, 2).Value + Cells(lngIndex - 1, 3)
    Else
        Cells(lngIndex, 3).Value = Cells(lngIndex - 1, 3)
    End If
Next
End Sub

Open in new window

SOLUTION
Avatar of Flyster
Flyster
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi, in Europe. So I will try tomorrow when in the office. Thanks, Jp
a demain
Barry, I went with your answer.

Carolchi, merci.

Thanks for the support!