This is fairly straight forward. We just need to break it down into separate goals.
Your first step should be to set up two ranges on the Output tab. These two ranges will cover the PM and SS weight/cost data that you provided on the Informations tab.
I have chosen cells J3:K31 for the PM weight/cost and cells M3:N62 for the SS weight/cost data.
In the J & M cells, you will need to enter the highest weight for each weight range. (e.g. cell J3 = 2, cell J4 = 3 etc. cell M3 = 0.5, cell M4 = 1 etc).
In the K & N cells, you will need to enter the cost. (e.g. cell K3 = 7, cell K4 = 8.5 etc. cell N3 = 8.5, cell N4 = 10 etc).
(I intend to use VLOOKUP to check these ranges. So in cell C3, my formula would be:
Once you have prepared the two ranges as instructed above, upload the updated workbook and I will give you the rest of the solution.
Bookmarks