Formula Help

  • Thread starter Thread starter picktr
  • Start date Start date
P

picktr

difficult formula
How to explain this.

In a range of cells, row J4:AB4

I will be entering numbers weekly, eventually filling up to AB4.

When I enter a number into, lets say, O4, I want a calculation
of the average of ONLY L4, M4, N4

Next week, when I enter a number into P4, I want a calculation
of the average of ONLY M4, N4, O4

So the result, in the cell that the formula is in, is changing weekly.

I hope this is clear.

Thanks,

Tom (e-mail address removed)
 
Please do not multi-post

Peo has given a suggestion to your post in:
microsoft.public.excel.misc
 
difficult formula
How to explain this.

In a range of cells, row J4:AB4

I will be entering numbers weekly, eventually filling up to AB4.

When I enter a number into, lets say, O4, I want a calculation
of the average of ONLY L4, M4, N4

Next week, when I enter a number into P4, I want a calculation
of the average of ONLY M4, N4, O4

So the result, in the cell that the formula is in, is changing weekly.

I hope this is clear.

Thanks,

Tom (e-mail address removed)


---
Click to expand...

It sounds as if you want the average of the most recent three entered values,
and that there you will not be skipping any cells as you fill in the numbers.

If so, the *array-entered* formula:


=AVERAGE(TRANSPOSE(OFFSET(I4,0,ROW(INDIRECT(COUNT(J4:AB4)&":"&COUNT(J4:AB4)-2)))))

will do what you want.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

This formula will give an error if there are less than three entries. What do
you wish to do in that event?


--ron
 
Back
Top