# return a formula based on cell value?



## miningguy (Jan 5, 2023)

Hi, I'm a novice user and I'm not sure how to approach this. I believe I could use nested IF statements but this would be cumbersome to add more ranges. I would like to enter a column of "PRICE" and return a "FEE" calculation in the adjacent column based on the range. 


PRICEMinMaxFEE formula$0 - $100001000$70$1001 - $20,000100120000$70 + (PRICE-1000)*0.021$20,001 - $200,00020001200000$469 + (PRICE-20000)*0.014$200,001+200001$2,989 + (PRICE - 200000)*0.012


----------



## jdellasala (Jan 5, 2023)

Classic lookup problem.
Book1ABCDEF1PRICEFEEAmountFeeExtra20079,794.0080,911.120.01431,0010.021133.001,250.120420,0010.014184,620.00185,737.120.0145200,0010.012764,250.00765,367.120.012647,971.0049,088.120.0147106,386.00107,503.120.0148220,161.00221,278.120.01291,624.002,741.120.02110104.001,221.1201180,421.0081,538.120.014Sheet1Cell FormulasRangeFormulaE2:E11E2=ROUND(D2:D11+(D2*XLOOKUP(D2,A2:A5,B2:B5,,-1)),2)F2:F11F2=XLOOKUP(D2:D11,A2:A5,B2:B5,,-1)Dynamic array formulas.
I included the Extra column for verification. It contains the same XLOOKUP formula as column E.


----------



## miningguy (Jan 5, 2023)

Thank you, this looks like a great function. I'm curious that if I leave a cell in D column blank the lookup returns the last value in the array. Should I use a ISBLANK to correct this or is there a neater way in the Xlookup function?


----------



## miningguy (Jan 5, 2023)

Also - the XLOOKUP formula is raising another issue, it is not a simple multiplier to calculate the fee. It must use the entered value to subtract a figure in the lookup range. It's this part of the logic I'm struggling with.


----------



## jdellasala (Jan 5, 2023)

miningguy said:


> Also - the XLOOKUP formula is raising another issue, it is not a simple multiplier to calculate the fee. It must use the entered value to subtract a figure in the lookup range. It's this part of the logic I'm struggling with.


Sorry I missed that. Here you go:
Book1ABCDEF1AmountFee %Discount FeeAmountFee2007079,794.001,586.1231,0010.02170133.0070.00420,0010.014469184,620.003,053.685200,0010.0122,989764,250.0012,160.00647,971.001,140.597106,386.001,958.408220,161.005,630.9391,624.00104.1010104.0070.001180,421.001,594.89Sheet1Cell FormulasRangeFormulaF2:F11F2=XLOOKUP(E2:E11,A2:A5,C2:C5,,-1)+ROUND(E2:E11*XLOOKUP(E2:E11,A2:A5,B2:B5,,-1),2)Dynamic array formulas.The first XLOOKUP returns the fixed amount of the fee, the second XLOOKUP returns the Amount times the appropriate amount to multiply the amount by.
The advantage of using full array formulas is that you don't have to lock the lookup array ranges, and only one place to make adjustments.


----------



## miningguy (Jan 5, 2023)

thank you that is very helpful !


----------



## Dave Patton (Jan 5, 2023)

Can your provide the expected result for a few numbers.
You may require a Tiered formula. There are many results of such solutions with SumProduct.
Excel 365's Sum can also handle such calculations.
With the formula in C2, I named the bracket information aB and the rate differentials aR. Review cell B2 for the details.
If you use the information as above, you will not need the table.

Consider the following
Commission2022.xlsmABCDEF12200,0002,989.002,989.00PRICEFEEFee Differential3004ExamplesFees1,0002.10%2.10%5500.0070.0020,0001.40%-0.70%615,000.00364.00200,0001.20%-0.20%725,000.00539.008200,000.002,989.009205,000.003,049.001080,421.001,314.8911aCell FormulasRangeFormulaA2A2=200000B2B2=(A2>0)*(70+SUMPRODUCT(--(A2>D3:D6),A2-D3:D6,F3:F6))C2C2=(A2>0)*(70+SUM((A2>aB)*(A2-aB)*aR))F4:F6F4=E4-E3B5:B10B5=(A5>0)*(70+SUM((A5>aB)*(A5-aB)*aR))


----------



## jdellasala (Jan 6, 2023)

Dave Patton said:


> Can your provide the expected result for a few numbers.
> You may require a Tiered formula. There are many results of such solutions with SumProduct.
> Excel 365's Sum can also handle such calculations.
> With the formula in C2, I named the bracket information aB and the rate differentials aR. Review cell B2 for the details.
> ...


Just add them to the Amounts column, and expand the XLOOKUP *lookup_value* range (currently E2:E1) accordingly.


----------



## Dave Patton (Jan 6, 2023)

I do not know if Sumproduct was always part of Excel or not; it shows in forum posts dated 2002.
Yes, the results can be achieved with multiple VLookups or XLookups or by basic arithmetic.
There are many ways to structure a solution.

We can secure the bracket and rate differential information by reading the specifications.
or
If we have the table and if we constructed the formula with reference to the table (see B2) in post 7, we can secure the bracket and rate information by highlighting the ranges such as D3:D6, pressing F9, and copying the array information {0;1000;20000;200000}.
To name the information, go to Formulas Name Manager and name the bracket array information say aB value= {0;1000;20000;200000} and aR for rate differentials {0;0.021;-0.007;-0.002}.

With this post, I used Excel 365's Let and I defined the array information within the formula.

Commission2022.xlsmAB121,000.0070.001320,000.00469.0014200,000.002,989.00151,000,000.0012,589.0011aCell FormulasRangeFormulaB12:B15B12=LET(b,{0;1000;20000;200000},r,{0;0.021;-0.007;-0.002},(A12>0)*(70+SUM((A12>b)*(A12-b)*r)))


----------



## Dave Patton (Jan 6, 2023)

Commission Tax Fee.xlsmAB1PriceFee_x2250,000.003,589.003800.0070.00425,000.00539.005100,000.001,589.0061,000,000.0012,589.0071aCell FormulasRangeFormulaB2:B6B2=Fee_x(A2)

To advance one step further, I used the new Lambda function.
I named the Formula Fee_x and the value Price and the formula.
This can be used anywhere in the workbook.
Type all or part of =Fee_x and Fee_x(Price) will show. Enter the formula as shown.


----------



## miningguy (Jan 5, 2023)

Hi, I'm a novice user and I'm not sure how to approach this. I believe I could use nested IF statements but this would be cumbersome to add more ranges. I would like to enter a column of "PRICE" and return a "FEE" calculation in the adjacent column based on the range. 


PRICEMinMaxFEE formula$0 - $100001000$70$1001 - $20,000100120000$70 + (PRICE-1000)*0.021$20,001 - $200,00020001200000$469 + (PRICE-20000)*0.014$200,001+200001$2,989 + (PRICE - 200000)*0.012


----------



## miningguy (Jan 6, 2023)

Dave Patton said:


> I do not know if Sumproduct was always part of Excel or not; it shows in forum posts dated 2002.
> Yes, the results can be achieved with multiple VLookups or XLookups or by basic arithmetic.
> There are many ways to structure a solution.
> 
> ...


thank you, another correct solution.


----------



## Dave Patton (Jan 6, 2023)

Hello MiningGuy

I asked previously for the following
       Can your provide the expected result for a few numbers?

N.B. I thought that you required a typical tiered formula.
        There are many solutions on this forum with Sum, SumProduct, regular arithmetic, and others.

The best solution is often the one that you have reviewed, tested, and provides the correct result.

If you have any questions on the suggestions or if you just want arithmetic, please advise.


----------



## Dave Patton (Monday at 2:09 PM)

Miningguy
Please show the edits that you made or the formula that you used.
The Xlookup solution that you marked as the answer may be incomplete or require edits.

Arithmetic or one of the versions that I posted should yield the correct answer.

Commission Tax Fee.xlsmABCDEFG1AmountFee %Accumulated1,000,000.00AmountFeeFee200.0%70.0070.0079,794.001,586.121,306.1231,000.002.1%70.00469.00133.0070.0070.00420,000.001.4%469.002,989.00184,620.003,053.682,773.685200,000.001.2%2,989.0012,589.00764,250.0012,160.009,760.0061E+30847,971.001,140.59860.597106,386.001,958.401,678.408220,161.005,630.933,230.9391,624.00104.1083.1010104.0070.0070.001180,421.001,594.891,314.89121,000,000.0014,989.0012,589.0013200,000.005,389.002,989.001420,000.00749.00469.001515,000.00385.00364.001648,880.7137,558.721cCell FormulasRangeFormulaF2:F15F2=XLOOKUP(E2:E15,A2:A5,C2:C5,,-1)+ROUND(E2:E15*XLOOKUP(E2:E15,A2:A5,B2:B5,,-1),2)D2D2=MIN(A3-A2,$D$1-A2)*B2+$C$2D3D3=MIN(A4-A3,$D$1-A3)*B3+D2D4:D5D4=MIN(A5-A4,MAX(0,$D$1-A4))*B4+D3G2:G15G2=Fee_x(E2)F16:G16F16=SUM(F2:F15)Dynamic array formulas.


----------

