Solved

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

Posted on 2013-01-02
12
149 Views
Last Modified: 2013-01-02
How to do it with the simplest and shortest formula.

Kind regards,
EC
0
Comment
Question by:easycapital
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 38737069
=IFERROR(A1/B1, "n/a")
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38737083
=IF(B1>0,A1/B1,"")
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 38737097
In case you have negative numbers
=IF(B1<>0,A1/B1, "")
0
 

Author Comment

by:easycapital
ID: 38737103
Formula should each row independently and then add each of those results. Eg. A1 / b1 + a2 / b2 + a3 b3 etc.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 38737140
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
0
 

Author Comment

by:easycapital
ID: 38737176
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Martin Liss
ID: 38737265
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

0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 200 total points
ID: 38737284
If you're looking for that result in one cell, use this:

{=SUMPRODUCT(A1:A5,IF(B1:B5>0,1/(B1:B5),0))}

Adjust your range as needed. This must be entered as an array, so once you entered the formula, you have to hit ctrl+shift+enter.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 300 total points
ID: 38737449
Slight variation, also array entered

=SUM(IF(B1:B5,A1:A5/B1:B5))

regards, barry
0
 

Author Comment

by:easycapital
ID: 38737628
Hi, in Europe. So I will try tomorrow when in the office. Thanks, Jp
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 38737696
a demain
0
 

Author Closing Comment

by:easycapital
ID: 38739134
Barry, I went with your answer.

Carolchi, merci.

Thanks for the support!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now