Ever-increasing discount curve

[toddbeck]

I have a unit list price of $10. At 100,000 units, I want the price to be $2.00.

How can I calculate an ever-increasing discount curve between those two points? I want it to be that the more you buy, the deeper the discount.

My amateur efforts so far result in curves that go negative, so I'm paying the customer to buy my stuff. Also, I don't want the total (units x price) to reach a point where it reverses and starts to go down--meaning you pay less total for 300 units than you would total for 200 units.

Does that make sense? Please help. Thank you!

 

[GlennUK]

I'm eager to try out all the new formulas you guys posted here and see which one fits best

The reason I'm so impressed by @GlennUK 's formula is because it accomplishes nearly all of this in a single line

=10-MAX((MIN(LOG10(ROUNDDOWN(K3,IF(K3>100,-2,-(K3>10)))),5)-1)*2,0)

That formula calculates a sliding scale discount value, has MinUnitNumber, MaxUnitNumber, MaxUnitValue, MinUnitValue, StepRate, and I "think" LYRate(using the LOG10 function), so its super close to the solution, the problem is only @GlennUK can decrypt it ,If I hadn't seen it working perfectly with my own eyes I would not have believed it

Hi again, I hope this post helps you decrypt my formula : the ROUNDDOWN just makes the steps, being 10 for numbers less than 100, and 100 for numbers over one hundred (and no step if less than 10). The LOG10 generates the discount (-1 and doubled to get the slope I wanted), maxing to a discount of 8 at 10 to the power 5 (log10 of 100,000 is 5, -1 is 4, doubled is 8, subtracted from 10). So, you can choose the max and min and slope by altering the various parts of the formula, and/or removing the step part, and maybe by using a different log (other than 10) to get a different discounting factor. Have a look at this and see if it helps:

 

[Dave Patton]

XionicFire​

Your profile does not show the Excel versions that you use; we can provide clearer suggestions if we know the versions that you use.


The internet was not working properly when I made my post.
Certain edits did not show. This does not impact this thread; points are just edits for clarity.

1. I mentioned that the Sum alternative would require Array Enter CSE with older versions of Excel but not current versions.
2. I did not say to name the tables; I stated name the bracket array information and the rate differential information.
See Formulas Name Manger
- The array of Bracket data is named aB Refers to ={0;30000;40000;50000;60000;70000;80000;90000;100000}
- The array of Rate Differentials is named aR Refers to ={10;-1;-1;-1;-1;-1;-1;-1;-1}

I look forward to seeing the solution that you decide best fits your requirements.

 

[XionicFire]

XionicFire​

Your profile does not show the Excel versions that you use; we can provide clearer suggestions if we know the versions that you use.


The internet was not working properly when I made my post.
Certain edits did not show. This does not impact this thread; points are just edits for clarity.

1. I mentioned that the Sum alternative would require Array Enter CSE with older versions of Excel but not current versions.
2. I did not say to name the tables; I stated name the bracket array information and the rate differential information.
See Formulas Name Manger
- The array of Bracket data is named aB Refers to ={0;30000;40000;50000;60000;70000;80000;90000;100000}
- The array of Rate Differentials is named aR Refers to ={10;-1;-1;-1;-1;-1;-1;-1;-1}

I look forward to seeing the solution that you decide best fits your requirements.

Thanks for the update Dave, I'm testing ALL the formulas I can find haha so far I have found19 different ways to do this, once I'm done ill post the XLSX file here so everyone can take a look at all the different ways, some do some things, others different things, trying to find the best fit., I haven't had time to examine Glens new post, I have a feeling ill need a keg of coffee and a free weekend just to understand how he did it haha, even with the decryption key on hand

As for your question, I Use office 365, not sure what version but its auto updated and on subscription so I'm guessing its the newest excel

Wow.. 5 mins later of looking everywhere I could not find the version, that in itself should tell you what version I'm using... I would say I'm impressed and surprised that I cant find what version i have anywhere but its Microsoft. so... yeah that .. haha

 

[Dave Patton]

Thanks for the reply.

Excel 365 is the version information that is relevant.

 

[XionicFire]

Just wanted to post here something rather amazing.... I was trying to figure out the formula I just hadn't had the time to sit down and wrap my brain around it then it clicked, ive heard about this magical ChatGTP.. lets try it so i type:

explain =10-MAX((MIN(LOG10(ROUNDDOWN(K3,IF(K3>100,-2,-(K3>10)))),5)-1)*2,0)

And I get this:

This is a mathematical expression written in Excel syntax, which calculates a value based on the input value in cell K3. Here is a breakdown of the formula: [LIST=1] [*]ROUNDDOWN(K3,IF(K3>100,-2,-(K3>10))) - This rounds down the value in cell K3 to the nearest multiple of either 100 or 10, depending on its value. If K3 is greater than 100, it rounds down to the nearest multiple of 100 (e.g., if K3 is 563, this would become 500). If K3 is between 10 and 100, it rounds down to the nearest multiple of 10 (e.g., if K3 is 63, this would become 60). If K3 is less than 10, it remains the same. [*]LOG10 - This takes the base-10 logarithm of the rounded-down value from step 1. [*]MIN(LOG10(...), 5) - This takes the minimum of the value calculated in step 2 and 5. [*]MAX((MIN(LOG10(...), 5) - 1) * 2, 0) - This subtracts 1 from the value calculated in step 3, then multiplies by 2. If the result is negative, it is changed to 0. Finally, the maximum value between the result and 0 is returned. [*]=10 - The value calculated in step 4 is subtracted from 10. [/LIST] Overall, the formula calculates a value based on the input value in cell K3, where higher values in K3 result in lower values in the final calculation. The exact interpretation of the result depends on the context in which the formula is used.

It explains it perfectly... I was able to make it work within minutes with this explanation, I would like to say I was shocked but I think I was beyond that, however being an IT guy I said ok it might just be a fluke... I wonder what happens when I do the reverse...

So I asked it to generate another excel formula just telling it plain text what I wanted

I said:

I need an excel formula that calculates a value, if the value in cell A2 is greater than or equal to 208,000 return 15, then If the value in cell A2 is less than 208,000, calculate a value using a linear equation relative to the max and the min with 2 steps one at at 250 with a return of 70 and one at 7500 with a return of 100.

It returned:

=IF($A$2>=208000,15,(IF($A$2<=7500,MIN(100,100-($A$2-250)*(100-70)/(7500-250)),MIN(100,100-($A$2-250)*(100-15)/(208000-250)))))

Which worked.... perfectly

I'm not sure how my parents felt but I'm currently feeling a mix of holy...what's going on, and am I really getting old? i guess this is what my grandma must have felt when cell phones came out....

Just wanted to let you guys know about this ABSURD tool, it may just make all of us obsolete, ive thrown at it hundreds of theoretical formulas ive been trying to break my brain to get them to work for YEARS, it took it a couple of tries but it got it done....

What a time to live in guys.. what a time....

 

[Peter_SSs]

Looks like you had a good experience with it but I would say don't get too excited yet about ChatGPT. A very common experience is that you get something that looks like it might/should work, but in fact does not. Also, for the future, please be aware of #17 of the Forum Rules. It is a new rule so no sanctions at this stage.

 

[XionicFire]

Looks like you had a good experience with it but I would say don't get too excited yet about ChatGPT. A very common experience is that you get something that looks like it might/should work, but in fact does not. Also, for the future, please be aware of #17 of the Forum Rules. It is a new rule so no sanctions at this stage.

Well I did extensively test the formula, I has absolutely skeptic that the thing would actually spit out something that complex and that it would work

Indeed as you say the initial answer spit out didn't work but was close enough to what I wanted that I was able to figure out where the computer screwed up and correct the error, giving me a useful result in a couple of minutes without the hours and hours of searching for parts of the solution

Regarding rule 17, understood, I didn't imagine it would be an issue but now I know thanks for pointing it out, I will be mindful of it in the future.

But please don't dismiss this tool as useless or demonize it on the forum, I am serious when I said I had several formulas I've been trying to make work for years and with the help of this thing I was able to knock out 6 of them on an afternoon, all working perfect and my customers are ecstatic, I understand it's not a magical one shot solution for excel formulas but it did help me understand in very clear terms why my formulas were not working and that helped me solve the issue of why they were not working with relative ease, for us non excel masters this is an absolute game changer.

There's a LOT of useful stuff here in the forums the problem is that at least for me, im nowhere near the level of some of you guys here, I see your formulas and I might as well be looking as ancient mesopotamian cuneiforms, this thing is like a master to idiot translator of what the formula does, where we screwed up, and how we can fix it, most importantly, on demand, it definitely has value, if not as a formula making tool, certainly as a formula making assistant, please take this into account too

Just wanted to put my 2c regarding this out there

As always thank you all for the invaluable help and work you do to help us noobs be better at this (and get promoted/not fired at our jobs haha)

 

[Peter_SSs]

Just wanted to put my 2c regarding this out there

That's fair enough.

But please don't dismiss this tool as useless or demonize it on the forum,

Just to clarify, we are not saying that people cannot use the tool to try to answer their own question instead of (or before) coming to the forum. The reason for the rule is to stop a "flood" of ChatGPT answers in the forum, particularly if they have not been well-tested. We want helpers to use their own brains/knowledge to suggest solutions or perhaps provide a link to a known solution elsewhere.

 

[XionicFire]

That's fair enough.


Just to clarify, we are not saying that people cannot use the tool to try to answer their own question instead of (or before) coming to the forum. The reason for the rule is to stop a "flood" of ChatGPT answers in the forum, particularly if they have not been well-tested. We want helpers to use their own brains/knowledge to suggest solutions or perhaps provide a link to a known solution elsewhere

Totally agree with you there, no one should post a Fresh outta the output hole ChatGPT solution, unless they have tested it and made sure it's working and it correctly and properly answers the questioners original question, otherwise you are right, there will be a flood of untested/useless answers that will cloud good answers, everyone will have a hard time distinguishing what works from all the garbage

IMHO I think it's fair to ask/require of anyone posting a ChatGPT assisted/generated formula to disclaim the origin of it and to say if he has tested it himself to ensure it works. This should keep useless stuff to a minimum while helping advance a solution to the person asking the original question

I hope someday stuff like this evolves to s point where it's truly magical and works right off the bat, help manuals will be a thing of the past, when that day comes I fear for my job hahaha but until then....

Thanks to everyone helping to keep the forums free of garbage, we do appreciate all your hard work.