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 [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?

Find more posts tagged with

Forms

Sales

Formulas

functionality

Accepted answers

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(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?

All comments

Mike Wilday

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

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

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.