Creating a formula using IF

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

excel

mathetes

Gold Contributor

Sep 22, 2022

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),"")

ProcessorsViaTable.xlsx13 KB

poochbeast

Copper Contributor

Sep 22, 2022

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!

Forum Discussion

Creating a formula using IF

Resources