×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# 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

Replies continue below

### RE: Excel average problem

why not just use sum(b3:p3)/count(b3:p3)?

TTFN

### RE: Excel average problem

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$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")

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

(OP)

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!