or Explore Discussions

# Conditional quantities for pricing sheet?

✭✭✭✭✭
edited 07/22/20
07/22/20 Edited 07/22/20
Accepted

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:

1. If the number selected is 1, 2, 3, or 4, cost = \$10 and widget quantity is counted per the customer's selection.
2. 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?

Tags:

• ✭✭✭✭✭

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?

• ✭✭✭✭✭

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.

• ✭✭✭✭✭

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.

• ✭✭✭✭✭

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.