Hello
Im trying to have excel tell me what set of values matches the closest with the numbers I'm trying to test to find the best match
I have attached a sample sheet of what im trying to figure out
Hello
Im trying to have excel tell me what set of values matches the closest with the numbers I'm trying to test to find the best match
I have attached a sample sheet of what im trying to figure out
Please update your forum profile. 10 is your Windows OS, not your version of Office. Thanks.
We'll need to know how you'd derive the average and how each of the components should be weighted.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I would like to weight each cell equally at 25%
I don't quite understand the first part, sorry
How are the averages shown worked out? Average of what?
To weight each column equally, we need to know the minimum and maximum values for each.
Where would you put this unknown player? Second base?
That's what Im trying to figure out? What position should he play
I want it to basically say to me
This Unknown player is projected to hit .324 with 26 home runs, 95 rbi's and score 89 runs
Historically(by looking at the averages on the left of the sheet that calculates each positions averages in each stat for the past 5 years)
First Base has the highest comparison score, so its the closest set of numbers out of the 4 positions for this unknown player so he should play First Base
and Second Base has the lowest score so he should never play there
Im not trying to find out the minimum and maximum just what position compares the best, if you still need those numbers I apologize
Last edited by appletree943; 04-09-2021 at 12:38 PM.
Yes, I know, however if you want to automate a spreadsheet to work it out for you, you need to tell it what to do to work it out, so you would have to know how to work it out yourself.That's what Im trying to figure out? What position should he play
I would need those numbers, yes, to give each item a weighted score, but as I know nothing about baseball, I'll leave this to someone who can talk to you and understand the relative significance of all those things you mention.
Good luck.
ok I understand and thank you for your help
Last edited by AliGW; 04-09-2021 at 12:47 PM. Reason: PLEASE don't quote unnecessarily!
I agree with AliGW, we need you to tell us how you measure/calculate "how closely matched" a set of values is (a question that really isn't an Excel question).
Using Pearson's correlation coefficient (Excel's CORREL() function) as a placeholder for whatever calculation you like for "how closely matched" a set of stats is, the programming strategy in a spreadsheet is mostly about how you use relative and absolute references. In F4, I enter =CORREL($I$4:$L$4,B4:E4), then copy that down to F7. Again, note the mix of relative and absolute references. Then I can look at column F and determine which value (for Pearson's correlation coefficient, it would be the largest value closest to 1) indicates the best fit. Then I can use MAX()/MIN() or other functions along with lookup functions to make conclusions (if I need to program that part into the spreadsheet).
Originally Posted by shg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks