Can Anyone Solve this Baseball Problem with Excel?

[buzzman]

I like to play a simulation baseball game where each batter has a card which attempts to replicate the players actual performance and dice are rolled to determine the player bat outcome.
The company that sells the game also sells a spreadsheet and claims the player data from the cards is in the spreadsheet.
But the spreadsheet only defines the hits, total based and HR for each player.
It does not list the singles, doubles or triples but I want to calculate this data based on the hits, total bases and HR.
Unfortunately, I realized there are multiple correct solutions unless I set triples equal to zero which is true for most players.
For example, a triple and a single is 2 hits and 4 total bases, two doubles is also 2 hits and 4 total bases. So I decided to assume triples=0 and then solve since this only allows one correct solution
There are about 1000 cards and I don’t want to spend the time to manually enter the data from all these cards and I want a solution that can hopefully solve for all the data at one time without having to solve one at a time.
Below is an example of the data for 3 players. The spreadsheet has the green columns, I want to solve for the purple columns, the blue columns are calculations I use to see if the solved solution is correct.
I tried to use solver and set objective for cells J2:J4 to value of 0 and cells K2:K4 to value of 0 by changing cells E2:F4
But it did not work since excel says the set objective must be a single cell reference
I have Excel 2010 but will upgrade to a higher version of Excel if that is the only way to solve this.
Below is data for 3 players from a spreadsheet but I don't know how to paste the spreadsheet image here which would be easier to understand:

[TABLE="width: 828"]
<colgroup><col><col span="3"><col span="3"><col><col><col><col></colgroup><tbody>[TR]
[TD]Player[/TD]
[TD]Hits[/TD]
[TD]Total Bases[/TD]
[TD]HR[/TD]
[TD]Solve for Singles[/TD]
[TD]Solve for Doubles[/TD]
[TD]Assume Triples=0[/TD]
[TD]Calculated Hits[/TD]
[TD]Calculated Total Bases[/TD]
[TD]Hits Minus Calculated Hits[/TD]
[TD]Total Bases Minus Calculated Total Bases[/TD]
[/TR]
[TR]
[TD]Player A[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]12.6[/TD]
[TD="align: right"]1.5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]=sum(D2:G2)[/TD]
[TD]=D2*4+E2*1+F2*2+G2*3[/TD]
[TD]=B2-H2[/TD]
[TD]=C2-I2[/TD]
[/TR]
[TR]
[TD]Player B[/TD]
[TD="align: right"]25.8[/TD]
[TD="align: right"]43.0[/TD]
[TD="align: right"]1.0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Player C[/TD]
[TD="align: right"]24.4[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]6.4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]Note: Hits, Total Bases & HR are rounded to 1 digit after decimal but could be lower by 0.05 or higher by 0.05[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"]Solved Values should include 2 digits after the decimal and the hundreds digit should end in 0 or 5 (1.45, 1.50 and 1.55 are valid solved values,[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"] but 1.52 is not a valid solved value if there is a way to incorporate this)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]Below are correct values after I manually added information from the cards[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player[/TD]
[TD]Hits[/TD]
[TD]Total Bases[/TD]
[TD]HR[/TD]
[TD]Solve for Singles[/TD]
[TD]Solve for Doubles[/TD]
[TD]Assume Triples=0[/TD]
[TD]Calculated Hits[/TD]
[TD]Calculated Total Bases[/TD]
[TD]Hits Minus Calculated Hits[/TD]
[TD]Total Bases Minus Calculated Total Bases[/TD]
[/TR]
[TR]
[TD]Player A[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]12.6[/TD]
[TD="align: right"]1.45[/TD]
[TD]0.05[/TD]
[TD]3.35[/TD]
[TD]0.00[/TD]
[TD="align: right"]4.85[/TD]
[TD="align: right"]12.55[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.05[/TD]
[/TR]
[TR]
[TD]Player B[/TD]
[TD="align: right"]25.8[/TD]
[TD="align: right"]43.0[/TD]
[TD="align: right"]1.0[/TD]
[TD]10.55[/TD]
[TD]14.25[/TD]
[TD]0.00[/TD]
[TD="align: right"]25.8[/TD]
[TD="align: right"]43.05[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]-0.05[/TD]
[/TR]
[TR]
[TD]Player C[/TD]
[TD="align: right"]24.4[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]6.4[/TD]
[TD]15.60[/TD]
[TD]1.50[/TD]
[TD]0.90[/TD]
[TD="align: right"]24.4[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]The solved solution for player C should be the below solution since I assume 0 triples:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player[/TD]
[TD]Hits[/TD]
[TD]Total Bases[/TD]
[TD]HR[/TD]
[TD]Solve for Singles[/TD]
[TD]Solve for Doubles[/TD]
[TD]Assume Triples=0[/TD]
[TD]Calculated Hits[/TD]
[TD]Calculated Total Bases[/TD]
[TD]Hits Minus Calculated Hits[/TD]
[TD]Total Bases Minus Calculated Total Bases[/TD]
[/TR]
[TR]
[TD]Player C (0 triples)[/TD]
[TD="align: right"]24.4[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]6.4[/TD]
[TD]14.70[/TD]
[TD]3.30[/TD]
[TD]0.00[/TD]
[TD="align: right"]24.4[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

You can upload the workbook to DropBox and then post a link to that site back here.
Someone may then be able to jump in and assist.

 

[buzzman]

https://www.dropbox.com/s/im3v8sj88ig01qt/Baseball Problem.xlsx?dl=0

 

[Michael M]

So, I'm still a little confused....one is that I don't follow baseball !!
Are you suggesting that the calculations that you have used in the blue columns are correct. ??
How did you arrive at the results in the purple columns ??
You can use the round function to get to within 0.05 in your calculations

 

[buzzman]

So, I'm still a little confused....one is that I don't follow baseball !!
Are you suggesting that the calculations that you have used in the blue columns are correct. ??
How did you arrive at the results in the purple columns ??
You can use the round function to get to within 0.05 in your calculations

I want excel to solve for the blank purple column cells. I created the formulas in the blue columns to check if the data that is entered in the purple column is correct. There is only one solution for each line. I can figure it out by just entering numbers until columns j & k are zero (+/- 0.15 since there is rounding error). But I have 1000 players to figure out and I don't want to do trial an error to figure this out.

The problem basically comes down to you are given hits, total bases, HR and triples=0. And must calculate singles and doubles based on this data. You must know the below baseball facts to figure out the answer.
Hits=singles+doubles+triples+HR
Total bases=singles*1+ doubles*2 + triples*3 +HR*4

Can excel solve for two values at the same time subject two objectives

 

[Michael M]

CAn Solver resolve one player correctly ??
If so, use the macro recorder to solve that one player, then post the recorded code back here.

 

[buzzman]

Thanks for the comments it helped me figure it out myself. Solver isn't required and each player can easily be solved with the equation i entered in the spreadsheet columns N & O.

 

[Michael M]

I didn't see any equations in cols N & O ??