Forum Discussion
Division by fixed number based upon choice
mathetes - Thank you for the spelling correction and I greatly appreciate your help~! I also came across the "data fixing element '$x$x' (for columns C & E)" in my quest for solutions to this query - Here is the link requested ->>> https://docs.google.com/spreadsheets/d/12UiC5ncBIqy2veOOvlmVgxSlwYGZPOUb/edit?usp=sharing&ouid=106533383641305730260&rtpof=true&sd=true . There are just three (3) fixed choices - As these products come in: A) 5ml B) 10ml C) 15ml sizes.
Could I first give you a spelling correction?
Number based on oil you're using
Now to your question: there are always multiple ways to get from A to B with Excel. I'm attaching a possible solution, but I think we need some clarification (and maybe modification) of what you seem to be seeking.
You'll notice in the attached spreadsheet--which does agree with yours for the first set of ml counts, up to 5 ml--that as you cross the 5 ml and 10 ml boundaries, the total price drops significantly. Kind of suggesting, for example, that if you're planning on ordering more than 2.5 ml, you really should order double the quantity for the same price. And so on...
My spreadsheet is based on the following assumptions about what you describe:
- for quantities up to 4.999 ml, divide by 80
- for quantities from 5 ml up to 9.999 divide by 160
- for quantities from 10 ml up to 15 (or even higher) divide by 240
Is that really what you want? It seems to me that it might make sense to make larger quantities start adding on at a lower rate, but (kind of like the inverse of our US tax tables) always begin with the full rate for the lower quantity, offer a slightly lower rate for the excess above 5 or 10.
So let us know how you envision using this...
Since you have a spreadsheet already, would you mind posting a copy of it on OneDrive or GoogleDrive and paste a link here granting edit access. That would make it a lot easier to help you.