Talk

Advanced search

Excel question - grouping data and pivot tables...

(9 Posts)
madcows Thu 08-Oct-09 12:33:44

I wonder if any of you experts can help. I'm entirely self taught excel user, and so you'll need to speak slowly!

I have some data showing land ownership (in acres) and social group, by household. I want to 'group' the land data - so that instead of showing the actual amount that each household has I put them into groups - ie 0, 0.1 to 2 acres, 2.1 to 5 acres etc). Do I do this in a pivot table (and if so, how?!), or do I have to create a new column in my data sheet, and find a way of putting the acreage data into groups?

Does this make any sense at all?

Many thanks for anyone who can help!
madcows

madcows Thu 08-Oct-09 13:03:53

Okay, so I think what I'm trying to do is work with the FREQUENCY function... Does that sound right?

MrAnchovy Thu 08-Oct-09 15:08:01

I don't think that function will do what you want.

If this is a one-off excercise the easiest way to do it would be to sort the rows by acres of land and put the bands in yourself by copying down.

If you will be repeating this with changing sets of data so you want a formula to calculate the band for each row, you need a nested if statement something like this

=if($a3<0.1,1,if($a3<2,2,if($a3<5,3,4)))

... which will give results of 1-4 depending on the value of cell a3.

MrAnchovy Thu 08-Oct-09 15:09:56

Oh, in case I have got the wrong end of the stick, this page gives a good example of what the frequency function does and how to use it.

madcows Thu 08-Oct-09 15:16:06

Thanks for your help! I'd found my way to the same page, and it seems that while this is one way of doing it... there is another way.
I've ended up 'grouping' data in a pivot table. (Much better - as this isn't a one-off!) Haven't quite got there, as the groups have to be the same distance apart (if that makes sense) - but at least I'm on the right lines!
madcows

3littlefrogs Thu 08-Oct-09 15:18:57

I am glad I saw this. Can anyone recommend a simple book (noddy language) on how to use excel?
Thanks

madcows Thu 08-Oct-09 15:32:39

I've been using the Microsoft 'Training' and 'Demos' that are available here

http://office.microsoft.com/en-us/training/CR061831141033.aspx

The great thing about them is that they actually SHOW you what to do!
goodluck!
madcows

3littlefrogs Thu 08-Oct-09 16:49:47

Thanks madcows. I will try and get hold of a copy.

3littlefrogs Thu 08-Oct-09 16:50:52

Oh - hang on - that is a link, not a book. I will try it.

Join the discussion

Join the discussion

Registering is free, easy, and means you can join in the discussion, get discounts, win prizes and lots more.

Register now