Can Anyone Solve this Baseball Problem with Excel?

buzzman

New Member
Joined
Jan 27, 2019
Messages
4
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:

PlayerHitsTotal BasesHRSolve for SinglesSolve for DoublesAssume Triples=0Calculated HitsCalculated Total BasesHits Minus Calculated HitsTotal Bases Minus Calculated Total Bases
Player A4.912.61.5 0=sum(D2:G2)=D2*4+E2*1+F2*2+G2*3=B2-H2=C2-I2
Player B25.843.01.0 0
Player C24.446.96.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
PlayerHitsTotal BasesHRSolve for SinglesSolve for DoublesAssume Triples=0Calculated HitsCalculated Total BasesHits Minus Calculated HitsTotal Bases Minus Calculated Total Bases
Player A4.912.61.450.053.350.004.8512.550.050.05
Player B25.843.01.010.5514.250.0025.843.050.00-0.05
Player C24.446.96.415.601.500.9024.446.90.000.00
The solved solution for player C should be the below solution since I assume 0 triples:
PlayerHitsTotal BasesHRSolve for SinglesSolve for DoublesAssume Triples=0Calculated HitsCalculated Total BasesHits Minus Calculated HitsTotal Bases Minus Calculated Total Bases
Player C (0 triples)24.446.96.414.703.300.0024.446.90.000.00

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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Jan 27, 2019
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Jan 27, 2019
Messages
4
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I didn't see any equations in cols N & O ??
 

Watch MrExcel Video

Forum statistics

Threads
1,129,713
Messages
5,637,927
Members
416,993
Latest member
ant8989

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top