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