I'm creating an attendance worksheet. At the top I have dates increasing
from left to right. Right now I'm using a counta formula to calculate
attendance percentage based on the total number of events (counta / total
events).

What I want to do is, as time proceeds and more events are added, have the
right-most cell I'm counting be worth 1 and every cell left of that be worth
the cell to its immediate right less 5%.

Here's a visual:
Currently:
Yes + Yes + (blank) + Yes + Yes = 4/5 = 80%

What I would like:
..8145 + .8574 + 0 + .95 + 1 = 3.6219/4.5244 = 80.05%

The idea is to make more recent attendance worth more than past attendance.
I want this calculation to be all inclusive in one cell if possible so I can
leave attendance binary "Yes" and (blank).

I've come close to figuring this out on my own but I can't figure out how to
count the number of cells between one cell and another. Alternatively, if
someone can answer this one I could probably come up with something.

I would put this in A1:
=.95*B1
and drag to the right filling A1:D1
Then put
1
in E1

Then put the yes/blanks in A2:E2
and this in F2:
=SUMPRODUCT(--($A2:$E2="yes"),$A$1:$E$1) / SUM($A$1:$E$1)
(and format the cell as a percentage with 2 decimal positions.)

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


If you find the numbers in row 1 irritating to view, you can hide that row.

Nolan wrote:

I'm creating an attendance worksheet. At the top I have dates increasing
from left to right. Right now I'm using a counta formula to calculate
attendance percentage based on the total number of events (counta / total
events).

What I want to do is, as time proceeds and more events are added, have the
right-most cell I'm counting be worth 1 and every cell left of that be worth
the cell to its immediate right less 5%.

Here's a visual:
Currently:
Yes + Yes + (blank) + Yes + Yes = 4/5 = 80%

What I would like:
.8145 + .8574 + 0 + .95 + 1 = 3.6219/4.5244 = 80.05%

The idea is to make more recent attendance worth more than past attendance.
I want this calculation to be all inclusive in one cell if possible so I can
leave attendance binary "Yes" and (blank).

I've come close to figuring this out on my own but I can't figure out how to
count the number of cells between one cell and another. Alternatively, if
someone can answer this one I could probably come up with something.

--

Dave Peterson