Forum Discussion
inability to write formula w/ accurate results across 3 cells to compute commissions athigher amts
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?
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
william palmer try attached file
-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