• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I am a Baby ninja and this is my current nightmare

David Evans

David Evans

Active Member
I've been wrestling with a convoluted legacy worksheet that I'm busy "automating" - one challenge I cannot overcome is included in the attached workbook - the "Aggregated" calculation is currently *not* calculated - it is done manually! There are lots of them to complete and they are a source of a lot of time, effort and, yes, error ....

I've been trying to get SUMPRODUCT to work on it to no avail.

Any assistance would be greatly appreciated, Oh devotees of Chandoo!

Thanks in anticipation..


Dai
 

Attachments

  • Aggregation Example.xlsx
    24.6 KB · Views: 6
Hello David..

Not sure on which part of this file you want our Ninjas to help you with.. If you could brief on what was before and what you need as an output and list down the conditions that would be of great help

Regards,
Pavan
 
Thanks for your prompt replies and for pointing out my sillyness!

The worksheet is used to calculate fees associated with managing Investments - the values in Row 3 are the amounts in each of 3 accounts. The "normal" allocation breaks out each account by band - In the Aggregation allocation, the amounts in each account are aggregated to populate the fee range bands, resulting in lower fees for the investors.

I need a formula that will populate the range H10:K13, preferably without nested ifs (but I'll even take that solution!)

We have a formula that populates the Range C10:F13 (although I didn't include it - I made this example worksheet as the real worksheet is A TOP SECRET!! Only Q has the right to disclose it .... I can tell you that it uses nested ifs and is hard to follow.

The formula in range H10:K13 needs to be copyable as this worksheet is, how do you say, cumbersome.

Rows 10:13 are bands with values in Col B - These are examples - the actual numbers are larger as they represent significant Fund Holdings, but the concept is what I was trying to get across.

Many thanks to you all!
 
Last edited:
Please ask as many questions as you need to - I realize that this is a little different concept - I don't want to over-explain it, but I also understand if you say I'm not clear!
 
HI David,

Can you explain how reached the values in aggregate table? I suggest you to randomly fill the original sheet with numbers so that we can see how formulas are setup. you can change the cosmetic look of the sheet so that "top secret" doesn't leak out. :)
 
Faseeh said:
HI David,

Can you explain how reached the values in aggregate table? I suggest you to randomly fill the original sheet with numbers so that we can see how formulas are setup. you can change the cosmetic look of the sheet so that "top secret" doesn't leak out. :)
Click to expand...

Thanks for taking a look, Faseeh.

I've uploaded a newer version of the file with formula for the Normal Aggregation.

Let me try to explain the Aggregation! In Aggregation, the amounts in Account 1 "consume" all of Band 1 (10,000) and 4,000 of band 2. When we go to Account 2 in this method, we have used up the "allowance" for Band 1 and also 4,000 of the 15,000 available in Band 2 - SO when we allocate the 37,000 in Account 2, it goes to Band 2 first - filling it with 11,000. then the remaining (37,000-11,000 =26,000) is allocated to the next band (25,000) with 1,000 remaining going to the Over 50,000 band ....

Account 3's 5,600 is then applied to the over 50,000 as would any further amounts in Accounts .....

Does this help?

Thanks again for your willingness to look at this puzzle - any ideas will be most gratefully and graciously received.
 

Attachments

  • Aggregation Example.xlsx
    25 KB · Views: 6
Pavan.Sada.PS said:
Hello David,

Explain us on whats basis you arrived at the amounts mentioned in range H10:K13
Click to expand...

Thanks for looking at this - I've written an explanation below - or is it above?! I hope it makes sense to you - The numbers are allocated based on the bands in A10:B13 .....
 
Hi David,

how did you reach the values in cells I12, I13 and J13?? See attachment and my workout below your workout.
 

Attachments

  • Aggregation Example(1).xlsx
    28.9 KB · Views: 7
Jake Collins said:
realised some errors in the previous formulas. see attached sheet for corections.
Click to expand...
Appreciate the help - am off to work and will incorporate your ideas this morning. I *might* be back with a few minor queries, but it looks very sensible on a quick review - again - Sincere Thanks
 
You must log in or register to reply here.
Back
Top