Apple "Numbers" -need help getting started

[macstatic]

Having downloaded the trial version of iWork I'm trying to see if "Numbers" will allow me to make graphical presentations of a survey, but I don't know how to get started.

The survey consists of around 50 different people who have answered around 10 multiple-choice questions. When done inputing the data I want to be able to make bar or pie charts telling me stuff like how many people are in a certain age group, who lives in a certain geographical area etc.

Is there an easy way to get this done in "Numbers"?

 

[dalvin200]

when you first open numbers it should ask you if you want the iwork user manual emailed to you...
maybe it would have the info in there?

 

[wordmunger]

Having downloaded the trial version of iWork I'm trying to see if "Numbers" will allow me to make graphical presentations of a survey, but I don't know how to get started.

The survey consists of around 50 different people who have answered around 10 multiple-choice questions. When done inputing the data I want to be able to make bar or pie charts telling me stuff like how many people are in a certain age group, who lives in a certain geographical area etc.

Is there an easy way to get this done in "Numbers"?

Sure, input your data, then create your charts. Numbers is designed for this. Numbers help gives basic pointers on how to create charts based on data. You might want to try it with just a few responses before entering your entire data set.

 

[Ramashalanka]

Enter your data. Select the data you want on the plot. The choose
Insert -> Chart -> Pie
from the menu. A chart appears automatically. Repeat for any other plots you want. Let us know if you have a more specific query.

 

[macstatic]

Thanks. Playing around with the templates helped me get a grasp of the basics (i've never used a spreadsheet before).

Now I'm wondering how I should apply my survey data correctly. I have multiple types of data for some of the participants. Here's an example of this with 3 people:

Name: John Doe
Country: England
Age: 15-20
Computer: Mac
OS: MacOS X

Name: Larry T.
Country: Canada
Age: 30-35
Computer: Mac, PC
OS: MacOS X, Windows

Name: Miranda K.
Country: USA
Age: over 50
Computer: PC
OS: Windows, Linux

As you can see, some people have multiple entries (e.g. they own two different computers or run several different operating systems).
In the end I want to be able to show things in a pie-chart or bar graph in numbers and percentage like:
- how many people come from the various countries
- how many people use a Mac vs. how many with a PC
- how many people have more than one computer
- how many operating systems are represented
- how many computers are fitted with more than one operating system

I guess what I really want to know in order to get started is which data fields do I need to set up in the spreadsheet, which ones go vertically and horizontally, and do I need to set up separate fields for say the computer types (i.e. one field for "Mac" and one for "PC"), or should I just have one "Computer" field where I enter "Mac" or "PC" for each person -and if that's the case, how do I enter two (or more) computers for the same person?

 

[Ramashalanka]

Thanks. Playing around with the templates helped me get a grasp of the basics (i've never used a spreadsheet before).

Now I'm wondering how I should apply my survey data correctly. I have multiple types of data for some of the participants. Here's an example of this with 3 people:

Name: John Doe
Country: England
Age: 15-20
Computer: Mac
OS: MacOS X

Name: Larry T.
Country: Canada
Age: 30-35
Computer: Mac, PC
OS: MacOS X, Windows

Name: Miranda K.
Country: USA
Age: over 50
Computer: PC
OS: Windows, Linux

As you can see, some people have multiple entries (e.g. they own two different computers or run several different operating systems).
In the end I want to be able to show things in a pie-chart or bar graph in numbers and percentage like:
- how many people come from the various countries
- how many people use a Mac vs. how many with a PC
- how many people have more than one computer
- how many operating systems are represented
- how many computers are fitted with more than one operating system

I guess what I really want to know in order to get started is which data fields do I need to set up in the spreadsheet, which ones go vertically and horizontally, and do I need to set up separate fields for say the computer types (i.e. one field for "Mac" and one for "PC"), or should I just have one "Computer" field where I enter "Mac" or "PC" for each person -and if that's the case, how do I enter two (or more) computers for the same person?

The short answer is that you can do it any way you want: a spreadsheet is so flexible that you can decide.

Usually you put what there is the highest number of in the rows. In your case that is probably the people. You then put all of the answers as columns.

You can set up separate fields for the computer types and countries, ie Mac, PC and I think for a first time user that would be the best. You'd have a 1 (or true) for yes and a 0 (or false) for no. Then you add other columns at the end which would summarize. For example if B is Mac and C is PC, then an additional column (say Q) might have the formula =B2 + C2 for row 2 which would give the number of computers owned. Another column (say R) might have =Q2>1 in row 2. Then, down the bottom (or at the top, up to you) you can sum the columns. The sum of column B would give the number of macs owned, the sum of column. The sum of column R would give the number of people with more than one computer.

(a more complicated option would be to have a single column for computer and have a code in powers of 2: 2^0=1 for a Mac 2^1=2 for a PC 2^2 for linux then a Mac and linux would be 2^0 + 2^2 = 5 and all three would be 2^0 + 2^1 + 2^2 = 7: I don't suggest this option for you)

I'd do the same for OS.

For country, I'd just put in the country as a word (say in column C) and then have a little table somewhere else with:

India in T2
and COUNTIF($C$2:$C$200,"="T2) in U2 which would give you the number of people from India.

Iceland in T3, COUNTIF($C$2:$C$200,"="T3) in U3 etc

(use the $ in the range addresses so you can copy U2 to U3 and it will be automatically updated)

For the age, I'd have a category for each age group (eg 1 is 0-20 or whatever). If people have given you the actual age, a formula can work out the group for you.

As I said, there are many ways of doing the same thing, but I think the above should be easiest for your sort of queries.

Let us know how you get on.

 

[macstatic]

Seems complicated

I'ved actually gathered all the data using OmniOutliner, and it would be great if I could just import that data into Numbers.
I don't know if that's possible, but my first obstacle is organizing the data correctly so that it can actually be read properly in order to do graphical representations.

What made things complicated were when the same person had several entries in the same group (e.g. in the "Computer" entry they would for instance answer: 2 Macs and 1 PC).
To easier explain what I mean, here's a screenshot of some entries made in "Omnioutliner":

Let's take "John D.":
He has 2 Macs (one with less than 1GB memory and another one with 4GB) and 1 PC (more than 4GB). He's got 2 CRT displays and one LCD.
As for operating systems he runs MacOS X, Linux, Windows and some obscure system I've categorized as "other". In addition to the computers' built-in hard drives he also has two external hard drives.
The data is similarly set up for other participants.

For the multiple data -some of it belongs together, and some doesn't.
For instance, with those two Macs: the first one has 1GB memory, the other one has 4GB and the PC has more than 4GB. In other words; a specific computer has a specific amount of memory.

On the other hand, data that doesn't necessarily belong together is the "OS", "Display", "Main use" and "Additional storage" categories. All that interests me is that he's using MacOS X, Windows, Linux and "other" on his computers. I don't care which OS is placed on which computer and which computer(s) have multiple OSes. The same goes for "Additional storage" where in this case I've just noted that the participant has two external hard drives (he might even use the same drive across several different computers). Equally for "Display" where I just want to know what kind of display(s) he has in his household. And of course for "Main use" I just want to know what his main use for his computers in general are (I don't care if he uses a Mac for surfing the net, the PC for making music while the second Mac isn't really used for much at the moment.

To enter (or import, if that's possible) this data into Numbers would I be using one row for each participant as you suggest, or several rows as I've used above?
I've really just used OmniOutliner as a notepad as it's more practical than e.g. "TextEdit" because it allows me to create pop-up choices (e.g. Mac, PC, Other) instead of typing in everything, and things align up nicely with the rows/columns. But I haven't taken into account if the data is computer-readable for making graphical representations (which is what I want), comparing the different entries.

Are there any free Numbers templates around which does similar things to what I'm attempting? That would help a lot.

 

[Ramashalanka]

Seems complicated

It's pretty simple really, the data just needs to be stored in a systematic way. Any complication is due to having to make the data to be clean.

I'ved actually gathered all the data using OmniOutliner, and it would be great if I could just import that data into Numbers.
I don't know if that's possible, ...

I haven't used OmniOutliner: it would've been much easier to put the data directly into Numbers. There shouldn't be a problem getting it into Numbers, but there'll be less control on the format. It's possible to change the format (eg rows per record etc) but can take some slightly more complicated formulae to do it.

I downloaded the OmniOutliner manual. What you need to do is choose:

File -> Export

and choose "Plain Text (with tabs)" from the file format. Make sure the filename has .txt at the end: Numbers seems prefers that for importing.

Then go into numbers and choose File -> Open and select the file. Your life will be much easier if there is one line per person, but from the picture you included, I don't like your chances.

What made things complicated were when the same person had several entries in the same group (e.g. in the "Computer" entry they would for instance answer: 2 Macs and 1 PC).

That type of data is to be expected; what made it complicated was putting it into OmniOutliner in the way you did. It would be easy if you had columns for Computer1Type, Computer1Memory, ... Computer20Type, ...

You could try to get it into that sort of form in OmniOutliner before you export. Or you'll either have to get it into that form in Numbers or write some less simple formulae to use it in that form (if you thought the previous post was complicated, you don't want to know).

I don't care if he uses a Mac for surfing the net, the PC for making music while the second Mac isn't really used for much at the moment.

Sounds like you don't need some of the data you've got.

Are there any free Numbers templates around which does similar things to what I'm attempting?

I don't know: anyone? What you're wanting to do is very easy once the data is in the correct format (one line per person): the formula COUNTIF is as complicated as it needs to get.

You could submit an extract of your data once you import it into Numbers. If it's multiple lines per person, you'll be able to easily get things like number from each country, in each age band, number of PCs, Macs etc (using COUNTIF ensuring blanks are ignored). However, number of people with more than one computer gets a little bit harder. An example is if column A is the name, you could have in a far right column, say Q, for row 2: =IF(D2="Mac",1,0)+IF(ISBLANK(A2),Q1,0) would give you the cumulative total of the number of macs for the person, then you'd do another column to pick off the last one and COUNTIF that column etc.

 

[macstatic]

Since a picture explains things a lot better than 1000 words, here's a new attempt -this time starting from scratch in Numbers:

Is this the proper way to do it? And once I've done that the rest (calculating percentages, showing
graphs etc. of the results) is easy (or at least doable with a little help)?

PS: is there a simple way to save/export an image like the above? I found no other way than to
take 3 different screenshots, then joining the images together afterwards.
Time consuming and cumbersome.
The "Print" function (which I also use to "Print to PDF" and even save images) would
only divide the output across several pages.

 

[Ramashalanka]

Since a picture explains things a lot better than 1000 words, here's a new attempt -this time starting from scratch in Numbers...
Is this the proper way to do it? And once I've done that the rest (calculating percentages, showing
graphs etc. of the results) is easy (or at least doable with a little help)?

Yes, that seems good, as long as (from your previous post) you don't need to know which OS has which amount of memory etc.

However, any unnecessary text may make your life harder (eg the GB for the memory, could just make it 2; for less than 1GB could just make it 0): dealing with text often involves converting it to numbers (eg if you later want to say how many with < 3GB or whatever).

The most important thing for you would be to calculate the %s etc before you put in all of the data. If you put in all of the data and find you need an extra column or different format you'll have to go back and change stuff. The three entries should be enough to check it's working. Have a look at my previous post re COUNTIF to get you started. When you've got it working you can put in the rest of the data. It's easy to make mistakes in spreadsheets (because they're so flexible), so check everything for reasonableness, eg adding up the number of people from different countries equals the total number of people, adding up the computers of different types equals the total number of computers etc.

PS: is there a simple way to save/export an image like the above? I found no other way than to
take 3 different screenshots, then joining the images together afterwards.
Time consuming and cumbersome.
The "Print" function (which I also use to "Print to PDF" and even save images) would
only divide the output across several pages.

What you did seems fine; it's not common to need a 3 screen width image. Anyone with a better idea?


How did you get on with the export from OmniOutliner?

 

[macstatic]

However, any unnecessary text may make your life harder (eg the GB for the memory, could just make it 2; for less than 1GB could just make it 0): dealing with text often involves converting it to numbers (eg if you later want to say how many with < 3GB or whatever).

I've tried to make sense of the user-manual and understand that (well, obviously really when I think about it) a spreadsheet deals with numbers.

The problem with changing everything into numbers is that it'll be very difficult for me to decipher just by looking at the table.
At least with my original OmniOutliner document I can very quickly see how many computers a certain participant owns, the type and how much memory each one has. Everything is written in plain English and could very well be used as it is. The only problem is that OmniOutliner doesn't do graphs and charts, which is how I want to present my findings. That's why I'm looking into Numbers.

I was hoping that I could simply enter all the relevant data into the spreadsheet, just remembering to be consistent all the way (i.e. naming a Mac "Mac" and not "Apple Mac", "Macintosh" or "Apple Macintosh computer") and the spreadsheet would simply count how many times it finds the words "Mac".
That way I assume I could easily have the spreadsheet tell me how many people owned each type of computer as well as the percentage.
Could it be done this way, or do I have to translate all my text values into numeric values?

The most important thing for you would be to calculate the %s etc before you put in all of the data. If you put in all of the data and find you need an extra column or different format you'll have to go back and change stuff.

Yes, I'll test things out with those 3 participants before spending time entering the data for all of them.

How did you get on with the export from OmniOutliner?

Haven't gotten that far as it seems the data structure won't be spreadsheet compatible. I'll have to rearrange everything in OmniOutliner first or re-enter things in Numbers. I think I prefer the latter as there aren't too many entries anyway.

 

[Ramashalanka]

I've tried to make sense of the user-manual and understand that (well, obviously really when I think about it) a spreadsheet deals with numbers.

Could it be done this way, or do I have to translate all my text values into numeric values?

Yes you can do it that way. You certainly don't have to convert your text into numbers. I was just saying don't put in unnecessary text (such as GB). It is normal in published tables to have the units at the top of the column anyway. The problem with text is if you want to calculate something like the number with less than 4 GB then you normally convert to numbers first. You can easily deal with text as I explained in my previous post:

For country, I'd just put in the country as a word (say in column C) and then have a little table somewhere else with:

India in T2
and COUNTIF($C$2:$C$200,"="T2) in U2 which would give you the number of people from India.

Iceland in T3, COUNTIF($C$2:$C$200,"="T3) in U3 etc

(use the $ in the range addresses so you can copy U2 to U3 and it will be automatically updated)

I'd expect you'd do the same as the above with operating system etc etc.

Haven't gotten that far as it seems the data structure won't be spreadsheet compatible. I'll have to rearrange everything in OmniOutliner first or re-enter things in Numbers. I think I prefer the latter as there aren't too many entries anyway.

It does seem it won't be compatible, but it would be very easy to see what it looks like after export/open (see my above post).

 

[corsair2112]

I'm new to Numbers as well. You would think this would be easy to do, maybe I'm dense, but how do I add just one cell to a column and shift the existing cells down? I see where I can add a new row or a new column, but I want just one cell in one column. Help!

 

[Ramashalanka]

I'm new to Numbers as well. You would think this would be easy to do, maybe I'm dense, but how do I add just one cell to a column and shift the existing cells down? I see where I can add a new row or a new column, but I want just one cell in one column. Help!

I don't think there is a Numbers equivalent to the Excel "Insert Cells ..." or "Insert Copied Cells ..." with the "Shift cells down" etc options, but I may be wrong. Perhaps it is missing because it can have unintended consequences, changing the alignment with respect to other columns.

To achieve the same thing, you can select the cells you want to move, move the cursor until it changes to a hand and drag the cells down which creates a space.

 

[macstatic]

This is probably a silly question, but where do I enter the formula for my spreadsheet calculations (which menu and/or icons)?

 

[Ramashalanka]

This is probably a silly question, but where do I enter the formula for my spreadsheet calculations (which menu and/or icons)?

You just click on the cell and start typing. Start with =, for example in B2 you could put =B1+1. If you want help you can click the little fx button for some formula choices.

 

[Ashka]

Select a cell > Check out the Inspector > Cells (42) > Checkbox for Yes / No answers & the Pop up Menu for Multi choice Mac, Windows or other.
Select a column of numbers and check out the automatic formulas available under styles on the side bar. Want the sum one? Just drag it to the cell you want the total in.

http://www.apple.com/iwork/numbers/

 

[macstatic]

I don't get it.
I click in the cell where the title is for that row (e.g. "Computer A"), then follow your instructions. I've managed to create a popup menu with multiple choices (e.g. "Mac", "PC" and "Other"). It's similar to what I can do in OmniOutliner.
But it only applies for that one cell!!

So next I tried selecting all the available cells in a column. This time I was able to select between a computer type for each questionnaire-participant, but for the time being I've only entered 3 people (for testing, to see if it all works).
I was hoping to put the popup in the top column or something, and then it would automatically apply for that whole column, but obviously not.
What am I doing wrong?

 

[Ramashalanka]

I don't get it.
I click in the cell where the title is for that row (e.g. "Computer A"), then follow your instructions. I've managed to create a popup menu with multiple choices (e.g. "Mac", "PC" and "Other"). It's similar to what I can do in OmniOutliner.
But it only applies for that one cell!!

So next I tried selecting all the available cells in a column. This time I was able to select between a computer type for each questionnaire-participant, but for the time being I've only entered 3 people (for testing, to see if it all works).
I was hoping to put the popup in the top column or something, and then it would automatically apply for that whole column, but obviously not.
What am I doing wrong?

Copy the cell that you've made with the popup menu, then paste it into as many cells as you want.

 

[ertdredge]

I don't think there is a Numbers equivalent to the Excel "Insert Cells ..." or "Insert Copied Cells ..." with the "Shift cells down" etc options, but I may be wrong. Perhaps it is missing because it can have unintended consequences, changing the alignment with respect to other columns.

To achieve the same thing, you can select the cells you want to move, move the cursor until it changes to a hand and drag the cells down which creates a space.

Sadly, this isn't identical. Text, formulas, backgrounds, etc. drag this way, but borders do not. When you shift an entire row/column everything moves.

 

[Ramashalanka]

Sadly, this isn't identical. Text, formulas, backgrounds, etc. drag this way, but borders do not. When you shift an entire row/column everything moves.

True, that is a difference between Numbers and Excel: borders move in Excel but not in Numbers.