Need to determine correct charge

Thank you for your prompt reply. I have highlighted three examples on the attached. I would like it to find the corresponding code in the price list, then look at the amount ordered and it work out with price break it belongs to and return the corresponding price.

I believe you need to have a normal price column and a discounted price column based on the amount ordered.

I ideally don't want to go down that route. The first page is a download out of the system, could these parameters not be built into a formula?

Hi,

Sorry I missed this.

If there is only one price break, this is charged no matter what,
if there are two price breaks, both at 1, this is an error and I need to fix this in our order system
All would be charged at the lower amount.

I work in a manufacturing plant, the more we make, the cheaper it costs so we charge the customer less.

Maybe you can structure it like in the 'Adjusted Prices' sheet?

Thank you for your reply.

This didn't work. It isn't taking into account the price break. It would need to find the SKU and then look at the price break.

@Billy,

Thanks but there is never a set structure that we offer a price break at. Some could have a price break at 23,994 and then another product could be offered 109,000.

Maybe try making a table with the discounted rate percentage based on the amount ordered? You can fill the column with every option.

Perhaps you can put the SKUs in groups for set discount rates. There needs to be some sort of structure for how a discount is applied otherwise calculations cannot be done.

How are they derived?

Also I noticed that the same SKUs have different prices after sorting the columns.

This is the full list of prices that are in the system, so they are static. Could it not look up the SKU then if there is only one amount, use that but if there are more than one, select the relevant price?

Sure it can be done with Index Match arrays. How would it determine what is relevant between the prices?

There are different price breaks for the same SKU, As they would get it cheaper if they order larger quantity.

This is an error, I will be doing a cleansing piece separately, there would only be one price for this.

Don't misunderstand I do want to help. Please clean the data first then structure it so one sheet has the prices for each item. There also has to be a way to determine the discounts. Whether they be forecasted amounts with percentage discounts or however they are derived.

Getting the correct structure in place will ensure accuracy and ease of use for the future.

Sorry, it is difficult to understand tone on messages. I am extremely grateful for your assistance.

I ideally don't want to go down the route of having to manipulate the data as 'Product Prices' is a direct download from our order processing system and as I have no insight to how the account managers agree pricing with their customers, it would be too time consuming to take out the duplicates and then work out the percentages.

I've been looking through Google and I keep coming across match and index etc but I can't find a way to use these and incorporate below and above the numbers that I have.

Thanks again.

P.S. I am going through the data now to try and remove the duplicates that shouldn't be there.

Yes but it kept returning 41.60 for the 5th SKU down despite there being 5 different prices. When I changed the quantity, it didn't change either.

Sorry, yes, I sorted it by SKU and then by quantity - smallest to largest. I have cleansed the data now, please see the attached.

My god, I think they've got it!!

Thank you so much for your assistance.