Combine Multiple IF and TEXT statements

isadoko

Active Member
Joined
Jan 10, 2005
Messages
322
Office Version
  1. 365
Platform
  1. Windows
I have several thousand records. Each records contains a payment type, eg, cash, check, credit card, etc. Each type of payment carries a fee, cash=$0.20, check $0.28, credit card=1.87%, debit card=1.87%.

I have created a look for the fees. Now my problem is either calculating the fee for each payment because some are cents and others are percentages. I need a formula to accommodate both. I wrote this formula for cash, =TEXT(IF(H2="cash",(J2/100)*L2),"$0.00"). H2 is my payment type, J2 is my amount divided by 100 and multiplies against the fee. It works.

The rub is how to include the fees calculated as a percentage and cents in one formula for all payment types. I'd appreciate any help to figure this one out. Thanks.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I would recommend creating a lookup table, with three columns:
1. Payment Type
2. Fee Type (cash/percent)
3. Fee Amount

Then, for each calculation, you can do an IF statement with some VLOOKUPs, looking up the Fee Type and Fee Amount based on the Payment Type, and incorporate that into your calculation, i.e.
Code:
=IF(VLOOKUP(...)="percent",Percent Calculation,Cash Calculation)
Within the "calculations, you can use another VLOOKUP to return the Fee Amount to use in the Calculation.
 
Upvote 0
Thank you Joe4. I settled on this =IF(H2="credit card",(J2/100)*L2,IF(H2="cash",(J2*L2),IF(H2="EFT",(I2*L2),IF(H2="Check",(I2*L2),"")))). Not fancy but does the trick.
 
Upvote 0
If that works for you, great.

You can see though how if you have a few more possibilities, that the formula can get quite unruly pretty fast. Also, you can only nest up to seven levels of IF statements.

If you have more to add at some point down the road, you may want to consider using a VLOOKUP solution (or even a VBA/UDF solution).
 
Upvote 0

Forum statistics

Threads
1,196,073
Messages
6,013,275
Members
441,759
Latest member
ab_

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