Multiple functions based on date.

D

Daniel

I would like to create a formula that counts and sums
cell ranges based upon multiple criteria, including a
date. The date criteria seems to be the problem.

I have named ranges for each column.
a b c d
1 Name Score Date Rating
2 Andy 1.00 09/30/2004
3 Betty 0.37 09/......
4 Carol 0.40 ....
5 David 0.84 ....
6 Andy 0.72 09/25/2004
7 Fred 0.24 ....
8 Andy 0.92 08/12/2004

The forumla would be placed in the rating column. For
each row the formula would identify all of the instances
where the name value in that row is found in the
range "NAME" and where the dates in the range "DATE" are
prior to the date in that row. Where these two things
are true, it will take an average of the scores (or sum
them and divide them by the count of them).

In short, find a person's average score preceding the
present entry. In the case of row 2, the Rating would be
0.82.

A million thanks.
 
D

Daniel

Please also note that there are many more columns
involved than are used in my example. The actual columns
are not contiguous. Also, the dates will not be in any
order and sorting is not a desirable option.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top