Formula to change percentage charged depending on transaction amount

**ManuelP** · 07-24-2017, 05:33 AM

Hi.

I am trying to get my formula to work with different amounts and different percentages.
The formula I am currently using is as follows:

=IF(ISBLANK(G4),"",IF(G4="C",(F4*$I$12+$J$12),IF(G4="Q",(F4*$I$13+$J$13),IF(G4="T",(F4*$I$14+$J$14),""))))

The Formula is used in H4 & H5. Numbers in brackets are just the line numbers, the letters (f,g,h,d,e,i,j) represent the columns being used.

F G H

Amount code Charge
(4) R100,000.00 c R1,607.00
(5) R10,000.00 c R167.00

Explanation Code %Rate under R10'000 %Rate over R10'000 Flat Rate

D E I J

(12) C= Cash C 1.9500% 1.6000% R 7.00
(13) Q=Cheque Q 0.0000% 0.0000% R 30.00
(14) T=Transfer T 0.0000% 0.0000% R -

The idea is to use E12 and J12 together for all cash transactions under 10'000, and to use I12 and J12 together for all cash transactions over 10'000.

The cheque and Transfer transactions only use J13 and J14 respectively.

I can get the formula to work with E12 & J12 or I12 &J12 but not to differentiate between when to use E12 or I12.

Please help.

Thank you in advance.

**AliGW** · 07-24-2017, 05:37 AM

Welcome to the forum!

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.