Referencing a formula with a dropdown menu

Sir this is a google sheet problem and it has been headaches for me... I have tried using INDEX & MATCH and VLookUp function to solve this but i keep missing something i can seem to figure out

Ok here is some context, let's assume i inserted 30000 into the amount column and selected the "Withdraw" dropdown menu, this dropdown will reference the content stored in the cell Withdraw-Rate intersection (Transaction Rate table) which is

=IFS(amount=0,"",
amount<5001,100,
amount<10001,200,
amount<20001,300,
amount<30001,400,
amount<40001,500,
amount<50001,600,
amount<60001,700,
amount<80001,800,
amount<100001,900,
amount<120001,1000,
amount<150001,1100)

Now because the amount = 30000, the Charges column (in the Transaction List table) will display "300" which falls within the range based on the stored formula.

Then the Profit (transaction list table) will output "300" because if the formula

=IFS(amount<20000,charge-(amount*0.55%),amount>19999,charge-100)

stored in the "Withdraw-ProFormula Intersection (in the Transaction Rate table)"

Alright please do i hope i am able to get help here.

Try putting the following formula in cell E9 and see if this is what you're after.

Formula:

Please Login or Register  to view this content.

I've amended the formula in E9 to clean things up a bit.

Formula:

Please Login or Register  to view this content.

I think I understand now. In your original link I put an ArrayFormula in cell D9 and E9 which will calculate the Charges and Profit for all rows. I also replaced the Transaction Rate table with a Charge table on the Charges tab. Hopefully this is what you're after.

Great work sir, you're extremely close to exactly what i am trying to achieve. Your recent update is an eye opener for me and i have already started watching tutorials on the XLookUp Function.


=ArrayFormula(if(B9:B="","",IF(C9:C="Withdraw",XLOOKUP(B9:B,Charges!A3:A14,Charges!B3:B14,,1),IF(C9:C="Transfer",XLOOKUP(B9:B,Charges!A3:A14,Charges!B3:B14,,1),IF(C9:C="Airtime",1.5%,3.5%)))))

Noticed from the above formula, you combined Airtime & Data together but i will like to separate them in other to make this calculations for determining the profit.

The formular
=ArrayFormula(IFERROR(if(B9:B>0,IFS(amount<20000,charge-(amount*0.55%),amount>19999,charge-100),""),""))
which we currently have in the "PROFIT" column is exclusively used for determining the calculation for the "Withdraw" option

So to calculate the profit for:

"Transfer" option, we will use: =IFS(amount>0,charge-30,amount=0,"")

"Deposit "option, we will use: =charge (this will just echo the value in the charge column as profit)

"Airtime" option, we will use: =amount*1.5%

"Data" option, we will use: =amount*3.5%

I hope you get what i am driving at sir, each selected option will have it's own specific formula for determining the profit.

I've gone back to the previous solution which I hope you can work with. Part of the issue was that you changed some of the charge rates as we went along. In any case, with the details that you've provided, this should work for you. Good luck.

Thanks for the help so far sir,

I will like to do one more thing here. I have created a sub table beside the "POSAcc" table.

The goal here is to determine the total sum generated by each options selected in the "Bill Type" column.

I tried illustrating what i want to achieve in the excel.

Thanks...

See the new formula in H9.

Thanks sir,

It worked like a charm... Thanks