# Divide the amt amongst on rational basis

1. ## Divide the amt amongst on rational basis

Dear Team,

Happy New Year to all ....

PFA the excel file showing the working of Limit and Outstanding bal.

I need to give a formula in col. M which allocates amount of cell M21 between cust ID "a" on rational basis.
Similarly, amount of cell M22 needs to be allocated between cust ID "b" and so on.

There is 1 condition.
Total amt in col N (Total O/s amt) should not exceed exposure limit in col D.

There are approx 50000 rows having diff. cust ID and A/c no. in the database.

Is there anyway we can automate this ?

2. ## Re: Divide the amt amongst on rational basis

If I understand correctly then the following formula should work:
Formula:
`Please Login or Register  to view this content.`

If I am missing something please provide your expected values for M3:M17 so that we can attempt to replicate those values automatically using either formulas or codes.
Let us know if you have any questions.

3. ## Re: Divide the amt amongst on rational basis

Thanks a lot...
I will get back to you.

4. ## Re: Divide the amt amongst on rational basis

Dear JeteMc,

you may reshuffle the rows considering actual scenario and allocate the amount on FIFO basis. But the amt. should not exceed Balance exposure limit (Col F)

5. ## Re: Divide the amt amongst on rational basis

How do i re-attach the revised file with desired output?

6. ## Re: Divide the amt amongst on rational basis

Manually put in the expected values for cells M3:M17, save to your hard drive, then upload to this site.
You could also type a post that lists the expected values of M3:M17.
Let us know if you have any questions.

7. ## Re: Divide the amt amongst on rational basis

PFA the file for your ref.

I have updated required data in column M.

Rows have been shuffled up-side-down since rows would not be in perfect sequence.

8. ## Re: Divide the amt amongst on rational basis

The following formula yields the same values as those manually placed in column M when pasted into M3 and copied down:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

9. ## Re: Divide the amt amongst on rational basis

wow...

Thanks a lot.

10. ## Re: Divide the amt amongst on rational basis

Moved to the correct forum section.

11. ## Re: Divide the amt amongst on rational basis

JeteMc,

PFA the revised file (refer tab pivot).

Is there any formula which gives me the desired out of col B ?

12. ## Re: Divide the amt amongst on rational basis

JeteMc,

The above question in continuation of the chain of mail...

13. ## Re: Divide the amt amongst on rational basis

The formula that is in B5 is a good place to start, I just modified it a bit so that it reads:
Formula:
`Please Login or Register  to view this content.`

It yields the same values as those that were previously in column B.
Let us know if you have any questions.

14. ## Re: Divide the amt amongst on rational basis

wow... thanks a lot.

Its working fine.

PFA the file.

We have to arrive the output in col Y.
I have manually updated the desired output in col Y.

Your help would make my day.

15. ## Re: Divide the amt amongst on rational basis

I don't understand the rational of the figures that are in column Y. For customer 'a' the balancing figure is the first value (associated with A/c No. a01). For customer 'b' the balancing figure is the last value (associated with A/c No. b04). I can not figure out why the positioning of the balancing figures is different, and if I can't figure it out, I can't write a formula to do it. Perhaps if you could explain why the balancing figures are in different positions for different customers someone might be able to help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1