Adding odd number

[kasbac]

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

 

[rob.barnes01]

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

	A	B	C
1	ACCOUNTA	356	457
2	ACCOUNTB	763	864
3	ACCOUNTC	487	587
4	ACCOUNTD	302

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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.

 

[kasbac]

Hi Rob

that worked like a charm!

Thanks.