Forum Discussion
trying to make two functions from one cell value need help
I appreciate everything you have done and the attempts to help,
I also realize that your time is valuable and would like to pay you for your time.
Here is what Id like to have
any value in the C2 cell up to 100 would be multiplied by the daily exchange rate then the D2 cell will show that value. I would like the -10 to apply to the value in D2 provided the range is 1-100. I would then like d2 multiplied by B1 for a value in E2. eg). C2=50 x1.25 = 62.50. 62.50-10.00 =52.50 then that value multiplied by B1) if there are 4 units in B2 the value of e2 should be 210.
if the value is over 100, say 101 I would like the same calculation but with 30 removed
eg. ( c2=101 x 1.25 =126.25. 126.25-30=96.25 then that value multiplied by B2) if there are 4 units in B2 the value of E2 should be 385.
I understand this seems odd but the majority of my units I purchase are in the 125-700 range and making 30 per unit when buying 300-400 units weekly is pretty good, when I tried it with the percentages it would rise as the quantity would rise and I would end up under paying. if you could get it to operate like that would be great. please forward an email address I will send an e transfer for you time
Ed
Ed--I think you have the ability to do what you seem to want to do.
But I'm giving you one more variation on the progressive discount based on percentages, this with far more flexibility. See the tables and "what-if" spaces on the "Constants" page. I graduate this for each $100. It follows (in format) the US tax tables, and probably the tax tables of most nations.
I mention tax tables specifically because what you were doing would be comparable to regressive taxation, rather than progressive...because your discount percentage gets smaller as the amount getslarger. You are rewarding folks for small orders, penalizing them (relatively) for larger. So IF a client wanted to game your system, they'd have incentives to place multiple orders just over $100, so as to get the maximum discount. For what it's worth, you should realize you ARE giving people a percentage discount, it's just a far more generous percentage at smaller levels. $30 at $125 is 24%; $30 at $700 is 4.3%. Do you really think that makes sense? Don't you want to incentivize folks to place larger orders?
So this new proposal begins at 1% for the first $100, goes up progressively--and you can change the rate of progression to get the result you want. But I hope you see that this gives incentives to make higher orders, as the discount gets marginally bigger, but only marginally so. Nevertheless, if you consider this still "too rich" all you have to do is make the percentage column go up slower. Right now I made the intervals 2.5 percentage points larger with each new level. Change those. See what happens.
Thank you for offering to compensate; I'm totally a volunteer here, getting my "reward" through helping people solve their problems and extending my own understanding of the wonders of Excel in the process. (And sometimes having to try to persuade folks to alter their way of thinking)