# Combine Multiple IF and TEXT statements

##### Active Member
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.

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.

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).

Replies
0
Views
270
Replies
0
Views
355
Replies
1
Views
300
Replies
1
Views
297
Replies
13
Views
771

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.

### Which adblocker are you using?

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

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