Split Amount Evenly Across Rows with 2 Decimals

[mzlee]

Hello, 

I want to split an amount of an invoice distributed evenly using 2 decimal points. This is the table I currently have 

Field1	Field4
958A	1187.39
958A	1187.39

I want to get to the Split Amount ensuring I have 2 decimals: 

Field1	Field4	Split Amount
958A	1187.39	593.70
958A	1187.39	593.69

Field1 could also have 3 records of the same number and need to split it evenly. 

I used the formula tool [Field4]/2 Data Type: Fixed Decimal Precision 10, scale 2 and gave me the 593.70 on both records, but need the second record to be 593.69. I know the Multi-row formula would work, but I haven't been able to get it to work yet. 

thanks for the help! 

[alexnajm]

If you want a "dynamic" count, I would do a Summarize tool to group on Field1 and Count Field4 to get the amount the values should be divided by. Then you can Join the count value back to the original data on Field1 and then use a Formula to divide your amounts by the Count.

For the rounding piece, you could look at the multi-row formula in some way to round up one but not the other - that'll depend on what logic you want to introduce to decide which one should be up versus down

[OTrieger]

@mzlee 
@alexnajm gave you 95% of the solution. To get the 2nd number or 3rd or 4th etc. what you will need to do is very simple, with a Multi-Rows Formula, do the following calculation, Field4 - ((Count-1)*Split Amount) that will give you the exact amount left from the total.

[binuacs]

@mzlee one way of doing this

[mzlee]

Hi , how would this formula be adjusted if for example I had more invoices with the same number and need to split the amount by 3 or 4 instead of 2? 

[binuacs · Accepted solution]

@mzlee 

[mzlee]

@binuacs this worked! thank you so much!