Division by fixed number based upon choice

New Contributor

I would like to know how to build a simple spreadsheet based upon: 1) If 5ml then divide by 80 2) If 10ml then divide by 160 3) If 15ml then divide by 240 - I would like to have the person enter one of those three [3] parameters 5ml,10ml,15ml - Or I could just make it simpler (if need be) by just requiring the simple number 5,10 or 15 - Either way would work for me.



4 Replies



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.

best response confirmed by Bill_Stuart (New Contributor)

@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 ->>> Price Per Drop - 15ml_Simplifed - 2023.xlsx .  There are just three (3) fixed choices - As these products come in: A) 5ml B) 10ml C) 15ml sizes.

I replied on the thread
This is not a solution to this issue - This is a clarification that there are three (3) fixed amounts that need to be divided by three (3) fixed numbers - 5ml divided by 80 - 10ml divided by 160 - 15ml divided by 240