Need to find the Average

[Cindy]

I am working on a sales spreadsheet and I need to take each month and find
the average increase or decrease in sales but using a %.

Example of spreadsheet:
January Sales
Current Sales Renewal Sales Difference
$4,162 $4,132 ($30)
$770 $663 ($107)
$4,932 $4,795 ($137)

I need to take the difference column and get an average % of either positive
or negative with the max of 100%

I hope I explained right - been working on it for awhile & now brain is
fried...

Thanks for all help!!!

 

[Gary''s Student]

Ignore the row-by-row differences (that is ignore column C).

=(SUM(A:A)-SUM(B:B))/SUM(A:A) and format as percentage to display:
2.78%
for your data

Note that the percentage may end up being either positive or negative.

 

[Cindy]

It worked except for one thing. It's showing -664% and I need it to stay
with a max of 100%. My numbers are:

Column A Column B
$1920 $0
$690 $690
$395 $405
$7780 $0
$360 $360

I know it would be in the negative, which is fine but I wanted to be within
100%. How would I do that?

Thanks again!

 

[David Biddulph]

I don't know where you get your -664%? The formula gives +86.94% from the
numbers you listed here.

 

[Gary''s Student]

We will fix two things. If column B is greater than column A we want the
increase to be Positive, not negative:

=(-SUM(A:A)+SUM(B:B))/SUM(A:A) in a cell like E1

We then need to limit this value , so in F1 enter:

=IF(E1>1,1,IF(E1<-1,-1,E1))

 

[Pete_UK]

Try it like this:

=IF(SUM(A:A)-SUM(B:B)<0,MAX(-1,(SUM(A:A)-SUM(B:B))/SUM(A:A)),MIN(1,
(SUM(A:A)-SUM(B:B))/SUM(A:A))

formatted as percentage.

Hope this helps.

Pete

 

[Cindy]

It all worked beautifully!! Thanks so much for everything....

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

It all worked beautifully!!  Thanks so much for everything....






- Show quoted text -