Order value based on price breaks

Steven Edwards

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

Travis

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. 

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

Travis

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?

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.