Avatar of easycapital
easycapital
Flag 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
Microsoft Excel

Avatar of undefined
Last Comment
easycapital

8/22/2022 - Mon
Carol Chisholm

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

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

In case you have negative numbers
=IF(B1<>0,A1/B1, "")
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
easycapital

ASKER
Formula should each row independently and then add each of those results. Eg. A1 / b1 + a2 / b2 + a3 b3 etc.
Flyster

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
easycapital

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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
Flyster

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
barry houdini

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
easycapital

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

a demain
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
easycapital

ASKER
Barry, I went with your answer.

Carolchi, merci.

Thanks for the support!