Sunday, June 17, 2012

yogi_Compute Prices Based On Input Parameters In Different Sheets

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #596   Jun 17, 2012

user datonn said:
IF formulas with multiple parameters (greater than and less than)
Hello everyone!
Say, I have a question that I have not been able to figure out, related to "if/then" types of output and multiple qualifying criteria/parameters inside Google Docs Spreadsheets.  I have a spreadsheet formula that I am trying to create which would essentially do the following:
I want to have users of a spreadsheet input three simple variables:
A. number of panels on a print product (either 6, 8 or 10)
B. size of the output (Small, Medium, or Large)
C. number of printed units to order...using something like the below:
If units is a value of between 0-500, then return a value of ("Minimum of 500 required"))
If units is a value of between 501-1000, then return a value of (B3*(3.33*1.1)) 
If units is a value of between 1001-2500, then return a value of (B3*(1.89*1.1)) 
Basically turning that "matrix" into one bottom-line number which I can then provide to individuals saying what said options would ultimately end up costing them.  A and B putting users into a category/basket of pricing information, then C basically multiplying the number of units by a different range of prices (get steadily cheaper, the more units one wishes to order).
I'm good at formulas, but something this complicated is giving me fits!  Consequently, if anyone has any ideas and/or can point me to tutorials or other discussion group posts which might lead me to a solution, I would greatly appreciate it.  FYI, I spent about an hour this morning searching through various posts and forums, and I am coming up blank.  Thanks in advance for any help I might receive!  - Derek
 I've since added new sheets that have that additional pricing information inside the same Google well as made the sheet editable to anyone with the link:
following is a solution to the problem