inability to write formula w/ accurate results across 3 cells to compute commissions athigher amts

[william palmer]

good morning-this being the 1st time I do not know whether I am following the proper procedure to contact you and submit my question/problem with spreadsheet formula.
-At the direction of the Court, I am completing an accounting over several years of 2011 – 2020 for a trust, part of which is the proper determination of the commissions for the trustee to be reviewed by the Court.
-This is been difficult because the rules are very convoluted, but I have managed to get 95% of it completed and accurate.
-There are 3 levels at which the commissions are computed
-yr end bal which can be any amt such as follows: an amount <=300000 to >600000 which amount is placed in the starting cell for the computations in the three sequential cells to the right(in my setup) and this starting end of year balance will be dynamic yoy

-in cell 1 the statutory range is as follows: if the amount in the initiating cell is <= 400000 then (amt <=400000/1000*10.50)

In cell 2 if the initiating cell contents is >400000 but <=600000, then the formula must keep the amount computed in cell 1 and in cell 2 determine -what ever that amt is bet 400000 & 600000/1000*4.50 and if the amount in the initiating cell is > 600000 then work with 200000/1000*4.50 and if there is a remaining balance >600000 then
cell 3 (amt>600000/1000*3
-what is frustrating is that this is not hard using a calculator but I cannot get the formula correct is cell 2. However with so many years and variable amts which I am sure the Court will adjust figures so that a calculator will be impossible to use with the changes I know are coming

- the PROBLEM ARISES IN CELL 2 WHERE I CANNOT GET THE FORMULA TO RECOGNIZE THE AMT THAT EXISTS BETWEEN 400000 AND 600000 AND WHEN THE INITAL AMT DROPS TO SAY 525000 THE FORMULA LEAVES THAT CELL 2 BLANK
-this is a lot of explanation so perhaps a copy of the sheet with the formula is going to be the easiest way for you to cut through all the above.

J2 start cell and M,N,O 2 are the subject cells and N2 is the difficult one 
THX William R Palmer 051220 6:45 AM

[#2]

@william palmer try this.

If I understand you correctly, we need follwing:

1) range: below 400K, at 10.5

1) range: 400K-600K, at 4.5

1) range: above 600K, at 3

[#3]

@bhushan_z  -yes the results are correct with the initiating cell at the stated amount- what do i need do  other than thank for your guidance, to put it into an actual activitive sheet so i can complete and present to the Court

thx bill palmer

[#4]

@william palmerYou can also use a Table to formulate your solution refer to sheet2 in the attached file

cheers

[#5]

Corrected file is attached in my above response. Do u have any other query related to excel?

[#6]

@bhushan_z 

-Yours is closer but still problems if change yoy end amount from which computations are made

-Copied and pasted your operative section to my spreadsheet which i will attach

-if this did not change your formulas then when i change the YOY end amount say $300000- it should read $3150 but reads $4200

-if i change YOY amt to $550,000 cell #1 should read $4200 which is correct but 2cell should read $675 and the third cell reads negative number and therefore subtracts from the proper total due

-at $68500- the computations are correct 

-i just cant get cell 1 and 2 with the correct amt if the amts are less than the full $ amt that maximizes that particular cell

-i hope you can help and not give up on me

regards bill palmer

[#7]

@bhushan_z 

with respect to the problems with computations that I discussed with you in my post earlier this evening, the more I think about it. Your approach is really very clever.

-It is an approach, i.e. using percentages, if we make it dynamic solves the problems.

-For instance, if the year-over-year amount at $300,000 and the breaking point is $400,000 for the 1st calculation then we would do the calculation for $400,000 find the percentage 300,000 is of $400,000(75%) can take 75% of the commission is available for $400,000 and that would be the figure for the 1st amount up to $400,000  In the 2nd cell the maximum amount that the computation can be is on $200,000 so that if we are $500,000. We know what the computation is for that $200,000 amount and then subtract the amount in the end of year over year from $600,000 and then subtract $400,000 from that figure and find what percentage that is $200,000 and we have that amount. Commission the same thing for the 3rd cell. Now if my reasoning is not all hosed up than this makes the formula dynamic, for whatever amount is in the end of year-over-year initial cell-what are your thoughts. Thanks, Bill Palmer

[#8]

@william palmer try attached file