Need to find the Average
 

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!!!

Need to find the Average
 

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.
--
Gary''s Student - gsnu200771


"Cindy" wrote:

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!!!

Need to find the Average
 

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

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!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200771


"Cindy" wrote:

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!!!

Need to find the Average
 

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

"Cindy" wrote in message
...
It worked except for one thing. It's showing -664% and I need it to stay
with a max of 100%. My numbers a

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!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200771


"Cindy" wrote:

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!!!

Need to find the Average
 

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(E11,1,IF(E1<-1,-1,E1))
--
Gary''s Student - gsnu200771


"Cindy" wrote:

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

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!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200771


"Cindy" wrote:

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!!!

Need to find the Average
 

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

On Mar 5, 7:48*pm, Cindy wrote:
It worked except for one thing. *It's showing -664% and I need it to stay
with a max of 100%. *My numbers a

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!



"Gary''s Student" wrote:
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.
--
Gary''s Student - gsnu200771

"Cindy" wrote:

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!!!- Hide quoted text -

- Show quoted text -

Need to find the Average
 

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

"Pete_UK" wrote:

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

On Mar 5, 7:48 pm, Cindy wrote:
It worked except for one thing. It's showing -664% and I need it to stay
with a max of 100%. My numbers a

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!



"Gary''s Student" wrote:
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.
--
Gary''s Student - gsnu200771

"Cindy" wrote:

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!!!- Hide quoted text -

- Show quoted text -

Need to find the Average
 

You're welcome - thanks for feeding back.

Pete

On Mar 5, 10:58*pm, Cindy wrote:
It all worked beautifully!! *Thanks so much for everything....



"Pete_UK" wrote:
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

On Mar 5, 7:48 pm, Cindy wrote:
It worked except for one thing. *It's showing -664% and I need it to stay
with a max of 100%. *My numbers a

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!

"Gary''s Student" wrote:
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.
--
Gary''s Student - gsnu200771

"Cindy" wrote:

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!!!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -