Hello Forum
Im not a excel guru so this may not be best practice, however:
I work in a sales team and want to create a calculation for a pricing based on drop down list results.
The product has 3 categories Standard, Pro Business
Each category has 4 sub categories, each have a different monthly and upfront cost
Product
A
B
C
Sub_Cat
A
B
C
D
Depending on which product and sub_cat is selected will display the monthly pricing in a cell from a list of pricing and the upfront cost of the respective selection in another cell.
I have IF(B5=I10,J10, IF(B5=I11,J11, IF(B5=I12,J12))) however this only covers the output of product A and the various pricing of sub_cat a, b, c. I think I need nested OR formulas as well.
B5 – pick your product
I10 – first on sub category list
Or if
Pick your category = Product B and sub category a, b, c or d. print the corresponding cell containing the respective price.
Ive tested this which results in a #value error but think that I might be going about this wrong. =IF(AND(B5=I10,B8=H9),J10 IF(AND(B5=I11,B8=H9),J11))
I want to the formula to look like
IF Product_A AND Sub_Cat_A is selected in the drop down list – output monthly cost A
OR IF
Product_B AND Sub_Cat A is select – output monthly cost in this cell and upfront cost in this cell
OR IF
P_A AND SC_C
OR IF
P_C AND SC_A
…
For all the different options.
Thank you
Bookmarks