IF Function


I'm super stumped! I'm still pretty new at Excel and am having a problem with writing out this formula correctly and I have spent many hours and still can't come up with the correct calculations for the formula. EX: . Gwen offers a $5.00 discount to repeat students. Apply this discount as appropriate to the student data as follows:
In cell H4, create a formula using the IF function and structured references to determine the correct amount paid based on the following criteria:
a. If the Repeat? value is “Yes”, calculate the amount paid by subtracting 5 from the Fee.
b. Otherwise, the amount paid is the Fee value.

14 Replies

That's like

= <Fee> - 5*(<Repeat?> = "Yes")
If you prefer the IF version of the Boolean formula, it is this:
=IF(<Repeat?> = “Yes”,
<Fee> - 5,


= <Fee> - IF(<Repeat?> = "Yes",5)
You make me smile with your creative use of the IF, Sergei!
Can someone explain this question to me and also show me the steps to getting to the answer?


Let say you have data as this


Amount Paid is calculated as Fee minus $5.00 if Repeat? = Yes, otherwise Fee.

=(B2="Yes") returns TRUE or FALSE which is equivalent of 1 or 0 in calculations. Thus you multiply 5 on 1 or 0 depends on value in column B, and deduct this result (5 or zero) from column A value.

Could you show me step by step how to get the amount paid in dollar amounts because when I worked out the problem the way you showed me I kept getting true and false answers?


Please open the file I attached to the previous post. Stay on any cell with the formula. On ribbon in Formulas section find Formula Evaluate. click on it and Evaluate. 

Other explanations also were in previous post.


If something still doesn't work please attach sample file.

@Sergei Baklan 


Thank you for your explanation and all of your help. After seeing your explanation I realized where I went wrong. I was putting an equal sign and IF in front of the first cell I needed to use and I did not know I needed to take the fee cell subtract it by 5 and multiply it times the repeat cell and put yes at the end.


Great to know you sorted this out, thank you for the update.

@PrincessDiva1923 Can you show the exact formula on how to do it on Excel because I'm stuck on this one too.

@Sergei Baklan I tried to use your formulas from above, but none of them seemed to work.


That only means you applied them in a wrong way since formulas work on a sample file

@cathysolaka Did you ever get the answer to this?   I tried all his formulas and they don’t work