EXCEL 2007 Experts Needed

[ThunderFlash]

I have been given a large amount of data, over 1000 respondents.

What i need from the data is the number of people who ticked a certain box by the certain region.

EG, 46 people from New York are looking to invest in gold in the next 12 months.

One column says where the person is from, then there is one column for curent investment (value 1) then the next column says looking to invest (value 2). If there is no investment, there is no value.
Thanks :thumb:

[joker3327]

Not much to go on lol.... what format is the data? how are the fields seperated ? how was the original data captured? how are you importing the data?

[ThunderFlash]

Ok what was given to me was a spreadsheet of over 1000 respondents.

The respondents each listed where they were from, so one column goes Albany, albany, albany........all the way down to west new york, west new york.

Then column CG onwards is have you invested in gold, some will have number 1 if they have, blank if not. CH is are you looking to invest. Number 2 indicates yes. Then CI is are you investing in hedge funds, 1 yes. CJ is are you looking to invest in hedge funds, 2 is yes blank is no.

I'll need to see how many people from Albany are investing in individual things and are looking to invest.

Essentially I want C= Albany * CG =1 etc

[alexnifty]

Not totally sure what your data looks like, however:

Probably needs a pivot table. Click anywhere in your data then go to Insert > Pivot Table.

You probably want your regions to be in the row box (drag from choose fields to the row labels box) then put your current and looking to invest into the values box. You will probably need to go into the values field settings and change it from SUM to COUNT. This will just count the number of times there is a value and not the total of the values.

[ThunderFlash]

http://forums.aria.co.uk/%3Ca%20href=http://img97.imageshack.us/i/exceltu.jpg/%20target=_blank%3E[IMG]http://img97.imageshack.us/img97/3010/exceltu.jpghttp://img97.imageshack.us/img97/3010/exceltu.jpg (http://img97.imageshack.us/i/exceltu.jpg/)

Uploaded with ImageShack.us (http://imageshack.us)

Here's a screencap of data

[joker3327]

Or from the sound of it...if I am getting you right... you can in 2007 use Auto Filter for several colums.... so example would be insert a new row at the top give it relevent headings ..then filter on location, little down arrow...... this will show all the locations... put a tick in the box you require...you can have mulitple choices eg Albany and albany... then do the same for the funds you require... this will then filter your choices by location eg Albany and albany and 1 in hedge funds etc ..

make sense ?

[ThunderFlash]

Yeah I tried that, problem is I have about 100 locations and that would still be long!

[joker3327]

Then as Alex says your only other option would be a pivot table..

Not sure if you know how to push out a pivot table....so..

http://www.timeatlas.com/5_minute_tips/chunkers/learn_to_use_pivot_tables_in_excel_2007_to_organiz e_data

http://www.youtube.com/watch?v=7zHLnUCtfUk

if you do ...just ignore the link lol..

[alexnifty]

Pivots are probably the easiest way of making sense of data. Your other option might be to come up with a tortuous COUNTIF or INDEX formula, but that's way more complicated than using filters or pivots.

[ThunderFlash]

Lads that's chuffing brilliant.

I've got all the counts, is there anyway of making them into percentages of region responses within the table?

[joker3327]

Lads that's chuffing brilliant.

I've got all the counts, is there anyway of making them into percentages of region responses within the table?

All you need too know.. !

http://office.microsoft.com/en-us/excel-help/calculate-percentages-HP001141712.aspx

[alexnifty]

So you want (Invest/Responses)*100 ?

Should be able to do a count of the number of responses by playing with the pivot so it counts regions.

By the way if you doubleclick the pivot you will get a table in a new tab which you can do more editing on and make it look nice for presentation.

[ThunderFlash]

All you need too know.. !

http://office.microsoft.com/en-us/excel-help/calculate-percentages-HP001141712.aspx

I know them, but you can't edit a Pivot graph like that apparently


So you want (Invest/Responses)*100 ?

Should be able to do a count of the number of responses by playing with the pivot so it counts regions.

By the way if you doubleclick the pivot you will get a table in a new tab which you can do more editing on and make it look nice for presentation.

I've done a count on regions as well, I selected field value settings and "show value as % of" but it won't allow me to select a region as a whole, just individual ones

[alexnifty]

Is it because your location is all in the same cell? So you have a whole string in one place instead of region and then city in separate columns?

[ThunderFlash]

FFFFFFFFFFFS.

After a morning of doing this, I get a call saying it isn't needed. Joy.

Thanks though for your help :)

[alexnifty]

FFFFFFFFFFFS.

After a morning of doing this, I get a call saying it isn't needed. Joy.

Thanks though for your help :)

Welcome to the Data Analysts world, my world. Assignments are either on too short a deadline to be completed properly, or they have enough notice but are next to useless.

[ThunderFlash]

They asked me to do 3 reports, then I gave them the 3, then they say "actually we meant 13"

Not enough swear words in the world to describe them

[joker3327]

FFFFFFFFFFFS.

After a morning of doing this, I get a call saying it isn't needed. Joy.

Thanks though for your help :)

Ha ha ha...just had the same thing happen ..been working on a project for 9 days showing analysis of an errors popping up over the last 2 years on a certain Pin entry device ... just got an email saying not to bother now as they are changing the hardware as they forgot to take out a support contract for the original hardware and no one would look at the data anyway..!

I feel your pain ! :ninja:

[ThunderFlash]

It's sods law, they never tell you early on to stop, always after you've built up unhealthy stress levels on the project:censored: