IF Function

%3CLINGO-SUB%20id%3D%22lingo-sub-361320%22%20slang%3D%22en-US%22%3EIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-361320%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20super%20stumped!%20I'm%20still%20pretty%20new%20at%20Excel%20and%20am%20having%20a%20problem%20with%20writing%20out%20this%20formula%20correctly%20and%20I%20have%20spent%20many%20hours%20and%20still%20can't%20come%20up%20with%20the%20correct%20calculations%20for%20the%20formula.%20EX%3A%20.%20Gwen%20offers%20a%20%245.00%20discount%20to%20repeat%20students.%20Apply%20this%20discount%20as%20appropriate%20to%20the%20student%20data%20as%20follows%3A%3CBR%20%2F%3EIn%20cell%20H4%2C%20create%20a%20formula%20using%20the%20IF%20function%20and%20structured%20references%20to%20determine%20the%20correct%20amount%20paid%20based%20on%20the%20following%20criteria%3A%3CBR%20%2F%3Ea.%20If%20the%20Repeat%3F%20value%20is%20%E2%80%9CYes%E2%80%9D%2C%20calculate%20the%20amount%20paid%20by%20subtracting%205%20from%20the%20Fee.%3CBR%20%2F%3Eb.%20Otherwise%2C%20the%20amount%20paid%20is%20the%20Fee%20value.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-361320%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362744%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362744%22%20slang%3D%22en-US%22%3EYou%20make%20me%20smile%20with%20your%20creative%20use%20of%20the%20IF%2C%20Sergei!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362737%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362737%22%20slang%3D%22en-US%22%3E%3CP%3EOr%3C%2FP%3E%0A%3CPRE%3E%3D%20%26lt%3BFee%26gt%3B%20-%20IF(%26lt%3BRepeat%3F%26gt%3B%20%3D%20%22Yes%22%2C5)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362256%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362256%22%20slang%3D%22en-US%22%3EIf%20you%20prefer%20the%20IF%20version%20of%20the%20Boolean%20formula%2C%20it%20is%20this%3A%3CBR%20%2F%3E%3DIF(%3CREPEAT%3E%20%3D%20%E2%80%9CYes%E2%80%9D%2C%3CBR%20%2F%3E%3CFEE%3E%20-%205%2C%3CBR%20%2F%3E%3CFEE%3E)%3C%2FFEE%3E%3CLINGO-SUB%20id%3D%22lingo-sub-361365%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-361365%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20like%3C%2FP%3E%0A%3CPRE%3E%3D%20%26lt%3BFee%26gt%3B%20-%205*(%26lt%3BRepeat%3F%26gt%3B%20%3D%20%22Yes%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3C%2FFEE%3E%3C%2FREPEAT%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1245608%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1245608%22%20slang%3D%22en-US%22%3ECan%20someone%20explain%20this%20question%20to%20me%20and%20also%20show%20me%20the%20steps%20to%20getting%20to%20the%20answer%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1245950%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1245950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590455%22%20target%3D%22_blank%22%3E%40PrincessDiva1923%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20say%20you%20have%20data%20as%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20362px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178810iBF8EBCDB27060B4D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAmount%20Paid%20is%20calculated%20as%20Fee%20minus%20%245.00%20if%20Repeat%3F%20%3D%20Yes%2C%20otherwise%20Fee.%3C%2FP%3E%0A%3CP%3E%3D(B2%3D%22Yes%22)%20returns%20TRUE%20or%20FALSE%20which%20is%20equivalent%20of%201%20or%200%20in%20calculations.%20Thus%20you%20multiply%205%20on%201%20or%200%20depends%20on%20value%20in%20column%20B%2C%20and%20deduct%20this%20result%20(5%20or%20zero)%20from%20column%20A%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262966%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262966%22%20slang%3D%22en-US%22%3ECould%20you%20show%20me%20step%20by%20step%20how%20to%20get%20the%20amount%20paid%20in%20dollar%20amounts%20because%20when%20I%20worked%20out%20the%20problem%20the%20way%20you%20showed%20me%20I%20kept%20getting%20true%20and%20false%20answers%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1263546%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1263546%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600114%22%20target%3D%22_blank%22%3E%40Princess1923%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20open%20the%20file%20I%20attached%20to%20the%20previous%20post.%20Stay%20on%20any%20cell%20with%20the%20formula.%20On%20ribbon%20in%20Formulas%20section%20find%20Formula%20Evaluate.%20click%20on%20it%20and%20Evaluate.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOther%20explanations%20also%20were%20in%20previous%20post.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20something%20still%20doesn't%20work%20please%20attach%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1265611%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1265611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20explanation%20and%20all%20of%20your%20help.%20After%20seeing%20your%20explanation%20I%20realized%20where%20I%20went%20wrong.%20I%20was%20putting%20an%20equal%20sign%20and%20IF%20in%20front%20of%20the%20first%20cell%20I%20needed%20to%20use%20and%20I%20did%20not%20know%20I%20needed%20to%20take%20the%20fee%20cell%20subtract%20it%20by%205%20and%20multiply%20it%20times%20the%20repeat%20cell%20and%20put%20yes%20at%20the%20end.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1265808%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1265808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590455%22%20target%3D%22_blank%22%3E%40PrincessDiva1923%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGreat%20to%20know%20you%20sorted%20this%20out%2C%20thank%20you%20for%20the%20update.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1266331%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1266331%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F590455%22%20target%3D%22_blank%22%3E%40PrincessDiva1923%3C%2FA%3E%26nbsp%3BCan%20you%20show%20the%20exact%20formula%20on%20how%20to%20do%20it%20on%20Excel%20because%20I'm%20stuck%20on%20this%20one%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1266341%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1266341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20tried%20to%20use%20your%20formulas%20from%20above%2C%20but%20none%20of%20them%20seemed%20to%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279627%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F601871%22%20target%3D%22_blank%22%3E%40cathysolaka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20only%20means%20you%20applied%20them%20in%20a%20wrong%20way%20since%20formulas%20work%20on%20a%20sample%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

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.

13 Replies
Highlighted

That's like

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

Or

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

@PrincessDiva1923 

Let say you have data as this

image.png

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.

Highlighted
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?
Highlighted

@Princess1923 

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.

Highlighted

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

Highlighted

@PrincessDiva1923 

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

Highlighted

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

Highlighted

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

Highlighted

@cathysolaka 

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