Solved

Excel formula that does not perform a calculation when cell values are 0 or blank

Posted on 2014-04-15
5
238 Views
Last Modified: 2014-05-09
I have an Excel file. Cells in columns A and B usually contain numerical values but sometimes are blank or sometimes have a value of 0.  On each row, in column C, I am dividing B by A.  To avoid getting division errors, I need a formula that I can copy down column C that will skip over a row when a value in cell A or B on a given row is either blank or 0.
0
Comment
Question by:dbfromnewjersey
5 Comments
 
LVL 4

Assisted Solution

by:Kent Fichtner
Kent Fichtner earned 120 total points
ID: 40001611
=IF(A1=0,0,B1/A1)

That should work.
0
 
LVL 4

Accepted Solution

by:
Kent Fichtner earned 120 total points
ID: 40001616
You could also try this:

=IF(OR(ISBLANK(A1),ISBLANK(B1),A1=0),0,B1/A1)
0
 
LVL 13

Assisted Solution

by:Santosh Gupta
Santosh Gupta earned 60 total points
ID: 40001619
Try

=IF(ISERROR(A1/B1),0,A1/B1)
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 60 total points
ID: 40001628
=IF(A1=0,"",B1/A1)
0
 
LVL 8

Assisted Solution

by:itjockey
itjockey earned 60 total points
ID: 40001745
Try this
=IFERROR(B1/A1,"")

Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

919 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

21 Experts available now in Live!

Get 1:1 Help Now