# Ratios and #DIV/0 errors

#### kegger99

##### New Member
Hi everyone!

First time to post here, but I have enjoyed watching Mr. Excel with Leo Laporte during the old "Call for Help" and "Lab with Leo" television shows...which is why I'm here today in need of this forum's assistance.

I wish to thank EVERYONE in advance for your insight and assistance with my question. I have run into a "road block" and need to have someone with more Excel knowledge assist me with a solution or point me in a different direction.

I'm using Excel 2007 (SP2) on a Windows PC. I've uploaded a copy of my spreadsheet to Google Docs...so you can see what I've done (and hopefully the solution).

ProductionReport

I've been given the task to create a Production Report for each employee in our office. The first tab (Data Entry) is where our office administrator would type in the daily results for each employee. This page is pure data entry, although I've placed the monthly summary at the bottom of each month for the admin's convenience (this is not the problem).

I've typed values into the first tab (Data Entry) so that some output values (Reports tab) can run through the formulas. That said, I'm only focusing this question toward the January values on the Reports tab.

The 2nd tab (Reports) is where I'm running into a problem. Specifically with the 2011 Ratios section. There are several cells that have the same problem. Its a simple math issue...."any number divided by zero"....but Excel outputs the code #DIV/0....however I need a value there which I can turn into a ratio (ie, 1:0).

I'll use cell B17 as an example. It references cell B8 (value = 6) divided by cell B9 (value = 0). In our industry, it it necessary to show a value of zero if no placements have been made. So the correct answer for cell B17 should be 6:0.

I realize that Google Docs doesn't give the option to format the cell into a ratio style...so the focus is on the formula to give the correct value in Excel 2007.

This is certainly not the only problem with this spreadsheet, but I think that the solution to this formula (with your kind assistance) should get me past this "road block" and back on course toward completion.

Thanks to all!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### shg

##### MrExcel MVP
Welcome to the board. Perhaps,

=IF(N(B9)=0, N(B8) & ":0", B8/B9)

#### kegger99

##### New Member
Hi shg4421....

Thank you for your quick reply to my question. I've been out of the office the last few days and didn't have a chance to check on my replies.

I'll test the formula today and report back a bit later.

Thanks again for your assistance!

#### kegger99

##### New Member
Cheers to you shg4421....

Thanks for the formula that solved my problem!

Best wishes to you!

Replies
1
Views
181
Replies
0
Views
443
Replies
3
Views
809
Replies
3
Views
507
Replies
7
Views
423

Threads
1,191,005
Messages
5,984,121
Members
439,872
Latest member
noaman79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back