Excel Question

[DakAttack]

I've been using Excel for a long time, on and off, so I'm no master. I'm doing a specific spreadsheet of data on universal healthcare and I'm looking for a way to present multiple sets of data with multiple data ranges together side by side for comparative purposes. I can't for the life of me figure out of a way to make Excel consider multiple data ranges.

Is it possible, or am I screwed?

[Eohmer]

Re: Excel Question

Nah, I think it's possible, but it might be tricky. I wouldn't know how exactly though since I'm no expert, but I think it's possible. Good luck.

[Tekumel]

Re: Excel Question

I'm a bit braindead right now, so reading through that was like trying to read a bowl of alphabet soup. Can you explain in a little more detail?

[Eohmer]

Re: Excel Question

Wouldn't more detail make it worse if you're braindead? lol

He basically wants a lot of data side by side for comparison reasons.

[Tekumel]

Re: Excel Question

I gathered that much Eo. The question is...why not just type it in side by side?

The multiple data ranges is what's throwing me. I'm not sure if he's trying to do different data ranges in the same comparison, or like...all of one row would be one range, with the next row being different...

/confused

[Sabaron]

Re: Excel Question

you can use cell references to mirror data from two sheets (even formulas):

Click on the cell you want the data in and type "="

Select the sheet which contains the data you want to display and then click on the appropriate cell.

Press "Enter" to save the formula you have made.

If you use the "drag box" (a little black box in the lower right-hand corner of the cell you just made, it will populate data from adjacent cells.

You can edit the cell reference (for instance E11) by including "$" in the reference to restrict the column and/or row. Examples:

E11: when using the drag box dragging left or right will "adjust" the column. Dragging one cell to the left will populate the cell to the left with data from "F11". Similarly, dragging down will populate the cell below the original cell with "E12".

$E11: works the same as the "E11" example, except that when you drag left or right the column letter will remain fixed. Dragging one cell to the left will populate the newly rubberbanded cell with "E11", but dragging down will still populate with "E12"

E$11: works the same as "$E11" except this time, the row number will not change. Drag Left: F11. Drag Down: E11.

$E$11: restricts all "motion" no matter what direction you band the cells, the newly populated cells will contain a reference to "E11".

[Macht]

Re: Excel Question

Hmm, I'm not that great at understanding by reading. My brains ability to comprehend what I read sometimes messes up pretty badly and trying to figure out what exactly you are looking for Excel to do I'm not understanding.

I've done a lot with excel, if I can understand what your are looking for then I might be able to help. Otherwise reading Sabaron's post that seems to fit what you are asking. I'm starting to guess that you are pulling external data into excel and you want to make a sheet were you can have the external data housed together formatted how your want. That way when the external data is refreshed the sheet showing them all is also updated.

If that's what you are trying then Sabaron's post should be what you are looking for.

[DakAttack]

Re: Excel Question

Ok, I suppose it wasn't clear enough. None of you actually mentioned charts and graphs. It was probably too late for me to make a coherant post anyways.

I've built the table already and I'm looking to create a graph of all the data. On the X Axis should be the country, and the Y Axis shouldn't contain a data range at all because that might be confusing. For each country there should be multiple bars on the graph each a different color, each representing a different statistic.

What it's doing is using the first stat, population, as the data range which ranges from zero up into the tens of millions. This means small numbers such as percentages and ages will not display properly.

What I'm looking for is a way to either use multiple data ranges for the multiple sets of data that will be displayed side by side, or a way to combine graphs so that the data will be displayed side by side. What I'm getting at is the side by side comparativeness and such...

Thanks for the help so far though.

[Sabaron]

Re: Excel Question

Wouldn't instead of showing each country with it's own set of bars (which are from my understanding in different scales) to instead show multiple graphs of the same statistic with the X Axis representing the individual countries and the Y axis in the scale of the statistic for comparison. By arranging the bars by country and placing differently scaled statistics near each other, you are completely obfuscating the data. You need a Y-scale.

For instance: (flip my axes)

Statistic 1:
Country A: xxxxxxxxxxxxxxx
Country B: xxxxxxxxx
Country C: xxxxxxxxxxxxxxxxxxxxxxxxxxx
etc...
Statistic 2:
...

This way, you can compare the stats of each country. The rest is just pagination and print settings (for instance printing 6 pages on each piece of paper).

Personally, as a data analyst, I hate most graphs since all they do is make it harder for me to see what's actually going on (because of scaling and purposeful obfuscation). I only make graphs so I can display information to people who wouldn't understand it in a more raw format--I prefer a sortable group of derived and raw statistics to a pretty graph, but to each his own.

[Ririkai]

Re: Excel Question

Perhaps try using a pivot table with pivot graphs...
http://en.wikipedia.org/wiki/Pivot_table
I don't really use them... ever... though if you are not looking for something that doesn't do summation this might be useless.
How about using Access and do some query reports. Seems easier.

[Macht]

Re: Excel Question

lol, well have to agree with Sabaron again. If you need something like that better to do multiple row charts then a column chart. Whipping out the data displayed for X or Y axis confuses the chart.

Especially if you are not going to have the data following the same range, if you are going to adjust the data then you'd have to make clear the ratio adjustment. You could do it on a single chart if kind of "flatten" the data by set ratio's for each set of statistics.

Still a very messy and ugly way to achieve your goal. Not to mention adjusting by ratio some people will not notice and they'll take the chart's info wrong. Still like the way Sabaron suggested is the best to change ranges of displayed data, just make 1 chart for each statistic then marry them together with some asthetic's on the excel.