I have a lot of data but I am not sure how to analyze it with Excel.
Should I use regressions? Pivot tables? Something else?
What is the first step in analyzing big data??
I have a lot of data but I am not sure how to analyze it with Excel.
Should I use regressions? Pivot tables? Something else?
What is the first step in analyzing big data??
Power Query and Power Pivot and pivot tables.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi xld,
thank you for your answer!
I have tabular data and my first data table has 1600 rows and 100+ columns.
In what way should I try to transform my data before analysis? Should it be tabular or non-tabular?
I am trying to compare cities against each other and find out what city-city combinations is the best.
That amount of data is trivial for these tools.
You should look to denormalise it so that you can pivot the data and get the sort of analysis you require. Power Query can import that data as it is and unpivot it so that it can then be pivoted.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi again xld,
thank you for your reply!
I have unpivoted my columns using PowerQuery and I now have 3 columns, city name, metric and value.
How should I proceed?
On the Home tab, click the arrow on the Close & Load button (Furthest left) and select Close & Load To. In the popup dialog, select connection only and Add to Datamodel. Exit back to Excel.
Now in Excel, go to Insert>Pivot Tables, select 'Use this workbook's Data Model' from the popup dialog, you should see your query there which you can now use in a pivot table.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi xld,
thank you for your instructions, I now have a data model with my data in it but when I click on metric in my field list I get a list of all metrics.
Is it possible to single out them one by one in the way my data started out like?
Put the metric in the rows pane not the value pane.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi xld,
I have followed your instructions but I am not sure on how the unpivoted data when pivoted in a pivottable is better?
After I have my datamodel should I create measures to analyze the data in PowerPivot? Or try to use regression on the data?
What you have is a set of raw data that you can now analyse. How you analyse it I cannot say, I do not know the data or what you are trying to achieve.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi xld,
thank you for your reply. I don't know how I should analyze my data.
I am comparing cities against each other on a lot of different metrics, more is better and there is a min and a max score.
Say I have cities A, B and C.
Then I am comparing City A vs City A. City A vs City B. City A vs City C.
City B vs City A, City B vs City B. City B vs City C.
City C vs City A. City C vs City B. City C vs City C.
I want to find the combination of highest score between all combinations of the different cities.
Any suggestions on how I can analyze data that is as in the above post??
Why compare a city against itself?
If you have 10 cities (C01 - C10) and 15 metrics (M01 - M15) to compare, then I'd figure you'll have 15 x (10 x 9) / 2 = 675 'compares' comparing each metric for each city pair
e.g.
C01 - C02 - M01, C01 - C02 - M02, … C01 - C02 - M15Sounds like you started with a 10 row, 16 column array of data using my made up numbers above ??
C01 - C03 - M01, C01 - C03 - M02, … C01 - C03 - M15
….
C09 - C10 - M01, C09 - C10 - M02, … C09 - C10 - M15
Last edited by Paul_Hossler; 04-13-2019 at 06:40 PM.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hi Paul,
thank you for your reply! I am comparing a city against itself only because I loop trough "all items" in two comboboxes.
There is no value in comparing city A with city A.
I am not sure what I am starting with at the moment. I will have to look at my file again.
I will post a sample workbook. Do you have any suggestions on how I should analyze the "compares"?
As an CONCEPTUAL EXAMPLE .... … you could look at something like this
10 cities (GREEN), 3 metrics (YELLOW)with each metric having a weighting factor (ORANGE)
The BLUE are just random numbers 0-100 but could be your raw scoring of City-X against City-Y for Metric-M (Col A - Col K)
Each raw score is weighted (Col M - Col N) in Col P - Col W
Weighted scores are summed Col Y - Col Z
The weighted summary is sorted High to Low in Col AB - Col AC
I used 2 UDFs and array functions
F9 to recalc and see what happens
Capture.jpg
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hi Paul,
thank you for your reply! You are a conceptual example genius!
I am looking at your workbook right now and I am trying to understand it!
How did you determine the weights?
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hi Paul,
thank you for your reply.
How would you go about finding the city/cities with the highest score?
The 'best' City Pair is in AB2 in my example above. The City01-City02 has a score of 70 for Metric-01
You could give City01 a 70 and City02 a 70 for Metric-01, and then weight Metrics, and, etc.
Capture.JPG
This makes City-04 the 'best' with a composite score of 530.95
It really all depends on how you weight the metrics and how you choose to determine the 'best'
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3