#### kasbac

##### Active Member
Hi there

I have an issue creating a formula that I hope you can help me with. I have four different accounts, A, B, C and D.

I want the value coming from account D be to spread out across account A, B & C. The trick is that I want the values of A, B & C to be whole numbers based on the following rules:

If dividable by 3 the value coming from D should be equally distributed across all the three other accounts.

If there is an additional value of .333 per account after the addition of the value from account D has been made the additional value should be added to account A ie A should increase by .777 and B & C should decrease by .333

If there is an additional value of .666 per account after the addition of the value from account D has been made the additional value should be added to account A and B ie. A and B should increase by 0,333 and C should decrease by 0,666

I know the above can be a bit tricky to understand so please let me know if you need any clarification on the above.

Thanks in advance all helps is appreciated

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Based on your description, I am assuming whole dollar amounts for account balances. If not, the rounding in these formulas will probably cause you some trouble.

In my example, account balances in column B and the formulas below entered into Column C will accomplish what you want.

Excel 2007
ABC
1ACCOUNTA356457
2ACCOUNTB763864
3ACCOUNTC487587
4ACCOUNTD302

</tbody>
Sheet1

Worksheet Formulas
CellFormula
C1=IF(MOD(B4,3)>0,ROUNDUP((B4/3),0),B4/3)+B1
C2=IF(MOD(B4,3)>1,ROUNDUP(B4/3,0),ROUNDDOWN(B4/3,0))+B2
C3=ROUNDDOWN(B4/3,0)+B3

</tbody>

<tbody>
</tbody>

Hope this helps.

Hi Rob

that worked like a charm!

Thanks.

Replies
1
Views
205
Replies
2
Views
453
Replies
3
Views
475
Replies
0
Views
640
Replies
8
Views
407

1,211,679
Messages
6,103,245
Members
447,850
Latest member
thebuzzman15

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