CHUD.com Community › Forums › THE CHEWERS › The Chewers Catch-All › MS EXCEL Help
New Posts  All Forums:Forum Nav:

MS EXCEL Help

post #1 of 13
12/7/10 at 5:04pm
Thread Starter 
Anyone know their way around Excel?

I currently have the following formula in a sheet I am using:

=SUM((O2+P2+Q2+R2+S2+T2+U2+V2+W2+X2+Y2+Z2+AA2+AB2)/14)

I need that formula changed so that it gives me an average of cells O through AB yet ignores empty cells where some people will have only 10 or 12 entries instead of the whole 14.

Am I looking for =SUM(O:AB)?

Thanks in advance for any assistance.
Reply
post #2 of 13
12/7/10 at 5:07pm
You should just be able to use =AVERAGE(O:AB). That'll only take into account cells with data in them in the range. Using =SUM(O:AB) will force it to count blank cells as zero and throw your average off.
Reply
post #3 of 13
12/7/10 at 5:12pm
Try using "eight equals D" a few times in your spreadsheet. "Pen Fifteen" is also a lifesaver.
Reply
post #4 of 13
12/7/10 at 5:16pm
Thread Starter 
Thank you, Dickson.

I plugged it in and it gave me #DIV/0! in the cell. Will this just be there until data is entered or can I make that go away?

Also, can I paste =AVERAGE(O:AB) into all the rows I need it in or do I need to specify =AVERAGE(O2:AB2) as in my formula above and then paste it? I know that it automatically changes the number.
Reply
post #5 of 13
12/7/10 at 5:18pm
Thread Starter 
Quote:
Originally Posted by Alex Augustine View Post
Try using "eight equals D" a few times in your spreadsheet. "Pen Fifteen" is also a lifesaver.
Reply
post #6 of 13
12/7/10 at 5:30pm
Quote:
Originally Posted by TzuDohNihm View Post
Thank you, Dickson.

I plugged it in and it gave me #DIV/0! in the cell. Will this just be there until data is entered or can I make that go away?

Also, can I paste =AVERAGE(O:AB) into all the rows I need it in or do I need to specify =AVERAGE(O2:AB2) as in my formula above and then paste it? I know that it automatically changes the number.
When you start plugging in numbers, the solution will replace it.

If you copy the formula in the first row, and paste it in say, the next fives rows, it'll change them all.
Reply
post #7 of 13
12/7/10 at 5:34pm
Quote:
Originally Posted by TzuDohNihm View Post
Thank you, Dickson.

I plugged it in and it gave me #DIV/0! in the cell. Will this just be there until data is entered or can I make that go away?

Also, can I paste =AVERAGE(O:AB) into all the rows I need it in or do I need to specify =AVERAGE(O2:AB2) as in my formula above and then paste it? I know that it automatically changes the number.
Yeah, until you have data, it'll give you that #DIV/0! error. You could use =IF(SUM(O1:AB1)=0,"",AVERAGE(O1:AB1)). That'll leave the cell blank if the total value of the range is zero (i.e, nothing's been entered yet), but show the average once data is entered in any cell in the range. That's mostly aesthetic; if you don't mind the error showing, you don't have to go that far.

And yes, if you copy and paste the formula into the next row down, it should automatically change the numbers (from A1 to A2, for example).
Reply
post #8 of 13
12/7/10 at 5:56pm
Thread Starter 
Rock on, guys. Thank you very much.
Reply
post #9 of 13
12/7/10 at 7:21pm
Thread Starter 
Any way to make the average stop repeating decimals?

Instead of 75.66666667; stop at 75.6?
Reply
post #10 of 13
12/7/10 at 7:22pm
Format Cell/Number/Decimal Places
Reply
post #11 of 13
12/7/10 at 7:27pm
Thread Starter 
Quote:
Originally Posted by Matt Hindmarch View Post
Format Cell/Number/Decimal Places
Much obliged!
Reply
post #12 of 13
12/7/10 at 8:09pm
Thread Starter 
NVM, found the button I was looking for.
Reply
post #13 of 13
12/14/10 at 8:02pm
just wanted to throw in: =SUBTOTAL(9,range)

these save my fucking life (9 for sum, 1 for average) then use the filters under data to only include the values you want.

a few other of my favourites:

=CountA(range) will only count cells with something in them (ie not 0)

and =countif("blah",range) will only count cells that have the word "blah" (or whatever you put) in them.

Or you could get into pivot tables, but that should probably be a thread in the Sex section.
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: The Chewers Catch-All
CHUD.com Community › Forums › THE CHEWERS › The Chewers Catch-All › MS EXCEL Help