Adding odd number

kasbac

Active Member
Joined
Jan 2, 2008
Messages
344
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Hope this helps.
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top