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

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?
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.
Help Article Resources
Categories
Check out the Formula Handbook template!