Product A, B & C are available in Material 1, 2 & 3 (9 products, 9 different prices). Problem I'm trying to solve is to figure out how to apply a % discount, then a flat $ discount (if applicable), based on a selection, only if the cell(s) are referenced. Example is col A has Prod A (A2), Prod B (A3), and Prod C (A4), Col B has 'Material 1 List Price' in B1 with prices in B2:B4, Col C has 'Material 1 Max Discount' in C1, with the following formula in C2 =IF(($I$2=ʺB2ʺ),SUM(((B2-(B2*$J$2))-$K$2)),IF(($I$5=ʺB2ʺ),SUM(((B2-(B2*$J$5))-$K$5)),B2)) and so on for C3 & C4, with two more sets of columns for D:G (Mat2 LP, Mat2 MD, Mat3 LP, Mat3 MD) with col I:K being the 'reference point' or input to customize discounts. I1 has "Range to apply discount:" with I2 being the dropdown selection for all available options (any of the 9 product/material combinations), J1 = "% Discount", J2 = dropdown selection of 0-40%, K1 = "$ Discount" with K2 = dropdown of $0-$25. The same range I1:K2 is copied down for I4:K5 to allow a 2nd discount combination to apply to any cells referenced in I5. The goal is to have the formula in each "Max Discount" column 'look for' it's referenced cell to be mentioned in the "Range to apply Discount" input location, then apply the discount. <html>
<body>
<img src="https://onedrive.live.com/redir?res...uthkey=!AGdh_-W1C3WgB1M&v=3&ithint=photo,.JPG" alt="https://onedrive.live.com/redir?res...uthkey=!AGdh_-W1C3WgB1M&v=3&ithint=photo,.JPG" title="Tooltip"/>
</body>
</html>
Any suggestion on how to solve the problem for the Max Discount columns, and additionally, any help on getting the 'Input' cells to reference multiple cells would be greatly appreciated!
<body>
<img src="https://onedrive.live.com/redir?res...uthkey=!AGdh_-W1C3WgB1M&v=3&ithint=photo,.JPG" alt="https://onedrive.live.com/redir?res...uthkey=!AGdh_-W1C3WgB1M&v=3&ithint=photo,.JPG" title="Tooltip"/>
</body>
</html>
Any suggestion on how to solve the problem for the Max Discount columns, and additionally, any help on getting the 'Input' cells to reference multiple cells would be greatly appreciated!
Last edited:
