Combine Multiple IF and TEXT statements

[isadoko]

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.

 

[Joe4]

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.

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

 

[isadoko]

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.

 

[Joe4]

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