Forum Discussion
inability to write formula w/ accurate results across 3 cells to compute commissions athigher amts
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
7 Replies
william palmerYou can also use a Table to formulate your solution refer to sheet2 in the attached file
cheers
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
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
- Corrected file is attached in my above response. Do u have any other query related to excel?
