Excel average problem 1

[glove419]

When averaging a row of numbers. I am trying to average the last 10 entries. The problem is the row may have blank cells.
Example:
I am using row 3 and columns A-P.
Column entries are as follows;
A=10
B=20
C=(BLANK)
D=40
E=(BLANK
F=(BLANK)
G=70
H=(BLANK)
I=90
J=100
K=10
L=20
M=30
N=40
0=(BLANK)
P=50

this is the formula I am using
:=AVERAGE(OFFSET(INDEX(3:3,,COUNT(A3:T3)),0,-9,1,10))
the result is:55

I Check the answer with =AVERAGE(B33)
this is my desired result:47

Any help is greatly appreciated

Thank you for your time!!

 

[IRstuff]

why not just use sum(b33)/count(b33)?

TTFN

 

[onlyadrafter]

Hello,

May have found a long winded solution

in P4 enter this formula

=IF(P3<>"",1,"")

in O4 enter this formula

=IF(AND(SUM($P$44)<10,O3<>""),1,"")

and copy this to A3

in a cell enter this formula

=SUMIF(A44,"=1",A33)/COUNTIF(A44,"=1")

the answer given is 47.

Long winded I know, but it does work, is this a feasable option?

Alternatively if the number of values can change i.e. go beyind column P

enter this formula in A3

=IF(AND(SUM(Q4:$IV$4)<10,P3<>""),1,"")

and copy as far as required.




----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[glove419]

onlyadrafter

Thanks for the input. Ill try it out and Ill let you know
Thanks again!!!!!