Showing percentage trends

[Paul B]

I am trying to tracking yoga class attendance. I have a
column of numbers (by week) and would like to know the
percentage of increased or decreased attendance. But I
don't want the increase from the last yoga class, I want
to know the difference between the average class size to
that date.

Also, can I make my positive percentages one color and my
negative percentages another color?

Thanks for any light to you may be able to shine on this
conundrum.

 

[muziq2]

If you have the class attendance in column A you can use this formula i
column B to get the percent change from average attendance.

=A1/average(a:a)-1

Format the column with your averages to percent.

To have positive percentages and negative percentages show up in
different color - select Format - Conditional Formatting. In the bo
that pops up select - cell value is - less than - 0 and then clic
format and pick the color you want for negative value.

Hope this helps

 

[Paul B]

Thanks for the help! I've got one more question. I now
have column A (with yoga numbers) and column B (with
percent change). At the bottom of my page I have some
summaries with the totals and averages and such. I would
like to have the most recent (bottom cell with value)
percent change shown there.

I've tried to refernce the approiate cell with COUNT
(B2:B53)-1 but I can't figure out how address a cell with
a formula in it. My method was this: to get to B14 I
tried: =B(COUNT(B2:B53)-1)

But alas, it does not work! Any hints?

 

[Peo Sjoblom]

One way

=OFFSET(B2,COUNT(B2:B53)-1,)

 

[Paul B]

The OFFSET works, except when the data is incosistent.
For example some weeks the yoga instructor forgot to
write the attendance. In this case, those blanks throw
off the COUNT. Any other ideas?

 

[Peo Sjoblom]

Tell him not do that

=INDEX($B$2:$B$53,MATCH(9.99999999999999E+307,$B$2:$B$53))

 

[Paul B]

Thanks so much for your help! It totally worked.
Someday, I'd like to figure out the logic behind your
solution. For now I've got work to do!

P.S. I'll let that yoga teacher have it.