Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 60

Thread: How to analyze big data with Excel

  1. #21
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi Paul,

    thank you for your reply! I really like your idea with a comsposite score.

    I will download your workbook later today and see if I can recreate it.

    Thank you again!

  2. #22
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi again Paul,

    I have now had a good look at your two conceptual examples and both are breathtakingly good! I am looking at your UDF right now.

    However, my data output from my macro is not in the same form. Is there an easy way to change my output macro to suit your conceptual examples?

    I have condensed my data to only have 3 metrics.

    1. Make my data the same as in your example
    2. Make the code aware of how many cities there are

    eg. my first batch of cities has 40 cities, the next batch has 59, the next 26 etc.. down to a mere 1 city.

    All suggestions are welcome.
    Attached Files Attached Files

  3. #23
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Sure

    However, you would not want to include city pairs (CityX CityY) where (a) X = Y and where (b) X > Y

    (A) X = Y would double count (e.g. City01 City02), and

    (b) X > Y would have been counted earlier where X < Y (i.e. City02 City 04 counted, but City04 City 02 would be counted again)

    Weighting factors?

    Did you want to score city pairs or each city by itself?
    ---------------------------------------------------------------------------------------------------------------------

    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

  4. #24
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi Paul,

    thank you for your reply! You are too clever for me and this is above and beyond what I could ask for!

    I want to score each city for itself. Since the combination of city pairs only is a comparison and I want to find the cities with the highest composite score.

    I am not sure about Weighting factors, I was planning to use it as you did in your conceptual example with one weight for each metric.

  5. #25
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Q: what does the originalest data look like? You said output of your macro, implying that it's processed or reformated somehow

    Q: I assume the cities have real names and not -nn numbers ("New York City", Los Angeles", "London", ...)

    Q: I assume the metrics have real names ("Size", "Quality of Life", Good Fishing", ...)



    Your basic premise (as I understand it) is to (A) compare two cities and 'grade' them against each other using various metrics (e.g. "LA vs NYC on size is 66")

    It is NOT to (B) just 'grade' each city on the metrics
    (e.g. "LA on size is 66" and "NYC on size is 75")

    I've never done it the A-way before, just the B-way
    ---------------------------------------------------------------------------------------------------------------------

    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

  6. #26
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi Paul,

    thank you again for your reply! It is getting late here and I will make a sample workbook tomorrow.

    The cities have real names, the metrics have real names and your premise is correct (I think).

    Do you see any advantages or disadvantages of the A-way?

  7. #27
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Do you see any advantages or disadvantages of the A-way?
    A way: I just think it'll be harder for you to score the city pair comparisons

    Example: say that the Metric = "Quality of Life"

    LA - NYC = 56

    LA - London = 67

    London - Sydney = 78

    The raw data using the "Each City" approach in post #20 would have

    LA = 123

    London = 145

    etc.




    B way: I just think it'll be easier for you to score each city for each metric

    Example: say that the Metric = "Quality of Life" and the score for individual cities are

    LA = 56
    NYC = 67
    London = 78
    Sydney = 89

    The sum by city and weighted would give you the composite score


    Doesn't have to be 0 - 100, I've seen just five values used -2, -1, 0, 1, 2
    ---------------------------------------------------------------------------------------------------------------------

    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

  8. #28
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi Paul,

    thank you for your reply! I have read all of your posts again and you are spot on!

    I don´t want to double count and I don´t want to use the A way.

    I have condensed my metrics down to two metrics.

    Metric A is the score of city vs city. Metric B is the score of city vs state. I am planning to weight my metrics 50% and 50%. So city01 vs city02 in metric A could be 55 and metric B could be 77. Metric A is a combination of other metrics and a score of 55 means that there are 55 comparisons where metric A is above the City 2 value and 77 is also a combination of metrics where metric B is above or equal to the state mean.

    Ex. City01 has a value of size that is greater then City02 and also greater then the state mean. The state mean is calculated from all cities in that state.

    So what I am trying to do now is to get my output (in the attachment above) to be as in your example.

    But I am not sure how?
    Last edited by waimea; 04-19-2019 at 01:21 PM.

  9. #29
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    So city01 vs city02 in metric A could be 55 and metric B could be 77. Metric A is a combination of other metrics and a score of 55 means that there are 55 comparisons where metric A is above the City 2 value and 77 is also a combination of metrics where metric B is above or equal to the state mean.
    Been thinking on this

    Metric 1 - So if

    1 vs 2 = 55
    1 vs 3 = 45
    1 vs 4 = 65

    2 vs 3 = 70
    2 vs 4 = 60

    3 vs 4 = 30

    how would you generate a single rank-able score for

    1 = ?
    2 = ?
    3 = ?
    4 = ?

    Ranking a PAIR of cities is easy: 2-3, 1-4, 2-4, 1-2, 1-3,3-4

    But I don't know what that tells you



    Metric 2 is much more straight foward

    1 = 35 above state mean
    2 = 15 above state mean
    3 = 45 above state mean
    4 = 20 above state mean

    Rankings are City 3, 1,4, 2
    ---------------------------------------------------------------------------------------------------------------------

    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

  10. #30
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi Paul,

    thank you for your reply! I am looking at sports rankings where they count wins, draws or losses.

    The best city would be the city with the most wins?

    I am working on a sample workbook with my macro that creates the output, I will post it as soon as I get it more complete.

  11. #31
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Here is a sample workbook which represents my actual workbook pretty good.

    I am using a macro to loop through two comboboxes and store the value of metric A and metric B in a new worksheet called Score.

    Is is the table in Score that I want to analyze and find the best combination of cities.
    Attached Files Attached Files

  12. #32
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Any suggestions on how I can analyze this data?

    The sample workbook uses the state with the least number of cities.

  13. #33
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by waimea View Post
    Any suggestions on how I can analyze this data?

    The sample workbook uses the state with the least number of cities.
    Didn't forget -- got busy this weekend

    Trying a few approaches
    ---------------------------------------------------------------------------------------------------------------------

    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

  14. #34
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    OK, see if this floats your boat. I didn't want to go too far in case it wasn't what you were looking for


    For the first composite metric

    I compared the score for each metric for each combanation of cities, and if CityX+MetricM minus CityY+MetricM was positive then add 1 to CityX/CityY score, if negative subtract 1


    For the second composite metric

    I compared the score for each metric for each combanation of cities, and if CityX+MetricM minus Average for MetricM was positive then add 1 to CityX score, if negative subtract 1

    Capture.JPG

    The worksheet "Dump" is not really needed - I just used it to display intermediate array results

    The worksheet "Score" is not automatically created yet



    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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

  15. #35
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi Paul,

    thank you for your reply! It is getting late here and I am not sure that I follow what you are doing.

    I will take a fresh look tomorrow at your workbook. I think that you float my boat! Thank you again for your response!

  16. #36
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi again Paul,

    I have now had a long look on your workbook and I don't understand all of your code in mod_PH1.

    However, I think that I understand your output.

    You are taking "CityX+MetricM minus CityY+MetricM " to avoid duplicates?

    I don't understand what you mean with +1 or 0 and NOT +1 and -1?

    Thank you again for your help with this!

    EDIT: I am not sure but I think that it is the best way to start with "Score" as input?






  17. #37
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Like I said, I'm struggling with meaningful analysis for city pairs. One city at a time is easy


    What I tried was ...

    1. Comparing Karlshamn against Karlskrona for 20 metrics, if Karlshamn > Karlskrona for 7 and Karlshamn < Karlskrona for 12 and even for 1, then I scored the Karlshamn - Karlskrona pair as -5 ( i.e. 7 - 12 + 0)

    I could just count the 'greaters than' and then Karlshamn would = 0 and Karlskrona = 12


    2. I started with the 'Data' sheet and 'Score' was the last. I'd format 'Score' better to make it more understandable ("easy enough for a VP to read" )


    3. I think I'll take a shot at just a single city comparison and see what it looks like
    ---------------------------------------------------------------------------------------------------------------------

    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

  18. #38
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi Paul,

    thank you for your reply. I am very grateful for your help on this.

    It isn't an easy ask and I am also not sure on how to do a meaningfull analysis.

    What makes the city pair analysis hard? I am not sure that I follow on the why.

  19. #39
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    The City Pair just feels like comparing apples and oranges

    I don't see knowing that (Karlskrona - Olofström) is 'higher' than (Karlshamn - Sölvesborg) is useful, but then again it's not my call


    City1 - City2 Summary #Metrics
    Karlskrona - Olofström 6
    Ronneby - Sölvesborg 4
    Olofström - Ronneby -2
    Karlskrona - Sölvesborg -2
    Karlshamn - Olofström -2
    Olofström - Sölvesborg -4
    Karlskrona - Ronneby -5
    Karlshamn - Karlskrona -5
    Karlshamn - Ronneby -8
    Karlshamn - Sölvesborg -10


    It's just that I'm used to (in my tiny world) more of a single parameter evaluation

    This is just a simple one city at a time scoring matrix to rank each city (not each pair)


    Capture.JPG
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    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

  20. #40
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    174
    Location
    Hi Paul,

    thank you for your reply! I will start with your city by city analysis and see what I get.

    Maybe you are right about apples and oranges! A while back I posted a question about regressions, could regressions be used on the city pairs somehow?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •