Aggravating Aggregation formula ...

[David Evans]

Hi Folks -

It's Friday pm and I cannot get my head around this ...

The idea is to allocate the Assets to the appropriate bands, having taken into consideration any value in the Prior Aggregated Assets. Cells E16:E19 have the desired result, but of course I need a formula that will do it

As usual, all assistance greatly appreciated

 

[Hui]

I think you want in F16:
=MIN($D16,(C$3-C$4)-SUM(F$15:F15))
Copy down

If that is wrong, Can you tell us what answer you expect and in which cells?

 

[NARAYANK991]

Hi ,

I am not sure this is correct ; please verify.

Narayan

 

[David Evans]

I think you want in F16:
=MIN($D16,(C$3-C$4)-SUM(F$15:F15))
Copy down

If that is wrong, Can you tell us what answer you expect and in which cells?

Thanks for looking at it Hui -
The answers I was looking for are in Col F - I've half-cracked it with the help of my Actuary Daughter, by using two cols - D & E and then subtracting the difference to get the answer in Col F ....
I was just going to Add the two formulas to jam them into one cell.
If you can offer some insight with a UDF to make it a single cell solution, I'll be very grateful! For a UDF, I'm hoping to derive something like Function(Assets, Prior Aggregated Assets) as two cell references, and bingo! Right now, this is done in a very cumbersome manner, that takes a lot of Excel real estate....

I've uploaded a "new"file for you with all the horrid merged cells removed

 

[David Evans]

Hi ,

I am not sure this is correct ; please verify.

Narayan

Thanks for you help Narayan - I was in the process of sending a file back to Hui, which explains it further, but you seem to have grasped it .... Now I want to create a UDF, as explained in my previous note.

I have created the UDFs for Un-Aggregated (without the pesky Prior Aggregated Assets) which I should share with you guys .... I'll see if I can dig it up here this evening.

 

[David Evans]

Hi ,

I am not sure this is correct ; please verify.

Narayan

Hi Narayan -

It works, however, the tier 4 calculation goes awry if the sum of Aggregated Assets and Assets is greater than the sum of rows C16:C19. The logic of the Tier 4 calculation is a touch different from the prior 3, in that is Over 50,000,000, I believe - I am tired and will return to it in the morning, after a game of squash!
Thanks again for your help - it's very close to being there

 

[NARAYANK991]

Hi David ,

Can you upload a workbook with appropriate data ?

Narayan

 

[David Evans]

Hi David ,

Can you upload a workbook with appropriate data ?

Narayan

Ooops! Sorry - I thought I uploaded it - Duh!

 

[NARAYANK991]

Hi ,

Sorry for the delay ; from the data I am not able to understand which cell has a problem ; can you explain ?

Narayan

 

[David Evans]

OK Folks - here is a solution to the "Aggregation" together with a UDF calculation. It's not the single cell solution that Narayan had proposed, but it does overcome the issue with his formula and, more importantly, it helps illustrate the UDF, although in this example it's still a SubRoutine - I have yet to amend it to be a true UDF - will do that in the am.
Thanks for all the assistance and ideas.

Hi ,

Sorry for the delay ; from the data I am not able to understand which cell has a problem ; can you explain ?

Narayan

 

[David Evans]

Hi Narayan -

If you look at the file Narayan Example I posted, you will see that when the sum of prior aggregated amount and assets exceeds 100,000,000 (or the sum of the bands) the aggregation goes wrong - it's because the 4th row of the banding is simply "everything greater than this number" - your formula derives the correct result while the numbers are less than 100,000,000 but goes awry - i fixed it in the Correct Aggregation Example and included the UDF code to calculate the related fees.

Thanks for your help - you got me the major part of the way there! Appreciated!

 

[NARAYANK991]

Hi David ,

No issues ; I always maintain that a sample file should have realistic data , and data which has all the possible variations ; any formula which is proposed can be thoroughly tested against the data.

If the data does not cover all possible variations , the proposed solution may work for the uploaded data but may fail when the data changes to a situation which the formula did not take into consideration.

Narayan