Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am struggling with Microsoft Excell. I am using it to keep track of averages and totals.Example: ROW 1 7,5,9,4,10,A,D,0,2 Sum:37 AVG 3.9 How can I create a sum of all numbers divided by the total numbers in one row minus the cells where I put alphabets? Is there a way to skip these cells easily? Is there a formula to add up all cells with numbers minus the ones with letters?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

your =sum(A1:I1) in J1 works fine. After that use a =count(A1:I1) in K1 and then just use =J1/K1 in L1

the =count() function will only count numbers, not letters

edit

=avg(A1:I1) only averages numbers not letters. What's the problem?

share|improve this answer
    
Or using @Raystafarian notation, in one cell combine both functions as a single formula =count(A1:I1)/count(A1:I1). Also, the average seems to be 5.28, not 3.9. Are we missing something? –  jdh Feb 16 '12 at 3:46
    
It was way easier than I thought. Thanks for all the tips. As I split the totals and averages and all letter are ignored I was able to add =SUM(AM10:BA10) and =AVERAGE(AM10:BA10) I did look into count() as well, but as SUM did the trick as well I am all good. –  rhand Feb 16 '12 at 6:18

If my understanding is correct

Your input is as below,

A   B   C   D   E   F   G   H   I   J
1   7   5   9   4   10  A   D   0   2

then average of above numbers will be 4.75

=AVERAGE(A1:J1)

Here it calculates only numbers.

Below are the list of formulas / scenarios, which gives same result as "4.75"

=(A1+B1+C1+D1+E1+F1+I1+J1)/COUNT(B1,A1,C1,D1,E1,F1,I1,J1)
=SUM(A1:J1)/COUNT(B1,A1,C1,D1,E1,F1,I1,J1)
=SUM(A1:J1)/COUNT(A1:J1)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.