Need Help with formula.

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

picktr

=AVERAGE(Y3:INDEX(A3:Y3,LARGE(COLUMN(G3:Y3)*(ISNUM
BER(G3:Y3)),3)))

This formula works fine, however, I need to add to it.
When I put my numbers into the cells that this formula references,
everything works fine. It gives me the avg. of the last 3 cells
with entries.

ex: G3 (50) G4 (50) G5 (50)
result avg = 50

When I delete the contents of those 3 cells, leaving them empty o
numbers, the formula cell leaves the "50".

I'd like it to return to "0"

Thanks,
To
 
I think you got confused in your post.

You have g3=50, g4=50, g5=50. But your formula points at g3:y3.

I think something like this might do what you want:

=AVERAGE(Y3:INDEX(A3:Y3,LARGE(COLUMN(G3:Y3)*(ISNUMBER(G3:Y3)),
MIN(3,COUNT(G3:Y3)))))
(all one cell and still array entered)

If there are no numbers, then you'll get a div/0 error.

You can check for that, but I'm not sure what the check should be:

=if(count(g3:y3)<4,0,yourformulahere)
or
=if(count(g3:y3=1,0,yourformulahere)
(still array entered.)

Do you have to have at least 3 entries--or can you average just 1, 2, 3, or
more?
 
Dave,

The formula is correct, it looks in the range (g3:y3)
for the last three numbers entered, then averages those
numbers. The example, g3, g4, g5, had numbers entered.
When I enter a value into G6, it then drops G3 and averages g4, g5
g6.

I'll play with the formula you gave me.

p.s. forgive me, I'm knew but what do you mean by
"array entered" ?

Tom (e-mail address removed)
 
Array formulas are entered by hitting ctrl-shift-enter (instead of enter).
If you do it correctly, excel will wrap curly brackets {} around your formula.
(don't type them yourself.)
 
It was a week ago that you asked the same question, and received many
answers, *one* of which is the link below:


http://tinyurl.com/3f3kc
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------




Dave,

The formula is correct, it looks in the range (g3:y3)
for the last three numbers entered, then averages those
numbers. The example, g3, g4, g5, had numbers entered.
When I enter a value into G6, it then drops G3 and averages g4, g5,
g6.

I'll play with the formula you gave me.

p.s. forgive me, I'm knew but what do you mean by
"array entered" ?

Tom (e-mail address removed)
 
Back
Top