Excel average problem
Excel average problem
(OP)
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(B3:P3)
this is my desired result:47
Any help is greatly appreciated
Thank you for your time!!
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(B3:P3)
this is my desired result:47
Any help is greatly appreciated
Thank you for your time!!





RE: Excel average problem
TTFN
RE: Excel average problem
May have found a long winded solution
in P4 enter this formula
=IF(P3<>"",1,"")
in O4 enter this formula
=IF(AND(SUM($P$4:P4)<10,O3<>""),1,"")
and copy this to A3
in a cell enter this formula
=SUMIF(A4:P4,"=1",A3:P3)/COUNTIF(A4:P4,"=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!
RE: Excel average problem
Thanks for the input. Ill try it out and Ill let you know
Thanks again!!!!!