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!
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!
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.
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
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.
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
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?
A way: I just think it'll be harder for you to score the city pair comparisonsDo you see any advantages or disadvantages of the A-way?
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 = 56NYC = 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
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.
Been thinking on thisSo 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.
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
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.
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.
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 weekendOriginally Posted by waimea
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
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
---------------------------------------------------------------------------------------------------------------------
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! 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!
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?
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
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.
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
---------------------------------------------------------------------------------------------------------------------
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 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
