Divide by zero and get 2 different answers

rfinnegan

Board Regular
I am measuring the increase in sales from one month to the next and want to show the percentage increase.

Here's the formula I've been using --
=IF(ISERROR(SUM(G7/D7)),0,(SUM((SUM(G7/D7)-1))))

Here' show it handle s the following events -

If there was 1 sale in January and 5 sales in February, Excel shows an increase of 500%.
Great.

If there were 0 sales in January and 0 sales in February, Excel shows 0%.
Great.

But if there were 0 sales in January and 5 sales in February, Excel shows 0%.
That is mathmatically correct, but it doesn't show that there WAS an increase in sales.

Is there a way to place something in the cell (astriks for example) instead of 0's that would indicate an increase in sales, even though mathmatically it's 0%?

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Scott Huish

MrExcel MVP
Based on you results you say you want, not sure what the -1 is for, but there is no need for ISERROR or SUM:

Perhaps:
=IF(D7,G7/D7,IF(G7,"*****",0))

mortgageman

Well-known Member
I am measuring the increase in sales from one month to the next and want to show the percentage increase.

Here's the formula I've been using --
=IF(ISERROR(SUM(G7/D7)),0,(SUM((SUM(G7/D7)-1))))

Here' show it handle s the following events -

If there was 1 sale in January and 5 sales in February, Excel shows an increase of 500%.
Great.

If there were 0 sales in January and 0 sales in February, Excel shows 0%.
Great.

But if there were 0 sales in January and 5 sales in February, Excel shows 0%.
That is mathmatically correct, but it doesn't show that there WAS an increase in sales.

Is there a way to place something in the cell (astriks for example) instead of 0's that would indicate an increase in sales, even though mathmatically it's 0%?

actually, it is NOT correct. The formula for percent change is (new-old)/old. (5-0)/0 is infinite not zero. More to the point - what do you want to see as in increase from 0 to 5? The word "Increase"? Do you want to see the same thing if you have an increase from 0 to 10?

Replies
1
Views
247
Replies
65
Views
3K
Replies
3
Views
347
Replies
0
Views
513
Replies
3
Views
414

1,191,169
Messages
5,985,059
Members
439,937
Latest member
MAlhash

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.

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

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