Peo

P

picktr

Peo,

Last week you gave me the following formula:

=AVERAGE(AD3:INDEX(A3:AD3,LARGE(COLUMN(L3:AD3)*(ISNUMBER(L3:AD3)),3)))

It's working great! It's skipping text, and only picking
up the last 3 numbers entered.

I'm also needing it for another cell, however with one
adjustment.

I need it to start the avg calc from the 1st cell to the
left of the active cell. I don't want it to include the
number that I'm entering.

Thanks,

Tom [email protected]
 
F

Frank Kabel

Hi
and where do you want to put this result or should this
result automatically change if you select a different cell?
 
P

picktr

Frank,

P.S. That formula already accounts for that,
I just need it to start the calculation from
the cell to the left of the cell I'm entering
a number into. It should not inlude that number
 
F

Frank Kabel

Hi
what formula are you using?
A standard formula won't ccount for the current active cell. This would
require VBA (and probably the use of the worksheet_selectionchange
event)
 
P

picktr

Frank,

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 currentl
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
 
F

Frank Kabel

Hi Tom
first: Difficult to use the cell you're currently working on. But I
think a formula will achieve the same just getting the last 3 numeric
values into your average calculation):
Second: in your first paragraph you talk about a row (row 4) but in
your second paragraph you use a column (column J) ->a little bit
confusing.

The following array formula (entered with CTRL+SHIFT+ENTER) in I4 will
calculate the average of the last 3 entered values in L4:AD4 (skipping
text entries)
=AVERAGE(OFFSET(AD4,0,0,1,-(COLUMN(AD4)-LARGE(IF(ISNUMBER(L4:AD4),COLUM
N(L4:AD4)),3)+1)))

This does NOT check there your current cell position is (and I would
strongly recommend not to create formulas which will use the current
cell position as parameter)
 
P

picktr

Frank,

How about this formula?

residing in cell I4
=AVERAGE(OFFSET($L$4,,COUNTA($L$4:$AD$4)-2,,-3))

This works great as long as I dont put in any text or
leave a cell blank.

But I will be putting in text.

ex:

J4= 20 K4=50 L4=text M4=text N4=text O4=30 P4=60

answer should be: avg of 30, 50 , 20 = 33.333333
P4 is not counted
Thanks,

To
 
P

picktr

Frank,

Please disregard all other posts and use this post.

How about this formula?

residing in cell I4
=AVERAGE(OFFSET($L$4,,COUNTA($L$4:$AD$4)-2,,-3))

This works great as long as I dont put in any text or
leave a cell blank.

But I will be putting in text.

ex:

L4= 20 M4=50 N4=text O4=text P4=text Q4=30 R4=60

answer should be: avg of 30, 50 , 20 = 33.333333
R4 is not counted
Thanks,

Tom
 
F

Frank Kabel

Hi
try the array formula
=AVERAGE(OFFSET(AD4,0,0,1,-(COLUMN(AD4)-LARGE(IF(ISNUMBER(L4:AD4),COLUM
N(L4:AD4)),3)+1)))
 
P

picktr

Frank,

It does not work.

I entered

48, 60, x, x, 60, x, x, 60

it gave an answer of 60

should be 53 ( 60, 60, 48 )

skipping the text "x", and the last number entered, 60
 
F

Frank Kabel

Hi
to skip the last numeric value use the following array formula
=AVERAGE(OFFSET(L4,0,LARGE(IF(ISNUMBER(L4:AD4),COLUMN(L4:AD4)),4)-COLUM
N(L4),1,LARGE(IF(ISNUMBER(L4:AD4),COLUMN(L4:AD4)),2)-LARGE(IF(ISNUMBER(
L4:AD4),COLUMN(L4:AD4)),4)+1))
 
Top