Solved

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

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

Kind regards,
EC
0
Comment
Question by:easycapital
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 16

Expert Comment

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

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 48

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

695 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