difficult formula

[picktr]

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)

 

[Peo Sjoblom]

One way

=AVERAGE(OFFSET($J$4,,COUNT($J$4:$AB$4)-2,,-3))

assuming that there are numbers and they will be filled without any blanks
from left to right

 

[picktr]

=AVERAGE(OFFSET($J$4,,COUNT($J$4:$AB$4)-2,,-3))

I do need it skip cells with text that I have to enter.

Again,

Thank you!

To

 

[Robert Christie]

Hi Peo

=AVERAGE(OFFSET($J$4,,COUNT($J$4:$AB$4)-2,,-3))

I could only get the correct answer by changing -2 to -1
What does the -2 actually do?
Numbers used in test a simple 1, 2, 3 =answer average 2

TIA
Bob C.

 

[Ron Rosenfeld]

=AVERAGE(OFFSET($J$4,,COUNT($J$4:$AB$4)-2,,-3))

I do need it skip cells with text that I have to enter.

Again,

Thank you!

Tom

Array-enter:


=AVERAGE(TRANSPOSE(OFFSET(A4,0,LARGE(ISNUMBER(J4:AB4)*COLUMN(J4:AB4),{1,2,3})-1)))

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


--ron

 

[Robert Christie]

My mistake Peo

Just read the original post again.
All is clear now.

Regards Bob C.

 

[picktr]

Ron,

=AVERAGE(TRANSPOSE(OFFSET(A4,0,LARGE(ISNUMBER(J4:A
B4)*COLUMN(J4:AB4),{1,2,3})-1)))

The formula needs to:

1: calculate the average of the last 3 numbers entered
starting from the 1st cell to LEFT of active cell
2: skip all text in cells

ex: J4= 20 J5=50 J6=xxx J7=xxx J8=xxx J9=30
J10=60(active cell)
answer should be: avg of 30, 50 , 20 = 33.333333

Thanks,

To

 

[Ron Rosenfeld]

Ron,

=AVERAGE(TRANSPOSE(OFFSET(A4,0,LARGE(ISNUMBER(J4:A
B4)*COLUMN(J4:AB4),{1,2,3})-1)))

The formula needs to:

1: calculate the average of the last 3 numbers entered
starting from the 1st cell to LEFT of active cell
2: skip all text in cells

ex: J4= 20 J5=50 J6=xxx J7=xxx J8=xxx J9=30
J10=60(active cell)
answer should be: avg of 30, 50 , 20 = 33.333333

Thanks,

Tom

Tom,

Including the last cell was an oversight on my part. It is easily remedied by
changing the 'k' argument of the LARGE function from {1,2,3} to {2,3,4}.


=AVERAGE(TRANSPOSE(OFFSET(A4,0,LARGE(ISNUMBER(J4:AB4)*COLUMN(J4:AB4),{2,3,4})-1)))

However, in this message, you have changed your array from a row to a column.
In addition, you are now referencing the "active cell" rather than, as you
mentioned before, the rightmost cell. Active cell has a very specific meaning
in Excel, and it is NOT the meaning you used to describe your problem
initially.

If you want to reference the "active cell", you will need a VBA routine.
However, if you are merely being sloppy in the description of your problem,
perhaps my solution will work. If you are not being sloppy, please restate
your problem as clearly as possible.


--ron

 

[picktr]

Ron,

My apologies, can you tell I’m new?

Anyway, here it is in plain English.

In cell I4 - the formula
Range of cells to reference - L4:AD4 (total 19 cells)

What I want:

I will be entering a number or text every week for 19wks into that row

1. calculate the average of the last 3 numbers entered
starting from the 1st cell to LEFT of the cell I’m
currently working on.

2: skip all text in cells

ex: J4= 20 J5=50 J6=text J7=text J8=text J9=30
J10=60(cell that I’m currently working on)

answer should be: avg of 30, 50 , 20 = 33.333333

Thanks,

To

 

[Ron Rosenfeld]

Ron,

My apologies, can you tell I’m new?

Anyway, here it is in plain English.

In cell I4 - the formula
Range of cells to reference - L4:AD4 (total 19 cells)

What I want:

I will be entering a number or text every week for 19wks into that row

1. calculate the average of the last 3 numbers entered
starting from the 1st cell to LEFT of the cell I’m
currently working on.

2: skip all text in cells

ex: J4= 20 J5=50 J6=text J7=text J8=text J9=30
J10=60(cell that I’m currently working on)

answer should be: avg of 30, 50 , 20 = 33.333333

Thanks,

Tom


---

Tom,

In your description, you describe cells in a row (row 4).

In your example, you describe cells in a column (column J).

This is not consistent.

If you mean "row" then my formula will work, although you will need to change
it to reflect the range in which you will be entering data. (My formula
reflects the range you gave in one of your earlier postings, but I see you've
changed it above).



--ron

 

[Frank Kabel]

Hi
see my reply in your newer thread