Hi,
I have below table that calculations depends on the country, if it's US or Canada, then there will be specific formula but if not, then other formula. I need a formula that I can use to replace below manual formulas that I have to update every time based on the country field. Any suggestions?
I have below table that calculations depends on the country, if it's US or Canada, then there will be specific formula but if not, then other formula. I need a formula that I can use to replace below manual formulas that I have to update every time based on the country field. Any suggestions?
Different formula US&CA.xlsx | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
2 | Country | Amount | Calc | US and Canada is a differnet formula, rest of the countries is the same | Employee Country | Country Code | Currency Code | Company | %1 | %2 | Field1 | Field2 | Field3 | Field4 | Field5 | Field6 | Field7 | Field8 | ||||
3 | United States of America | 319,255.90 | 14,488.14 | =IF(B3>$M$3,((B3*$K$3)+($M$3*$L$3)),(B3*($K$3+$L$3))) | United States of America | USA | USD | 1 | 1.683% | 6.200% | 147,000 | 18,243 | 18,243 | - | - | 6,500 | - | - | ||||
4 | United States of America | 257,600.43 | 13,450.27 | =IF(B4>$M$3,((B4*$K$3)+($M$3*$L$3)),(B4*($K$3+$L$3))) | United States of America | USA | USD | 2 | 1.683% | 6.200% | 147,000 | 18,243 | 18,243 | - | - | 6,500 | - | - | ||||
5 | Canada | 210,000.73 | 12,649.01 | =IF(B5>$M$3,((B5*$K$3)+($M$3*$L$3)),(B5*($K$3+$L$3))) | Canada | CAN | CAD | 3 | 1.950% | 7.662% | 61,900 | 6,351 | 4,979 | 3,731 | 2,925 | - | 4,035 | 4,035 | ||||
6 | Chile | $60,556.61 | - | =B6*VLOOKUP(A6,$G$3:$T$11,5,FALSE) | Chile | CHL | CLP | 4 | - | - | - | ######## | 5,065 | ######## | 2,401 | - | 788 | 788 | ||||
7 | Guatemala | $238,608.29 | 25,841.83 | =B7*VLOOKUP(A7,$G$3:$T$11,5,FALSE) | Guatemala | GTM | GTQ | 5 | 10.830% | - | - | 50,952 | 6,589 | 47,953 | 6,201 | - | - | - | ||||
8 | Argentina | 93674.99 | 22,530.54 | =B8*VLOOKUP(A8,$G$3:$T$11,5,FALSE) | Argentina | ARG | ARS | 155 | 24.052% | - | - | 142,524 | 1,449 | (47,368) | (482) | - | - | - | ||||
9 | Belgium | $259,826.39 | 42,480.56 | =B9*VLOOKUP(A9,$G$3:$T$11,5,FALSE) | Belgium | BEL | EUR | 7 | 16.350% | - | - | 29,420 | 34,530 | 1,219 | 1,431 | - | - | - | ||||
10 | Denmark | $172,849.79 | 2,770.75 | =B10*VLOOKUP(A10,$G$3:$T$11,5,FALSE) | Denmark | DNK | DKK | 8 | 1.603% | - | - | 4,149 | 655 | (10,850) | (1,713) | - | 20,049 | 20,049 | ||||
11 | Switzerland | $264,985.76 | 18,044.61 | =B11*VLOOKUP(A11,$G$3:$T$11,5,FALSE) | Switzerland | CHE | CHF | 9 | 6.810% | - | - | 8,152 | 8,754 | (4,958) | (5,324) | - | 36,948 | 36,948 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3:C5 | C3 | =IF(B3>$M$3,((B3*$K$3)+($M$3*$L$3)),(B3*($K$3+$L$3))) |
D3:D11 | D3 | =FORMULATEXT(C3) |
C6:C11 | C6 | =B6*VLOOKUP(A6,$G$3:$T$11,5,FALSE) |