Excel Magic Trick 167p1 IF function formula: 12 Examples!

[llnicholsii]

Excellent video!!! Is there a way to have two IF logical functions with with two different true responses and two different false responses?

E.g. IF(AND(A1=B1,"Ok",IF(A1<0,"-1"­,IF(B1<0,"+1,"Bad")))

[ExcelIsFun]

@llnicholsii , yes, post questions to THE best Excel question site:

mrexcel [dot] com/forum

[jonjon72886]

How do I write a command which will say the following. If a cell is greater than zero move down one row, if not delete row.

[ExcelIsFun]

You may need VBA for that. Post Q to:

mrexcel [dot] com/forum

[nikhilkhandelwal0311]

@ExcelIsFun

please give formula for this condition

[bootz2boobz]

I am still confused trying to calculate IF function.Any student worker who works 18 hours or less will have the pay applied to his or her tuition. If a student works more than 18 hrs (referenced in F7), then student will receive a paycheck for hours over 18. In cell D11, construct an IF function to determine the number of hours worked that will be applied to the student’s tuition using cell F7 as an absolute reference. Cell F6 Hourly Rate=$9.75,F7=18. Cell C11: 18,39,22,16,27,20,32,16,11,47,­54

[MariooTX]

How would you incorporate %'s into these type of formulas? For example, my professor threw this in at the end of the assignment:

Add a delivery charge to customer totals: 10% if over $200;otherwise 15%

I keep wanting to put "=IF(H2>$200,10% of H2,15% of H2)"

H2 is $81.50

Obviously, I know I cant put the word "of" and expect it to work so I'm confused about the the correct formatting.

[ExcelIsFun]

Try something like:

=IF(H2>200,1.1*H2,1.15*H2)

[MariooTX]

@ExcelIsFun Hey Thanks so much!! That worked out great!!

[leftylee527]

Yeah! It worked! You are the BEST!  Thank you, thank you, thank you!

[leftylee527]

Thank you but it isn't working. In my spreadsheet O2 = Qty Ordered and P2 = Qty Received; so my formula is =IF(AND(P2>=O2,P2<=1.1*2),100,­1). For every scenario, the supplier is scoring 1 with this formula. We need them to score 100 if they ship the exact quanty up to and including 10% over. If they ship 11% over or anything under the quantity we ordered; they score a 1. Any other suggestions of a formula to use? I would be very grateful if you could get it to work. THX!

[ExcelIsFun]

Try:

 =IF(AND(P2>=O2,P2<=1.1*O2),100­,1)

[leftylee527]

I could use your help. I'm trying to figure out the IF formula for the following scenario: IF we receive any quantity under the quantity we ordered from a supplier, the supplier scores a "1". IF we receive the exact quantity we ordered up to and including 10% over, the supplier scores a "100". If we receive 11% and over the quantity we ordered, the supplier scores a "1". How would I write up this IF formula? Thanks for any help you can provide.

[leftylee527]

I could use your help. I'm trying to figure out the IF formula for the following: IF we receive any quantity under the quantity we ordered from a supplier, the supplier scores a "1". IF we receive the exact quantity we ordered up to and including 10% over, the supplier scores a "100". If we receive 11% and over the quantity we ordered, the supplier scores a "1". How would I write up this IF formula? Thanks for any help you can provide.

[ExcelIsFun]

If Qty Ordered is in B1Qty Received is in B2, then this formula may work: =IF(AND(B2>=B1,B2<=1.1*B1),100­,1)

[leftylee527]

I need help. I'm trying to figure out the IF formula for the following:  IF we receive any quantity under the quantity we ordered from a supplier, the supplier scores a "1". IF we receive the exact quantity we ordered up to and including 10% over, the supplier scores a "100". If we receive 11% and over the quantity we ordered, the supplier scores a "1". How would I write up this IF formula? Thanks for any help you can provide.

[ExcelIsFun]

If Qty Ordered is in B1Qty Received is in B2, then this formula may work: =IF(AND(B2>=B1,B2<=1.1*B1),100­,1)

[pokwang021]

Hi! Im here again. Do you have any knowledge regarding this: i want to test a value of d3 if it is null or not, If it is, i'll leave d4 null also, meaning blank, and if it is not null, i'll use vlookup to find d3's corresponding value. Is it possible in any way? I've trialed and errored but it returns an error whenever I enter the formula :(

[ExcelIsFun]

Yes, somrthing like this will work:

=IF(D3="","",VLOOKUP....))

[yondamn]

Is it necessary to add inverted commas in `value if true or false`???

[ExcelIsFun]

I do not understand your question.

[yondamn]

@ExcelIsFun never mind. I got it. Well thanks anyway. It was a good video.

[gronkowieczlocisty]

you are amazing man! How much time have you spent on recording all that stuff?  I'm gonna study ALL your videos from the beginning to the end. Wonder how long it will take.

[ExcelIsFun]

search for 'excelisfun' then go to the channel and watch the video that auto plays - it will show the best order to wacth videos.

[ExcelIsFun]

You don't have to watch them all, just teh Excel Basics and Highline Excel Class series.

[coreHC]

Man you rock, you didn´t directly solve my problem, but you gave me the perfect idea on how to fix it, you just saved this man some long hours of work

[ExcelIsFun]

I am glad that the video helped!

[miiihaaas]

How you made red numbers in the cell when the number is under 0?

it is on 6:13

[ExcelIsFun]

Conditional Formatting. Here is a video that shows many Conditional Formatting tricks:

Highline Excel Class 21: Conditional Formatting 12 Examples

[RawhaantheFascinatin]

I swear these are the BEST VIDEOS EVER!!!

[ExcelIsFun]

I am glad that you like them!!!

[thisisouraccount]

THESE ARE THIS BEST VIDEOS I HAVE EVER SEEN

[ExcelIsFun]

I am glad that you like the video!

[MESSER8989]

How did you do the drop down functions? (8:45)

[ExcelIsFun]

Data Validation. Try this video title:

Excel Magic Trick 548: Data Validation Drop-Down List In A Cell Same Sheet or Different Sheet

[Jhardyrules9]

What if the logical test is A1=A2 but in A1 and in A2 there is 2 different formulas and u dont want every element of those formulas carried out just what the formula result is for Ex. In A1 =57-1 In A2 =56*1 In A3 =If(A1=A2,1,0) It comes out 0 but i would like it to equal 1 is there a video for this if there is not could u help me out thanks Good Videos

[Jhardyrules9]

@Jhardyrules9 Nvm I figured out what the problem was Thanks Anyway Keep the Videos Coming

[ExcelIsFun]

I do not know how to do that. But no worries. THE best Excel site for asking questions is:

mrexcel[dot]com/forum

[bichngoc2589]

very good tutorial.i find it very helpful.thank you

[ExcelIsFun]

I am glad that it helped!

[alwayzsic]

can you explain the formula on A40? I dont get it.

[ExcelIsFun]

It:

="The "&B41&" ="

is a Text Formula.

& is the join symbol. So the Word "The " is joined with the cell content in B41 and " ="

Here is a video about this:

Excel Basics #3: Formulas w Cell References

[elbouyas]

thanks for all your efforts, I truly enjoy watching your videos.

[ExcelIsFun]

You are welcome!

[Rourba]

Love Excel, life savor.

Sahit.

[ExcelIsFun]

I am glad that it helped!

[Memoi58]

I love your videos....you made excel easy to use and understand...

i have a question though, is it possible to use the if statement when you have words and not numbers as your data. For instance, if it says Yes I want it to note "1" and "2" if no

[ExcelIsFun]

If A1 contains "Yes", try:

=IF(A1="Yes",1,2)

[fonsecafh]

OH MY GOD,OH MY GOD,OH MY GODDDDDDDDDDDDDD!!!!!!!!!!!!!!­!!!!!!!!!WHAT A DIFFICULT EXCEL EXCISE...BUT I WON'T GIVE UP TO LEARN IT.THANKS.FERNANDO/BRAZIL-DF.

[ExcelIsFun]

Don't give up!!

[fonsecafh]

@ExcelIsFun /thanks,i won´t.

[mmilos11]

this dude rocks,thanks

[ExcelIsFun]

I am glad that it helped!

[DarkLightA12]

I love the automatic speaker! xD

[ExcelIsFun]

Cool!

[LuluNero]

You are a WONDERFUL TRAINER.

I am happy to have found someone who teaches with such passion. One wants to listen to your classes (and one doesnt get half asleep or lost btw). Your tricks are very detailed (which I like) and easy to follow. Thanks for doing what you do.

[ExcelIsFun]

You are welcome!

[ExcelIsFun]

You are welcome!

[SuperRobinsont]

I am preparing for Excel vendor test ........so happy to run across this priceless information. Thanks

[ExcelIsFun]

I am glad that they help!

[fantabulous008]

SWEET!!!

[ExcelIsFun]

I am glad that you like it!!

[aman1245singh]

Hello sir, regarding one of the part of this video(put 1 or 2 ranges in a cell), I have used this formula( at the end) to give me sum when A47 is sum and average when A47 is average, and it automatically selects the data1 or data2 depending on what's there in the B48 cell. would you suggest any improvement to this formula.

=IF(A47="sum",SUM(IF(B48="Data­2",B45:B46,A45:A46)),AVERAGE(I­F(B48="Data2",B45:B46,A45:A46)­))

[ExcelIsFun]

That looks good. Great work!

[munmunmonty]

@aman1245singh Try this.. it calculate- Sum, Average, Min, Max, count (numeric value only) in a particular data range and prompt you, in case you type-in incorrect formula.

Assume your command should be in A1 and you define a data range by "naming function".

=IF(A1="Sum",SUM(data1),IF(A1=­"Average",AVERAGE(data1),IF(A1­="Min",MIN(data1),IF(A1="Max",­MAX(data1),IF(A1="Count",COUNT­(data1),"Function Missing")))))

[jbrian457]

Sorry I probably should have explained it a little better. I'm trying to track my monthly spending and i want something that will add up everytime i spend money in say the grocerys category. A1 though A100 have a dropdown menu with different category's. i.e. groceries, dining out, entertainment. column B has the amount I spent in each. looking for something that will search A1 through A100 for the word grocerys and only total the amount to the right in column B, and put the result in say H23.

[DownUnderGran]

Hi

I am trying to use this video lesson to understand this...

Use the IF function to display the text "OK" if the function in C25 (where there is another formula) evaluates to TRUE and 'too high" if it evaluates to FALSE

You make things seem so simple but I can't seem to apply it to what I want to do.

[jbrian457]

Excel Magic, I’m having a little trouble with a formula. Wanted to know if, for example any of cells A1-A100 have the word "groceries" in it. I want to take the value in the B column that corresponds to it, and add it in another cell.

[ExcelIsFun]

A formula like this might work:

=IF(A1="groceries",B1,"")

[munmunmonty]

@ExcelIsFun - can't we do it with "vlookup" rather "if"?

[ExcelIsFun]

It totally depends on what you are trying to accomplish. If you serach for IF VLOOKUP, I have a number of videos that compare the two functions and when it is best to use one over the other.

[munmunmonty]

@ExcelIsFun - You are doing great job mate. Best wishes for your future plans.

[ExcelIsFun]

Thanks!

[udanial]

sir there is any if use advance videos?

[ExcelIsFun]

Try this video Playlist with about 70 IF videos:

Excel IF Function Formula Tricks

[Finty290]

Hi man, I have a question which I am stuck on, I wonder if you could help... the question is - A premium of 1.5% if the house value figure is less than €250,000, and the contents value figure is less than €10,000; a premium of 3% if the house value figure is less than €400,000, or the contents value figure is less than €15,000; otherwise a premium of 5%. - I have two columns with the house value and contents value, would u have any idea what the formula should look like? thanks alot!

[ExcelIsFun]

If the House Value is in cell B4 and the Contents Value is in cell C4 this formula may work:

=IF(AND(B4>=400000,C4>=15000),­0.05,IF(AND(B4>=250000,C4>=100­00),0.03,0.015)) but I would test it to see if all cases are covered. For example, if House Value is 300000 and Contents Value is 9000, this formula will yield 0.015.

[Finty290]

@ExcelIsFun Hey man thanks alot for your quick response! Yeah I can see where I was going wrong now, nice one :) you rock!! had to refresh myself in excell real quick, your videos helped alot.

[ExcelIsFun]

I am glad that it helps!

[JointBuster]

how do you use the if " function with dates.

lets say if someone had a job maturity higher than 5 years, then he should get a bonus?

[ExcelIsFun]

You have to tell me how your data is setup. Give more detail and I can find a formula that will work. Give me some data and an example of the result you want, and I can find a formula.

[JointBuster]

@ExcelIsFun I have an exercise like that: column of dates when people started work: 01/02/1981 02/03/1992 19/02/1980 and i am told to give a bonus to people by their job maturity. say a person who worked for less than 5 years gets no bonus. ones who worked more than 5 years, get 1000 bonus, and who worked more than 10 get 2000 bonus. at the first part of the "if" function i tried to choose the cell and do this. A1 (the date of the works start)> A1+5 if true: 1000 but exel doesnt see 5 as years

[ExcelIsFun]

Try this formula :

=VLOOKUP(DATEDIF(A2,TODAY(),"y­"),{0,0;5,1000;10,2000},2)

or this:

=IF(DATEDIF(A2,TODAY(),"y")<5,­0,IF(DATEDIF(A2,TODAY(),"y")<1­0,1000,2000))

I'll make a video of the 'whys' next week.

[JointBuster]

@ExcelIsFun

THANK YOU.

i saw a vid of how to do it.

you make a "date" function,, then you can add + 5 to it .. the second example is clear

[nicoyanicoya]

learning a trick a week!

how did you get your excel to "say" the numbers..i can hear it in the background!

[ExcelIsFun]

Try this video:

Excel Magic Trick #71: Excel Will Talk To You

[bbdarthchris007]

I made an excel "program" to help me punish my kid fairly. I love it you can do almost anything with excel

[ExcelIsFun]

Now that's probably an Excel first!

[hasina21]

Hi, I am looking to validate UK Postal Code using IF formula in MS Excel, can you be any help!!! The postal code is should be in the following format:

AN NAA

ANN NAA

AAN NAA

ANA NAA

AANA NAA

AANN NAA

When A is alphabet and N is number. If the condition is incorrect then highlight that particular cell that contains the incorrect postcode. Many thanks in advance.

[ExcelIsFun]

I do not know how to do that. You should post your question to the Mr Excel Message Board (THE smarest Excelers around ahng out at this site):

mrexcel[dot]com/forum

[hasina21]

Thanks for the replay anyway appreciate it.

[kohtnet]

Excewllent just saw your viedeo. I want to learn excel and want to be perfect but I am just a biggner. How long it takes to be perfect in excel. How can i download your download the workbooks. I hope i can learn something for your watcing video's. I write down what you says in videos and it's takes a long time. If you a note which you have a videos' might be learn fast. Keep on posting. Thank

[ExcelIsFun]

Search for and watch this video tile:

Search & Find Excel Videos, Playlists, Download Workbooks - excelisfun

[nicoyanicoya]

love that u are doing 2007 now !!!

Keep it up professor !!!

[ExcelIsFun]

I will!!

[romanticgirl888]

Dear Mr. ExceIsFun,

You rock! I learned so much for your videos and workbook. Thanks!!!

Jen

[ExcelIsFun]

You are welcome! I am happy to hear that you download the workbooks also, many people don't, and thus don't get the full effect!!

[TheDanCas]

there's two L's in his name...

[Mynotoar]

That was especially interesting, the part on using a drop-down list to be able to decide what specifically to calculate. I'm gonna trundle through this playlist in the hope that I find how to use IF in an array / multiple IFs lol.

[ExcelIsFun]

If you look down the IF function playlist:

1) You will see the videos about "nesting" IF functions, which will be good for your question about how to have multiple IFs in one formula

2) You will see one for "Medain IF", this one will show how to use IF in an array.

[poi9090]

here displayed #name? =/

[ExcelIsFun]

I don't understand your question. Can you state your question differently?

[BerPC96]

Taking this in college right now what are some out of class materials that you can give me that would help supplement my learning experience......does video professor warrant any investigation for good material????

Thanks!

[ExcelIsFun]

Dear BerPC96,

I don't know who Video Professor is. But I have over 800 Excel how to videos. You may be interested in the Excel Basics Series and the Highline Excel Class series. watch this video title to see how to get to these videos and how to download the free book and Excel workbboks:

Search For Excel Videos Download Excel Files excelisfun

--excelisfun

[RJ8976]

I am just learning to use excel and was wondering what the if function would look like when trying to determine which students Passed/Failed (i.e. got a mark of 50% or >). I tried a few different things but I cant get it to work...

thanks!

[ExcelIsFun]

Dear RJ8976,

I have a Video Playlist that shows many ways to do this:

Various Excel Grade Book Examples

If you can't find it, search for and watch this video that will show you how to use the excelisfun channel including finding Playlists:

Search For Excel Videos Download Excel Files excelisfun

--excelisfun

[huyenng]

EXCELlent!

I have a question: how to make the DROPDOWN, like the one you have in your example.

[ExcelIsFun]

Dear huyenng,

Try this video:

Excel Magic Trick #5: Data Validation & VLOOKUP

--excelisfun

[pwnions08]

amazing what you can find on youtube!!

thanks for all your videos .. you have been my best friend this ENTIRE week lol

-needsmorefriends XD

[ExcelIsFun]

Dear pwnions08 ,

You are welcome!

--excelisfun

[MmansoorM]

Could you tell me please what function to use for increase and decrease in percentage ?

Thanks

Second question:

I have some data about crimes from 2002 to 2008 and also name of the area. Could you help me what functions and other things can I use for that data ? Please I would be very thankful to you if you help me in this. Thanks

Keep the good work.

[ExcelIsFun]

Dear MmansoorM ,

Search for this video and then watch it:

Search For Excel Videos Download Excel Workbooks excelisfun

This tells you how to use the excelisfun channel. In the Playlist area, I have the Business Math Chapter 3 Playlists with many examples of exactly what you are looking for!!

--excelisfun

[BrianRuelPeru]

who ever this persone is. thank you becouse you helped me pass my computer test!!

[ExcelIsFun]

Dear BrianRuelPeru,

I am glad that the video helped you to pass your test!

--excelisfun

[capodaughter]

can you tell me how to use percentgae change on excell then graph please?

[ExcelIsFun]

Dear capodaughter ,

I do not have one video together that shows what you want, but these two together should help:

Excel Name Trick #11: Global Percentage Change Formula

Excel Magic Trick #103: Pie Chart For Student Scores

--excelisfun

[ExcelIsFun]

Dear capodaughter , See this video that I made today:

Excel Magic Trick # 267: Percentage Change Formula & Chart

--excelisfun

[edwindrg]

Can this logical test work with text. I mean, if F2 = "Ed" then 0 if false then 1. I have only seen it work with numerical values but not text. I know is a logical text and it doesn't disqualify text filled cells. Please, if you can let me know if it does work with text also. Thanks!

[ExcelIsFun]

Dear edwindrg,

Absolutely!! The IF and logical tests work on words also. If you watch the 9:30 mark of this video you will see a text example.

Your Formula, =IF(F2="Ed",1,0), will work just fine!

Also, see this video for a laudry list of logical tests with numbers, text, and operators:

Excel Magic Trick 62: Logical Formulas & Comparative Operators

Here is another video when the IF looks at text:

Excel Magic Trick #145 p3 AVERAGE IF before 2007 Excel

--excelisfun