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
Carol Chisholm

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

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``````
SOLUTION
Flyster

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.