Conditional quantities for pricing sheet?
Smarties,
I am creating a pricing form and am stuck on a formula. In the example, the first 4 widgets cost $10. Each additional widget is after 4 widgets is $3. In the preceding cell [14 WIDGETS], I have a singleselect dropdown of 1, 2, 3, 4, 5+ (as I need these numbers to count the number of widgets sold for another cell/formula). What I'm looking to do:
 If the number selected is 1, 2, 3, or 4, cost = $10 and widget quantity is counted per the customer's selection.
 If the number selected is 5+, cost = $10, count [14 WIDGETS] quantity as '4' AND execute logic that displays the next column of '[ADDITIONAL WIDGETS]' with 5, 6, 7, 8, etc..
I was going to use IFBLANK because I do not want to have a dollar value in cells that have not yet been selected (as this will mess up my totals in the summary reports).
I have a feeling that I am overthinking this. Any thoughts?
Best Answer

Mike Wilday ✭✭✭✭✭
What about doing a nifty calculation and letting the user select specifically the number of units they want to buy? Then you could just have one column called Widgets.
=IF([email protected] <= 4, [email protected] * 10, 4 * 10 + ([email protected]  4) * 3)
That would calculate your total widget price in one cell with one formula. Would that work?
1
Answers
Could you share some screenshots? Its hard to picture what you're talking about. Are you looking for a formula to calculate the cost based on 14 = 10 each and then $3 for every additional? Or are you trying to make a form display content correctly? If you could send us screenshots of the form, the output from the form when users select more than 4 widgets I am sure we could help come up with a viable solution.
In [Widgets 14] the user will select the number of widgets. The first 4 widgets are $10, regardless of how many are selected. So, the price should calculate as $10 no matter what they choose. That said, I DO want to calculate the number of widgets sold, so '4' and '5+' should both calculate as '4'.
EDIT: I changed the Help Text in the form to help me out here. I have abandoned the conditional logic and will probably use counts of the first two columns to determine the total number sold.
What about doing a nifty calculation and letting the user select specifically the number of units they want to buy? Then you could just have one column called Widgets.
=IF([email protected] <= 4, [email protected] * 10, 4 * 10 + ([email protected]  4) * 3)
That would calculate your total widget price in one cell with one formula. Would that work?
Mike,
I just realized that I did not reply. Your solution worked! Thank you very much and apologies that it took a year for me to confirm your solution.