Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Order value based on price breaks

Steven Edwards
Steven Edwards ✭✭✭
edited 12/09/19 in Archived 2015 Posts

Hi,

 

I have ~30 products each with four price breaks and I need to determine the value for each order (a single product type is listed per row) based on the ordered quantity and its associated price break.

 

The really clunky method I have used so far is two main nested IF formulas and additional columns:-

 

The first main nested IF (30 levels deep, one per product) determines which product has been ordered and puts the first price break into an additional column. This is repeated four times, once for each price break.

 

The second main nested IF then checks the quantity ordered, determines the applicable price break from the four additional columns and simply multiplies the quantity by the price break.

 

Does anyone have any ideas for a neater method available in Smartsheet? In Excel the Lookup functions would be used.

 

 

Thanks

Tags:
· Share on FacebookShare on Twitter

Comments

  • Travis
    Travis Employee
    edited 05/28/15

    Instead of having one formula that contains the prices for all 30 items, you could have a row for each item, then a formula in each row that calculates the cost of the item based on quantity ordered. You could them sum all 30 rows to get a total. 

    · Share on FacebookShare on Twitter
  • Steven Edwards
    Steven Edwards ✭✭✭

    Hi Travis,

     

    Thanks for the comments.

     

    Your suggestion would work but it does not really fit with what I am trying to do. Some more details......

     

    The Smartsheet is populated each time I get a new order with one item per row i.e. if an order has three items there will be three rows in the Smartsheet. 

     

    As each row can be any of the 30 items I need to first confirm the part being ordered (large nested IF) and then once that is known I can work out the value for that row.

     

    The 30 items can all have different prices hence needing to determine the exact part before checking the price breaks.

     

    I am trying to keep all the price calculations in a single sheet, the resulting values are then linked to an additional sheet to display a summary of the orders on an annual basis.

     

    Perhaps this is just something that Smartsheet does not cater for in a "neat" manner.

     

     

    Thanks

    Steve

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    edited 06/02/15

    It sounds like you are looking for a VLOOKUP functionality which we do not support. The nested IF statement would be your best bet at this point. Perhaps you could split up your items into smaller categories to make the formula smaller?

    · Share on FacebookShare on Twitter
  • Steven Edwards
    Steven Edwards ✭✭✭

    Hi Travis,

     

    That's what I was thinking of, the nested IF works OK but just is a little "clunky".

     

    I was looking at the requirement today and could create a new sheet with links to the "main" PO sheet. The new sheet would (following your suggestion) have one row per item and the price breaks as child rows, this allows a very short IF statement to be used to determine value. This would require manual pasting of links etc. but I may well try it out at some stage.

     

    Thanks for your help.

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories