Forum Discussion
Creating a formula using IF
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.
poochbeast I'm going to be presumptuous and suggest an improvement on the formula offered by my friend, HansVogelaar .
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),"")
- 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!
