Creating a formula using IF

[poochbeast]

Hi. I'm new to Excel and I could really use some help. Here's what I'm trying to achieve: I use 2 merchant processors for my business: GroovePay and PayPal. When I make a sale, depending on the processor, I am charged a different rate. So, I want a cell to return a certain value based upon:

IF the processor is GroovePay, then Sale Amount (D2 for example) x 2.85% + 0.25 + 1 = result.
IF the processor is PayPal, then Sale Amount (D2) x 2.9% + 0.30 = result.

How do I setup this formula?

Thanks in advance!

[#2]

@poochbeast 

Let's say the sale amount is in D2 and the merchant processor in E2.

In another cell in row 2, e.g. F2:

=IF(OR(D2="",E2=""),"",IF(E2="GroovePay",D2*2.85%+1.25,D2*2.9%+0.3))

This can be filled down if required.

[#3]

Perfect! Hans, you are my hero! Your formula works exact as is. It also helped me to discover my error. I was trying to add the name "PayPal" to the calculation, but since PayPal (FALSE) is not GroovePay (TRUE), I realize now that I don't need to actually add this name. Thank you again. Your assistance is most appreciated!

[#4]

@poochbeast I'm going to be presumptuous and suggest an improvement on the formula offered by my friend, @Hans Vogelaar .

I try to avoid what's called hard-coding of variables into formulas. It's easy to forget that those variables are "hidden" away in the formula, and makes for scrambling when rates change. So in a case like this, I'd suggest creating a table such as the one shown here.

And then use a formula that references the table, retrieving the values from the table. Then, if they change in the future, the formula remains unchanged. This also has the advantage of allowing you to add new processors (e.g., ApplePay) and whatever their rates are and still keep the same formula.

Here's the basic formula (I've attached the working example).

=(D3*VLOOKUP(E3,Table1,2,0))+VLOOKUP(E3,Table1,3,0)+VLOOKUP(E3,Table1,4)

In the spreadsheet, I've nested it within an IFERROR function so as to produce blanks where there are no values in D3 and E3, in the extra rows.

=IFERROR((D3*VLOOKUP(E3,Table1,2,0))+VLOOKUP(E3,Table1,3,0)+VLOOKUP(E3,Table1,4),"")

[#5]

@mathetes, this is a fascinating approach. I wouldn't have thought of it, but I can certainly see its value and the flexibility offered by your alternative solution. I've previously used something similar in MS Access (accessing another table), but never in Excel. I'll have to give it a try. Thank you!