Forum Discussion
trying to make two functions from one cell value need help
here is the actual page Im working on so you can see what Im attempting to accomplish
so basically its a buying sheet for used catalytic converters
i will attempt to add the formula to d2 and g2 then copy formula down each Colum. im away for the next few hours and may not get to it until morning but will keep you updated
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)
- Hello again, I have played with the constants and tried different values but unfortunately it will not work, it removes too much from value as the prices rise.
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 This now incorporates the last formula I sent, only in one cell.
You may have overlooked the fact that with that last iteration I totally changed your logic, making the deduction a matter of percentages (which I suggested I'd reduce if you really were to use this way of thinking). Go back and read that description before you take it any further. But basically it takes 10% off the first $100, and then 30% off all the rest, the balance that exceeds that first $100.
I think I suggested you change the constants (in the second sheet) to something like 5% and 15%.....
- Ahh yes. Forgot.
2426 is password - OK. I took a look and can see the context with this a lot better. The sheet is locked with a password, so nothing I can do but look at it. If you have questions or need help, let me know...although we're taking off for about two weeks of vacation so I'll only be sporadically checking, if at all. Fortunately, there are many others here who can help.
