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:

Player | Hits | Total Bases | HR | Solve for Singles | Solve for Doubles | Assume Triples=0 | Calculated Hits | Calculated Total Bases | Hits Minus Calculated Hits | Total Bases Minus Calculated Total Bases |

Player A | 4.9 | 12.6 | 1.5 | 0 | =sum(D2:G2) | =D2*4+E2*1+F2*2+G2*3 | =B2-H2 | =C2-I2 | ||

Player B | 25.8 | 43.0 | 1.0 | 0 | ||||||

Player C | 24.4 | 46.9 | 6.4 | 0 | ||||||

Note: Hits, Total Bases & HR are rounded to 1 digit after decimal but could be lower by 0.05 or higher by 0.05 | ||||||||||

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, | ||||||||||

but 1.52 is not a valid solved value if there is a way to incorporate this) | ||||||||||

Below are correct values after I manually added information from the cards | ||||||||||

Player | Hits | Total Bases | HR | Solve for Singles | Solve for Doubles | Assume Triples=0 | Calculated Hits | Calculated Total Bases | Hits Minus Calculated Hits | Total Bases Minus Calculated Total Bases |

Player A | 4.9 | 12.6 | 1.45 | 0.05 | 3.35 | 0.00 | 4.85 | 12.55 | 0.05 | 0.05 |

Player B | 25.8 | 43.0 | 1.0 | 10.55 | 14.25 | 0.00 | 25.8 | 43.05 | 0.00 | -0.05 |

Player C | 24.4 | 46.9 | 6.4 | 15.60 | 1.50 | 0.90 | 24.4 | 46.9 | 0.00 | 0.00 |

The solved solution for player C should be the below solution since I assume 0 triples: | ||||||||||

Player | Hits | Total Bases | HR | Solve for Singles | Solve for Doubles | Assume Triples=0 | Calculated Hits | Calculated Total Bases | Hits Minus Calculated Hits | Total Bases Minus Calculated Total Bases |

Player C (0 triples) | 24.4 | 46.9 | 6.4 | 14.70 | 3.30 | 0.00 | 24.4 | 46.9 | 0.00 | 0.00 |

<colgroup><col><col span="3"><col span="3"><col><col><col><col></colgroup><tbody>

</tbody>