# Need assistance with an "IF" formula/function, please?!

I’m trying to create a rewards’ spreadsheet for customer purchases. If they pay cash, they will get credit for the amount (does not include tax); if paying via credit/debit card, a fee is deducted from purchase price (2%, for now). I’m wanting an “IF” formula for entering whether cash or cc/debit?

AMT PURCHASED TAX TOTAL PD W/CASH PD W/CC/DEBIT PAY CASH PAY CC/DEBIT

\$40.00       \$2.60     \$42.60  YES/NO   YES/NO   \$40.00   \$39.20

I have formulas for cash/cc/debit and have tried numerous “IF” formulas, but nothing is working for me. I just want to be able to enter whether cash or cc/debit and the amount pops up. I will be doing running totals, so I only want 1 payment to show.

(Customer will pay \$42.60, but, in order to receive punches, the running total will not include tax, nor the 2% credit card fee.)

Any help is certainly appreciated!

=IF(W/CASH reference="YES";TOTAL reference*.98;TOTAL reference)

…or without IF() =((W/CASH reference="YES")*0.2+0.98)*TOTAL reference (is written a little shorter, but the result is the same)

@JohnSUN, I still don’t fully understand your formula, but it looks interesting. Changed = by <> and 0.2 by 0.02.

Maybe =((W/CASH reference<>"YES")*0.02+0.98)*TOTAL reference

• =((D10<>"YES")*0.02+0.98)*E9 Not shorter, but may be more efficient.
• =IF(D10="YES";E9*0.98;E9)

@LeroyG Just a typo, an annoying typo - you’re right, one zero is missing and ="NO". And about the length of the formula, you are also right - the option without IF () will be a little shorter only if we refer to data on another sheet (if the sheet name is also included in the reference). I guess my head was on vacation when I wrote the comment …

@JohnSUN, I just understood your formula. It’s great the idea of TRUE|FALSE*0.02!

@getti, I just put your data in a spreadsheet, and the issue is not clear for me.

If the customer pays in cash, they pay \$ 40; but if with credit/debit card, \$ 39.20? Your question seems to say the opposite.

[EDIT] You can combine writing paper or leaving empty for plastic in a column, then use =IF(D2="paper";A2;A2*0.98). Replace in the formula the word paper for another do you think would be the best for your practice (I would use 1).

See sample file modified. [edit end]

Assuming that column A and row 2 are the first with data, and if the customer pays in cash they get -2% on the amount purchased without taxes, you can use the next formula:

=IF(D2="YES";A2*0.98;A2), or (adapting @JohnSUN idea) =A2*(1-0.02*(D2="YES"))

See sample file.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.

Check the mark () to the left of the answer that solves your question.

You are amazing! First time I’ve been on this site - didn’t think I’d get a response, so THANK YOU!!! I need a little more time to study your responses, but, based on your info, this is what I have and it should work. If you have something that would simplify, please let me know. Such as, instead of having 2 cells (cash & cc/debit), could combine into one & just indicate which payment and the amount pops up?

AMT PURCHASED TAX TOTAL PD W/CASH PD W/CC/DEBIT PAY CASH PAY CC/DEBIT
\$40.00 \$2.60 \$42.60 YES \$0.00 \$40 \$0.00
=IF(D2=“YES”,A2, 0)

AMT PURCHASED TAX TOTAL PD W/CASH PD W/CC/DEBIT PAY CASH PAY CC/DEBIT
\$40.00 \$2.60 \$42.60 \$0.00 YES \$0.00 \$39.20
=IF(E2=“YES”,A2-(A2*0.02),0)

Thanks for the new information. Since it doesn’t answer the question, please, edit your question, and paste it there.

Or re-post it as a comment. To do that, hover on “more” (just above these comments), and press “repost as comment under older answer” / “repost as comment under question”. Thanks again.