Recommend
4 
 Thumb up
 Hide
14 Posts

BoardGameGeek» Forums » Everything Else » Chit Chat

Subject: Excel help rss

Your Tags: Add tags
Popular Tags: [View All]
Melissa
Australia
Parkville
Vic
flag msg tools
admin
designer
May you find the Perfect Shoe! xxx
badge
Mostly offline, but trying.
Avatar
mbmbmbmbmb
Crowdsourcing BGG help again.

I am doing some stats on a survey we have just done for work.

I currently have a Very Big Excel Workbook which contains a page for each question that I have analysed.

On each of these pages:

Cell A1 contains the question
Cells B13 to F13 contain average values for responses from each of 5 groups
Cell B16 contains an overall average value


What I would like to do, is create a new sheet at the end that summarises all of this into a table with the following headings:

QUESTION - Gp 1 mean - Gp 2 mean - Gp 3 mean - Gp 4 mean - Gp 5 mean - overall mean

(OK maybe the headings will be a bit more descriptive but I hope you can see what I mean).

My primary support person thinks that I need to set up a formula for each cell, manually.

I think that would be horrible and OMG I WOULD RATHER RETYPE THE WHOLE LOT and there is (or should be!) a way that I can get Excel to automatically collect that information and put it in the appropriate rows.

My primary support person thinks I am being lazy. He may be right, but I maintain that this functionality SHOULD exist.

I have looked at the various stats and lookup functions and nothing seems to quite cover this. Is there an Excel whiz here somewhere who can advise me?
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Blorb Plorbst
United States
Bloomington
Indiana
flag msg tools
badge
I think we're all bozos on this bus.
Avatar
mbmbmb
It sounds like the issue is that you have a brazilian sheets in this book, true?

How many books are we talking about?

Are they consistently named? (test1,test2, etc.)

Do you have any experience with macros?
3 
 Thumb up
1.00
 tip
 Hide
  • [+] Dice rolls
Melissa
Australia
Parkville
Vic
flag msg tools
admin
designer
May you find the Perfect Shoe! xxx
badge
Mostly offline, but trying.
Avatar
mbmbmbmbmb
well yeah, but I have the bajillion sheets because each one has raw data from one question. Well, fairly raw, anyway.

They are not consistently named and I am not a goddess of macros.

I am wondering whether the Data consolidation function might be able to do what I am looking for
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Society of Watchers
United States
Killbuck
Ohio
flag msg tools
badge
Avatar
mbmbmbmbmb
Ugh. Can be done. Using a macro might be good and if you've named each sheet consistently would really be good because you can then easily create a loop Sheet1, Sheet2, Sheet3, etc.
3 
 Thumb up
1.00
 tip
 Hide
  • [+] Dice rolls
Melissa
Australia
Parkville
Vic
flag msg tools
admin
designer
May you find the Perfect Shoe! xxx
badge
Mostly offline, but trying.
Avatar
mbmbmbmbmb
Hmm. Renaming sheets would be pretty quick.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Melissa
Australia
Parkville
Vic
flag msg tools
admin
designer
May you find the Perfect Shoe! xxx
badge
Mostly offline, but trying.
Avatar
mbmbmbmbmb
Does this help? It is an image of what the pages look like. I marked the cells that I want to summarise.

1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Blorb Plorbst
United States
Bloomington
Indiana
flag msg tools
badge
I think we're all bozos on this bus.
Avatar
mbmbmb
Consistently named spreadsheets would only let you use an editor to build a formula in a text editor or somesuch.

Do you just want to average all the B16s in each sheet?

I'm at home on my Linux system and don't have Excel available but the answer to your problem is a custom function in Excel VBA.

The pseudo code will be something like this:

Quote:
public function AVERAGE_SHEETS() as float
dim A as worksheet


For each A in Sheets do
result = result+ A.range("B16").value

next A

result = result/Sheets.count-1

end function


Then in your summary sheet just use the function: "=AVERAGE_SHEETS()"

I can test this tomorrow morning (9am GMT-5) but I bet some other clever person can flesh it out from here.

Edit: this will include the value in cell B16 of your summary sheet as well so make sure that's blank. If you need to average all the individual values in each sheet (B13-F13) then they'll need to be added up individually and then the denominator needs to be (sheets.count-1)*5

2 
 Thumb up
1.00
 tip
 Hide
  • [+] Dice rolls
Larry Levy
United States
Manassas
Virginia
flag msg tools
designer
badge
Best hobby, with the best people in the world. Gaming is the best!
Avatar
mbmbmbmbmb
Maybe I'm not understanding this, Melissa, but couldn't you set up just one cell and then do a series of Find and Replace actions to make the formula work for the other cells? I think you can do the Replaces directly inside of a formula, but if not, you could paste the formula to a Word document, make the Replacements there, and then paste them back into the spreadsheet. Would that work or is this more complicated then I'm making it out to be?
1 
 Thumb up
1.00
 tip
 Hide
  • [+] Dice rolls
Melissa
Australia
Parkville
Vic
flag msg tools
admin
designer
May you find the Perfect Shoe! xxx
badge
Mostly offline, but trying.
Avatar
mbmbmbmbmb
I think if I paste the formulae it will paste them as text not as formulae
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Melissa
Australia
Parkville
Vic
flag msg tools
admin
designer
May you find the Perfect Shoe! xxx
badge
Mostly offline, but trying.
Avatar
mbmbmbmbmb
Thanks for your help, everyone. I have found the "show formulas" [sic] link and that at least makes updating all the references a little bit faster.

xxx
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Larry Levy
United States
Manassas
Virginia
flag msg tools
designer
badge
Best hobby, with the best people in the world. Gaming is the best!
Avatar
mbmbmbmbmb
melissa wrote:
I think if I paste the formulae it will paste them as text not as formulae

Nope, I've tried it and it works just fine. (At least it does in Excel 2002; don't know about its hated younger brothers.) But if you've found something that works just as well (and certainly a macro would be more elegant), that's good.
1 
 Thumb up
1.00
 tip
 Hide
  • [+] Dice rolls
Sean Todd
United States
Bloomington
Minnesota
flag msg tools
badge
Avatar
mbmbmbmbmb
One of the things I love about Excel*, is that there are a dozen ways of doing almost anything. You can have four experts in a room and each may only know three of non-overlapping ways to do it and all might be spreadsheet wizards.

Anyway, another way to do your task is to use an Indirect formula. On your final summary sheet you put these values in cells A1 to A14:

Sheet1
Sheet2
...
Sheet14

(If you put "Sheet1" into cell A1 and drag the corner down it will actually increment the numbers up for you, so you only have to type "Sheet" once.)

Second, in put these values in cells B1 to B14:

A1
A1
...
A1

In column C goes the formula: =Indirect(A1&"!"&B1). This produces the same result as =Sheet1!A1 Copy this formula down to fill in cells C1 to C14 and your results should appear.


One other thing, the people I know who are best with Excel got that way by being exceedingly lazy. If you ever have to do something more than four times in Excel, you should probably think about automating it. Anyone who wants to be an Excel master should hold on to their lazy streak.


*Yes, I just said I love something about Excel, what of it?
4 
 Thumb up
1.00
 tip
 Hide
  • [+] Dice rolls
Larry Levy
United States
Manassas
Virginia
flag msg tools
designer
badge
Best hobby, with the best people in the world. Gaming is the best!
Avatar
mbmbmbmbmb
Wow, I've never heard of the Indirect function and it sounds extremely useful! I also see it allows you to always reference the same cell in a formula, even if rows are inserted or deleted. Very cool! Thanks!!!
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andrew Simpson
United Kingdom
Leeds
West Yorkshire
flag msg tools
badge
Avatar
mbmbmbmbmb
Here's how I would do it:

Add a button and create a macro attached to it.

Sub Create_Summary()

Dim sm As Object
Dim sh As Worksheet
Dim r As Long
Dim c As Long

Set sm = Worksheets("Summary")
r = 2 ' start on the second row below the titles

For Each sh In ActiveWorkbook.Worksheets
Select Case sh.Name
Case sm.Name
' if page is the summary sheet, do nothing
Case Else
' copy the question into first cell in row r
sm.Cells(r, 1) = sh.Cells(1, 1).Value
' loop through the 5 columns of groups and copy their averages
For c = 2 To 6
sm.Cells(r, c) = sh.Cells(13, c).Value
Next c
' copy the total average
sm.Cells(r, 7) = sh.Cells(16, 2)
End Select
' move to the next row
r = r + 1
Next sh

End Sub


It basically loops through every sheet in the workbook, copying the answers to the summary sheet, excluding the summary sheet itself. If there are other pages that need ignoring, you can just alter the Case sm.name line and add them eg. Case sm.name, "Notes" would ignore the "Notes" worksheet as well.

Here's a quick spreadsheet as an example:

http://www.fredlawton.com/melissa.xls
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.