Conditional quantities for pricing sheet?

BrianT · 07/22/20

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 [1-4 WIDGETS], I have a single-select 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 [1-4 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 over-thinking this. Any thoughts?

Mike Wilday · 07/22/20

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(Widgets@row <= 4, Widgets@row * 10, 4 * 10 + (Widgets@row - 4) * 3)

That would calculate your total widget price in one cell with one formula. Would that work?

Mike Wilday · 07/22/20

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 1-4 = 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.

BrianT · 07/22/20

In [Widgets 1-4] 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.

Mike Wilday · 07/22/20

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(Widgets@row <= 4, Widgets@row * 10, 4 * 10 + (Widgets@row - 4) * 3)

That would calculate your total widget price in one cell with one formula. Would that work?

BrianT · 10/26/21

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.

Conditional quantities for pricing sheet?

Best Answer

Answers

Help Article Resources

Categories