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

English
,
#1

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!

#2

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

#3

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

#4

@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)
#5

@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 …

#6

my head was on vacation

:slight_smile:

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

#7

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

Please, click edit below your question to add more information. Thanks.

#8

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

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

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

#9

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)

#10

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.

Then delete your answer, because it doesn’t solve your question.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.