• Welcome to Overclockers Forums! Join us to reply in threads, receive reduced ads, and to customize your site experience!

Excel/Google spreadsheet help please?

Overclockers is supported by our readers. When you click a link to make a purchase, we may earn a commission. Learn More.

ratbuddy

Member
Joined
Aug 24, 2007
I'm making some charts that plot performance in games, and using rows to list what game, and column to list the FPS scores. At the bottom of each column, I've got a cell that says "=AVERAGE(C2:C23)" for example. Also, since I'm listing data for 2 different cards, I'm using "=(B2-C2)/C2" in cell D2 to show the % difference between the scores.


I've got all the basic info (list of games for example) in the first sheet, and each individual review site on its own sheet. I'm just copy/pasting the whole template from the first sheet into each new sheet and filling in the blanks. I'm having two problems, both pretty much the same. Both of those formulas I listed above work fine when there is data, but when there is not, they display a divide by zero error.

Is there a simple/stupid way to make the =AVERAGE function only average up the non-zero entries, and is there a way to make the B2-C2 thing also ignore =zero stuff?

Pic attached if that helps, and thanks :beer:

BTW, are there any 3D spreadsheets? I could have done this all on one sheet if so..
 

Attachments

  • 5970help.jpg
    5970help.jpg
    95.7 KB · Views: 695
Use "=IF(C2=0, "N/A", (B2-C2)/C2)" in cells D2 to D23 (and similar columns), and the average function will ignore cells with text.
 
Last edited:
Yep, it ignores text, logical values, and empty cells.
 
Last edited:
Great, thanks. I assume it also works the same for the
Code:
=AVERAGE(D2:D23)
thingy? Also, what's the "N/A" mean? Empty cells?

You don't need to change your AVERAGE on row 30. It should ignore the N/A in the percentage cells.

Just change your "percentage" columns such as D to "=IF(C2=0, "N/A", (B2-C2)/C2)" replacing with correct cells of course. The N/A is the text that will be printed in column D when there isn't any value in column C, you can change N/A to whatever you like.
 
Beautiful, perfect, thanks a million :beer:
 

Attachments

  • 5970thanks.jpg
    5970thanks.jpg
    97.2 KB · Views: 645
No prob. Glad I could help. If anything else comes up, send questions my way.
 
I'd like to do some averaging across sheets. I don't want to include the first sheet because it's just a blank template, so I'm going to use (for example) =AVERAGE(Techspot:Anandtech!D30) or whatever the functional way of doing that is. Can't get it to parse. It either gives a name error saying no such sheet, or when I add extra ' or "'s to try and get it working, fails completely :p

I'm also going to need to nest the across-sheet averaging with the =IF function, I assume it'll work the same as before?
 
I tested a few simple averages across sheets "=average(sheet1:sheet3!b2)" and one example I had in the b2 cells 1, 2, 3. The average worked giving me 2. You dont need to add ' or " to the equation, just type in the names of the start and end sheets. The function you're using should avaerage the D30 cells on all sheets Techspot through Anandtech. Ill try something more in-line with what you're doing after lunch, since there are formulas in the D30 cells instead of values, that might have something to do with it (I dunno why it would, but it's possible).

Without the actual spreadsheet to see all the data/formulas, it's hard to pinpoint the problem.

Nesting this average with the previous IF function should work, allowing the average to skip blank cells across sheets.
 
I tested a few simple averages across sheets "=average(sheet1:sheet3!b2)" and one example I had in the b2 cells 1, 2, 3. The average worked giving me 2. You dont need to add ' or " to the equation, just type in the names of the start and end sheets. The function you're using should avaerage the D30 cells on all sheets Techspot through Anandtech. Ill try something more in-line with what you're doing after lunch, since there are formulas in the D30 cells instead of values, that might have something to do with it (I dunno why it would, but it's possible).

Without the actual spreadsheet to see all the data/formulas, it's hard to pinpoint the problem.

Nesting this average with the previous IF function should work, allowing the average to skip blank cells across sheets.

It's a Google doc, I can share access if that'd help.
 
Excel doesn't have problems reading in formulas across sheets. I got averages on sheets 1-3, then on sheet4 I got the average of those averages and it still worked.

Sharing could possibly help, but I've never used Google's spreadsheet. So, I don't know how to even get to them, you might have to walk me through it.

Note: We're in the process of getting satellite internet at my house (no more 56Kb/s), so I'll only be able to help from work.
 
Well, that's blocked here at work...government :rolleyes:

If you wanted, you could send me a copy through email with a note about exactly what you want. Then I could see if I can find the problem.

If not, then you need to supply as much info on each cell you're working with as possible.
 
Well, that's blocked here at work...government :rolleyes:

If you wanted, you could send me a copy through email with a note about exactly what you want. Then I could see if I can find the problem.

If not, then you need to supply as much info on each cell you're working with as possible.

Every sheet has (in cell D30, formatted as a %)
Code:
=AVERAGE(D2:D23)

I'm simply trying to add a sheet at the end that does
Code:
=AVERAGE(Sheet2:Sheet8!D30)

to show the average of all the results. The cell where I place that formula is saying "#NAME?" and "Error: Unknown range name Techspot" when I mouseover it. The first sheet is called Techspot and the last is called Anandtech so what I entered is
Code:
=AVERAGE(Techspot:Anandtech!D30)

Maybe I need a numeral in the worksheet name somewhere? Ready to give up and just do it all manually :-/

Thanks for the help so far though, wouldn't have gotten any of this done without it.

edit: Great, this thread is the first hit when Googling 'google spreadsheet average across worksheets' :rolleyes:
 
You don't need numerals in sheet names, I've tried that as well. I've tried everthing you've said in Excel and it seems to work fine. There has to be a typo or something somewhere or excel and google spreadsheets are different when it comes to what you're doing.

Did you accidently put " Techspot" or "Techspot " in the sheet name or something?

I'm trying to duplicate your error, I just can't seem to do it...
 
Last edited:
You don't need numerals in sheet names, I've tried that as well.

There is something wrong with Techspot. Did you accidently put " Techspot" or "Techspot " in the sheet name or something?

I'm trying to duplicate your error, I just can't seem to do it...

I doublechecked, it's definitely called Techspot. I renamed it Sheet1 even and it's still giving the same error. I even tried creating a range name for them and nothing. I looks like Google Spreadsheets just doesn't support ranging worksheets inside a formula. Gonna export as Openoffice format and see if it works there.

edit: Well, figured it out. Had to be entered as =AVERAGE(Techspot!D30,BitTech!D30,TomsHardware!D30,PCPerspective!D30,HardwareCanucks!D30) rather than letting me use : to range it. Sigh.

Thanks for trying, seems like Google/Openoffice have a ways to go to catch up to Mickeysoft.
 
Last edited:
Hopefully that will work.

A work-around would be to make a column on your new sheet that references each D30 cell that you want and put an average at the bottom of that column, or reference each cell individually.

EDIT: Just saw your edit, glad you figured out what it was. Will you be posting screens of those sheets when you're done?
 
Hopefully that will work.

A work-around would be to make a column on your new sheet that references each D30 cell that you want and put an average at the bottom of that column, or reference each cell individually.

EDIT: Just saw your edit, glad you figured out what it was. Will you be posting screens of those sheets when you're done?

Oh yes, and I'll also be fiddling with the chart features :p
 
Back