Any more than a 100% is just excess

[Squeezo]

A bit of background

I want to produce a chart that shows a seasonal monthly percentage
shift in sales.
So what percent do sales change between March and April?
In cell A1 I have the number of sales for March as 1 unit
In cell B1 I have the number of sales for April as 55 units.
In B2 I have a formula that works out the percent change between these
months.
=(B1-A1)/A1
As a percent this will return 5400%

When I am displaying my graph that shows the different sales the chart
sometimes becomes difficult to visualise with figures of 5400%
shooting off into space. So I have decided to put a cap of 100% on any
result that is inclined to do this to see how that works. Could
someone tell me a formula that I could incorporate into this following
equation that basically returns a hundred percent or negative a
hundred percent as the maximum/ minimum?

=IF(Report!G47="no data","no data",IF(Report!F47="no data","no
data",(Report!G47-Report!F47)/Report!F47))

Where [Report!F47] is equal to A1
Where [Report!G47] is equal to B1
The rest of it is mumbo jumbo about returning the text "no data" if
that is what is written in either A1 or B1.

Any help would be graciously and humbly received.

Cheers,
D

 

[Paul Corrado]

D,

Since Net Sales can be negative you can change the last to part of your
calculation to this and it will return NMF if the prior period's sales were
negative or place a max/min of +-100% on any other period.

IF(F15<0,"NMF",SIGN(F16-F15)*(MIN(ABS(F16-F15)/F15,1)))

HTH

PC