# Can Anyone Solve this Baseball Problem with Excel?

#### buzzman

##### New Member
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:

 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>

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.

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

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

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

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.

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

Replies
1
Views
618
Replies
1
Views
6K

1,219,093
Messages
6,146,266
Members
450,681
Latest member
ASBM24

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back