Comparing 1 Series Of Values To Multiple Series Of Values To Find It's Best Match

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.

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

That's what Im trying to figure out? What position should he play

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.

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

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).