More troublesome IFs, ANDs and ORs - Good job I'm already bald!

[Purplerattler]

Hi gurus,

I have a sheet (attached, no sensitive info as its all example data at the moment) that, thanks to the help from you guys, not has a PTO Charge (Paint to order) which is set at an arbitrary 50% charge.

Now comes the difficult bit, unless I'm overthinking it; I have a list of data on the creatively-named Info sheet that has doors, cornices, panels etc but they all need a DIFFERENT percentage levied.

i.e. Single Shaker Door 486x596 has a Paint-to-Order charge of 15%, whereas T&G End panel has a Paint-to-Order charge of 69.94%.

Is there an easy (or even a complicated one cos i love my formulas lol) that will calculate the total and correct PTO charge on a list of products, even if they are all different? I'm wondering f this is going to end up down a database road, but hoping we can do this in excel as I can get my boss to navigate that program now lol.

I have attached the whole sheet (loads of locked cells but totally unlocked for this exercise).

Hope this is something I can do...

Thanks in advance for any assistance, as always it is very gratefully received.

Rob.

[AliGW]

Are you still using Excel 2010? If not, please update your profile.

[Purplerattler]

Not by choice lol. Every machine at our premises uses Office 2010.

Ta

[AliGW]

You would need a column in the product list that indicated which to use and then add that using a VLOOKUP or a SUMPRODUCT.

Can you please fill in two or three products in the quote sheet and MANUALLY indicate what the results should be. Please annotate that sheet with the logic.

[Glenn Kennedy]

Where is the list of applicable PTO rates?

[Purplerattler]

Where is the list of applicable PTO rates?

It was originally built into the PTO Charge cell as a standard 50% rate, I was told this morning that each product may now end up having different values, so I guess first things first is add a column like Ali has suggested to show the PTO PRICE rather than discount, then somehow flag those products with the higher price if the PTO colour is labelled 'Yes' on the top of the sheet?

[Purplerattler]

You would need a column in the product list that indicated which to use and then add that using a VLOOKUP or a SUMPRODUCT.

Can you please fill in two or three products in the quote sheet and MANUALLY indicate what the results should be. Please annotate that sheet with the logic.

Thanks Ali,

I'm headed out for a CT scan so might not be able to play with this until later this evening but will pick up any reponses on my phone.

thanks

[AliGW]

As Glenn has implied, you need to add the PTO rate for each product in a column on the product list. We cannot help further until we have a definitive list of those rates.

[Glenn Kennedy]

Good luck with the scan!


I'd do something like this... see the yellow cells which I have modified on both sheets.