Percentage when both factors are zero should be 100% in my case.

[dshunt]

I was able to get the correct outcome with a formula posted here on the site by changing it. But when using that formula in my example rows 6 & 7 return a value of 100% as well when it should be 0%.
The formula: =IF(C5=0,1,C5/B5). When the Total for the month = 0 and Shipments = 0, (Row 4 in my case) the correct return is 100% which this formula correctly shows. Can someone please assist.
Date Total that month On time % on time
01/30/13 10 10 0 100%
02/28/13 10 5 5 50%
03/28/13 20 5 15 25%
04/28/13 0 0 0 100%
05/30/13 2 1 1 50%
06/30/13 1 0 1 100%
07/30/13 3 0 3 100%

[AndyLitch]

You've got 5 data columns and 4 headers which is a tad confusing ...

[BB1972]

dshunt, welcome to the forum.

Your formula:

Please Login or Register  to view this content.

says that if C5 equals 0, return 1; otherwise, divide C5 by B5. Your example of row 6 & 7 (which is actually C7 & C8, as a row is required for the data headings), those cells contain 0, so your formula returns 1, and formatted as %, your result is 100%.

The formula you seem to require is:

Please Login or Register  to view this content.

- if C5 equals 0, return 0; otherwise, divide C5 by B5.

Hope this helps.

[MoneyMaker]

dshunt, welcome to the forum.


The formula you seem to require is:

Please Login or Register  to view this content.

- if C5 equals 0, return 0; otherwise, divide C5 by B5.

This wouldn't work for the following row

04/28/13 0 0 0 100%

For this example your formula is saying 0 divided by 0 is 0

[MoneyMaker]

Percentage when both factors are zero should be 100% in my case.

That will make Sir Isaac Newton turn in his grave

[MoneyMaker]

When the Total for the month = 0 and Shipments = 0, (Row 4 in my case) the correct return is 100% which this formula correctly shows. Can someone please assist.

04/28/13 0 0 0 100%

0 ÷ 0 ≠ 0
0 ÷ 0 ≠ 1
0 ÷ 0 ≠ ∞

Division by zero is not allowed

[BB1972]

This wouldn't work for the following row

04/28/13 0 0 0 100%

For this example your formula is saying 0 divided by 0 is 0

Not only does my formula not say, suggest, infer or imply that

0 divided by 0 is 0

, the division element does not even occur in the example you've given, as the logical test evaluates to TRUE. While drawing attention to a potential error that may occur due to division by 0 errors is helpful, the rest of your "contribution" to this thread is not.


dshunt,

You should consider uploading a workbook with your sample data in it, as - as Andy has pointed out - your data does not appear to line up with your columns. We can then get you sorted out with a solution that fits your needs.

[MoneyMaker]

Not only does my formula not say, suggest, infer or imply that , the division element does not even occur in the example you've given, as the logical test evaluates to TRUE. While drawing attention to a potential error that may occur due to division by 0 errors is helpful, the rest of your "contribution" to this thread is not.

@BB1972

Firstly let me state I do not seek arguments while answering questions, and I stand by all my three posts in this thread

What I meant to say was that the solution you had provided indeed works when the numerator is zero, but in one of the rows both the numerator and denominator are zeroes in which case your solution will return a value of 0 since all your checking in your formula is for a value of 0 in numerator

As for my sarcastic statement, about founder of Calculus Newton having trouble with the OP's assertion that seeks an answer of 100% when both factors in percentage calculation are zero, holds it's ground since the OP is seeking a FALSE solution to a problem where there is no solution

[BB1972]

What I meant to say was...

What you meant to say? Perhaps your time would be better spent saying what you actually mean to say, rather than directing sarcastic comments towards first-time posters.

As for my sarcastic statement, about founder of Calculus Newton having trouble with the OP's assertion that seeks an answer of 100% when both factors in percentage calculation are zero, holds it's ground since the OP is seeking a FALSE solution to a problem where there is no solution

If the OP has a requirement for his/her own reasons to display 100% if "total for the month = 0 and shipments = 0", who are you to question that? OP is not seeking a false solution to ANYTHING - OP has a requirement to display 100% if the two criteria mentioned are 0, a requirement that Excel can easily accommodate.

The real shame here is that your comments could have been made in a helpful and constructive manner, but instead, they were made in a condescending, sarcastic manner - a manner which, I can assure you, is unwelcome on this forum.

... I stand by all my three posts in this thread

Standing over a post, when you've admitted that the post did not say what you actually meant it to say, is a fairly poor position to argue from; and if that's the level of maturity and intelligence you're going to bring to this debate, then all that remains for me to say is: welcome to my Ignore List.

[MoneyMaker]

@BB1972

Recall from my last post that I do not seek arguments and I know full well where that road leads to.

You have read my memoirs I am sure.

What I left out of those memoirs was my encounter with Irishmen in New England. I don't think I am going to go back down memory lane to relive those painful days.

Now I already see the downfall of coming to this thread, as I have witnessed hacking attempts on my Email account associated with this forum account.

Forums are good business model, not that I have received a single paycheck from any of the forums I contributed at.

So you to suggest that I go else where is the same sort of Chutzpah I heard elsewhere from those who take others for granted.

[MoneyMaker]

I think this is just enough

I would want the forum moderators to cancel my account, since someone has removed the links that I had in my signature

SPAM is a double edged sword whose meaning is defined by those who dictate the terms

Sorry I am not coming back to Excel forum any longer

Bye