easycapital

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

Kind regards,

EC

=IFERROR(A1/B1, "n/a")

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

In case you have negative numbers

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

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

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

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

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

Flyster

ASKER

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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi, in Europe. So I will try tomorrow when in the office. Thanks, Jp

a demain

ASKER

Barry, I went with your answer.

Carolchi, merci.

Thanks for the support!

Carolchi, merci.

Thanks for the support!