Sorting data into ranges and comparing it to different options

[AATOlive]

Hi all,


I have travel household surveys and am sorting it for an assignment. The idea is sort out the data based on how many vehicles households own dependent on their income. An example of the data is shown in the first table:


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]INCOME[/TD]
[TD]NO. OF CARS[/TD]
[TD]NO. OF TRUCKS[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5320[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]100512[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17850[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

keep in mind, this is a made up sample, the actual data goes on forever. The table below is what I want the data to look like, with the values representing the average number of cars per household in that income range.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]INCOME RANGE[/TD]
[TD]AVG CARS/HH[/TD]
[TD]AVG TRUCKS/HH[/TD]
[/TR]
[TR]
[TD]0 - 1000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1001 - 20 000[/TD]
[TD]1[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]20 001+[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




And finally the graph attached HERE is what I would like to end up having. (A graph comparing the average number of different vehicles owned per income group)


I have used pivot tables for a previous assignment questions, but for examples that differed slightly, when I tried using it for this example, I just couldn't make sense of it. I can do them all individually, example, income versus AVG number of cars owned, and then a separate one for income versus AVG number of trucks owned. But grouping the income ranges each time is becoming tedious, especially since I am sure excel can do this and I am just wasting time.


If you could help or link me to any tutorials dealing with similar situations, I would be glad.


If you can think of a better tool to use, I am also happy to try it.


Thank you!

 

[FDibbins]

This doesn't quite give you what you want, but it may be a start...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]INCOME[/td][td]NO. OF CARS[/td][td]NO. OF TRUCKS[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

1000​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

5320​

[/td][td]

1​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

100512​

[/td][td]

3​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

17850​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]INCOME RANGE[/td][td]AVG CARS/HH[/td][td]AVG TRUCKS/HH[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]0 - 1000[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td]1000 - 1000[/td][/tr]

[tr][td]

11​

[/td][td]1001 - 20 000[/td][td]

1​

[/td][td]

0.5​

[/td][td][/td][td]5320 - 17850[/td][/tr]

[tr][td]

12​

[/td][td]20 001+[/td][td]

3​

[/td][td]

0​

[/td][td][/td][td]100512 - 100512[/td][/tr]
[/table]
E10 (which would actually go in A1)=MIN(IF($B$2:$B$5=$B10,$A$2:$A$5))&" - "&MAX(IF($B$2:$B$5=$B10,$A$2:$A$5))
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.
Then copy down