Find one cell up??

[pjhdude]

My questions today is, How can I find one cell above a formula I have created?
I have a form that does a countif and displays the weekly average of my bowling team. I want to show their average from the week previous as well.

How?

Can I just use the new average cell and see show the one above it???

Thanks

[protonLeah]

You need to explain your calculations. For Bob you have a this weeks average as 193 but you added the three prior weeks and divided by 3. I don't understand why you are adding two previous weeks and calling it this weeks average. Where did the 3 come from and what happens to your formula when you fill in data for week 4?

In the table, none of the rows above have anything to do with any previous week since they relate specifically to individual record rows (the row above Paul belongs to Bob, and there is no row above Bob???)

[pjhdude]

It's bowling scores where each week is a series of 3 games. The formula is adding up the sum and getting there average. It doesn't matter how many weeks are bowled

You need to explain your calculations. For Bob you have a this weeks average as 193 but you added the three prior weeks and divided by 3. I don't understand why you are adding two previous weeks and calling it this weeks average. Where did the 3 come from and what happens to your formula when you fill in data for week 4?

In the table, none of the rows above have anything to do with any previous week since they relate specifically to individual record rows (the row above Paul belongs to Bob, and there is no row above Bob???)

[protonLeah]

O.K. If each week is the total scores of three games played, then use a couple of helper columns.
Use

Please Login or Register  to view this content.

in column T to count the number of weekly score totals for each player.
In U4 use:

Please Login or Register  to view this content.

to assume that the max is the number of the last weekly meeting.
In column C, calculate the average of the last week's three games for each player:

Please Login or Register  to view this content.

in column B, calculate the average of the previous week's three games for each player:

Please Login or Register  to view this content.

---

So, the INDEX formulas are using the cell in the same row as the formula and the column corresponding to the week in U4 Bob -> H4/3 -> 588/3 = 196