Variable Dependent Calculations

SeveralTradesLater

New Member
Joined
May 30, 2023
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
Hi Excel Community/Smart folks,

What formula would I used to resolve the challenge below? Where,
If Cell A2 is Product Category A then D2*C2 , if A2 is Product Category B then D2+E2*C2.

Assume the product list has 20 or so more products.

Hope someone can assist.

Thank you in advance.

Sheet 1: Rates
A​
B​
C​
D​
1​
Product​
Fee Percentage 1​
Fee Percentage 2​
Product Category​
2​
Orange​
15%​
5%​
A​
3​
Apple​
18%​
3%​
B​
4​
Cherries​
33%​
3%​
B​
5​
Banana​
18%​
2%​
B​



Sheet 2: Product Order Form
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
Product Type (Data Validation List Based on Rates Sheet)​
Fee Percentage 1​
Fee Percentage 2​
Product Value​
Product Value Based on Fee Percentage 1​
Variable Calculation Logic
2​
Orange​
15%​
5%​
$3000​
=D2*B2​
If Cell A2 is Product Category A then D2*C2 , if A2 is Product Category B then D2+E2*C2.
3​
Apple​
18%​
3%​
$2000​
=D3*B3​
"​
4​
Cherries​
33%​
3%​
$1000​
=D4*B4​
"​
5​
Banana​
18%​
2%​
$3000​
-D5*B5​
"​
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Excel Formula:
=IF(VLOOKUP(A2,Rates!A:D, 4, FALSE)="A", D2*C2, IF(VLOOKUP(A2,Rates!A:D, 4, FALSE)="B", D2+D2*C2*B2, 0))
 
Upvote 0
Try this ....

Define a name range from your first table. Let's call it RatesTable (Range:'Sheet1'!A2:E6)

For column E in Sheet 2 enter the following Function:
=VLOOKUP(A2,RateTable,2)*D4

GetLastRow.xlsm
ABCD
7ProductFee Percentage 1Fee Percentage 2Product Category
8Orange15%5%A
9Apple18%3%B
10Cherries33%3%B
11Banana18%2%B
Sheet5


You should be able to delete Columns B and C from Sheet2 since these percentages are defined in Sheet1

GetLastRow.xlsm
ABCD
22Product Type (Data Validation List Based on Rates Sheet)Product ValueProduct Value Based on Fee Percentage 1Variable Calculation Logic
23Orange$3,000$450$3,009
24Apple$2,000$360$2,011
25Cherries$1,000$330$1,010
26Banana$3,000$540$3,016
Sheet4
Cell Formulas
RangeFormula
C23:C26C23=VLOOKUP(A23,RateTable,2,FALSE)*B23
D23:D26D23=IF(VLOOKUP(A23,RateTable,4)="A",VLOOKUP(A23,RateTable,3)*B23,B23+VLOOKUP(A23,RateTable,3)*C23)
Named Ranges
NameRefers ToCells
RateTable=Sheet5!$A$7:$D$11C23:D26
Cells with Data Validation
CellAllowCriteria
A23:A26List=Sheet5!$A$8:$A$11
 
Upvote 0
I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Best I can understand is that this would be another option.

SeveralTradesLater.xlsm
ABCD
1ProductFee Percentage 1Fee Percentage 2Product Category
2Orange15%5%A
3Apple18%3%B
4Cherries33%3%B
5Banana18%2%B
Rates


SeveralTradesLater.xlsm
ABCDEF
1Product TypeFee Percentage 1Fee Percentage 2Product ValueProduct Value Based on Fee Percentage 1Variable Calc
2Orange15%5%$3,000$450150
3Apple18%3%$2,000$3602010.8
4Cherries33%3%$1,000$3301009.9
5Banana18%2%$3,000$5403010.8
Product Order Form
Cell Formulas
RangeFormula
E2:E5E2=D2*B2
F2:F5F2=CHOOSE(FIND(VLOOKUP(A2,Rates!A$2:D$5,4,0),"AB"),D2*C2,D2+E2*C2)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top