Office 2010 Class #38: Excel IF Function Formula Made Easy (7 examples)

[@excelisfun]

I am glad that the videos help! That is why I put them out there!

[@excelisfun]

Yes. Here is more about IF: Excel Magic Trick 452: IF Function Formula 16 Examples

[@excelisfun]

Copy PivotTable, then do a Paste Special Values. You can also use Format Painter button on PivotTable, then click on the Paste Special Values area.

[@excelisfun]

@thethingsdestroyer7 , you are welcome!

[@excelisfun]

@bluebell264 , you are welcome!

[@excelisfun]

In a single cell? Or in many cells? Many cells, use: =COUNTIF(range,5) Single cell is quite a hard formula, but here it is: =SUMPRODUCT(--(MID(cell,ROW(INDIRECT("1:"&LEN(cell))),1)="5"))

[@excelisfun]

You are welcome!

[@excelisfun]

I am glad that the videos help!!

[@excelisfun]

I am not sure, but maybe AND function in IF function logical_test argument?

[@excelisfun]

It means if the 3 cells have yes, then put complete, otherwise put incomplete.

[@excelisfun]

Read statement at beinging of video or click on the link belo the video.

[@excelisfun]

@Ashybearification , I am glad the video helped!

[@excelisfun]

Here it is: Excel Magic Trick 167p1 Excel Magic Trick 167p2 IF Highline Excel Class 33: IF AND OR Customer Credit Analysis

[@excelisfun]

For Excel, start at video #20 and go to 40.

[@GISS06]

What an amazing lesson! I really like the way you explain things. If all of the videos are like this, I am going to get good at this!

[@excelisfun]

I am unclear on all the parameters of what you are trying to do, But maybe a formula like this: Given numbers in column A: =IF(A1="one hundred",100,A1) Then copy down ????

[@excelisfun]

@saraliseth , Cool! I am glad it helped - and I learned a new word: EXPECTACULAR!!

[@JonathanThompson1320]

You're so awesome! I watch all of your videos. It's amazing how much I've learned from watching your videos. My skill set thanks you!

[@excelisfun]

Maybe: =IF(A1="Final Fantasy","Correct!","Incorrect") A1 = cell.

[@excelisfun]

@lebavo , the formula would have to be in the cell, otherwise you would have to use VBA code. Try posting question to: mrexcel [dot] com/forum

[@excelisfun]

Try: =IF(AND(Cell1="Yes",Cell2

[@excelisfun]

=IF(CellWithYes="Yes",CellWithDate+45,"Exempt")

[@excelisfun]

Conditional Formatting is how you do that: Here is a video title: Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples)

[@etoilevn]

Hey Leon, put the cursor in the cell 1 and press F4 one time, that will prevent the cell from changing to other cell. If cell1 is A1, press F4 and it changed to $F$1. It will work :)

[@excelisfun]

Maybe 2 IFs like: IF(ISNA(A1),0, IF(A1="yes",A2,0) )

[@Ashybearification]

You have helped me finish a college assignment in Computer Applications. Thank you so much!! I was racking my brain and what was my problem? I wasn't using "double quotes" for the text in my formula.

[@nedivaavia]

Great to have found detailed techy videos that are actually interesting to watch. I love Excel!! Thanks so much for all of these - gonna go grab that promotion :-)

[@excelisfun]

Maybe: =IF(ISNUMBER(SEARCH("L",A1)),"Large","No L")

[@excelisfun]

@dukk77777 , =IF(A1>=2,What to do if it is true, what to do if it is false)

[@clairewu2040]

,For all other accounts, the billing rate varies based on the type of customer. The billing rate is $3, $2, or $1.50 per thousand gallons used depending on the type of customer, For example, a commercial customer using 75,000 gallons has a water bill of $225 (75x$3), whereas a government customer using 100,000 gallons pays $150 (100x$1.50). A commercial customer using 15,000 gallons has a water bill of 0.

[@Twizzzle]

What you want is the $ sign to lock the value to one cell. A$1 will mean if you copy and paste in another cell it will only use A1. Another is multiple if statements which will look something like this. IF(test, iftrue, iffalse) You can place another if statement in iftrue or iffalse. Your formula may look something like this in say cell A2 =IF(A$1="Yes",5,IF(A$1="No",0,0)) Your column formula would add A2 like so +A$2

[@bootsntrails]

EXACTLY what I needed. Thanks a billion. Now I can finish my Accounting assignment.

[@leeza38]

This has been a short and sweet way to learn the IF function in Excel. I'm expanding my professional horizons in accounting, and this skill happens to be one that's required in most industries which I lacked until now, and with further practice. Thank you greatly!

[@excelisfun]

You are welcome!

[@excelisfun]

=IF(AND(A1>=15,A1

[@excelisfun]

You do, here at KZbin!

[@haveagreeenday]

man I wish I had a teacher like you

[@drewfasa]

Thanks, this is great. Are you aware that when using an 'IF' function you can also just refer to another cell for your logical test if that cell already contains a logic statement? I stumbled upon this fact while working through your vids (they are fantastic!).

[@jasminmemic]

Thanks :) I just needed many cells but thank you for the single cell formula.

[@saraliseth]

Thanks!!! you made this soo easy to understand!!! YOU'RE EXPECTACULAR!!!

[@shez4izzie25]

please can you use if function to change the colour of a cell if some conditions are met,how can that be done, and also can a formula be embedded to carry out this task? thanks

[@raycostello7449]

Excellent class. I do have a question. How would I create a summary from a spreadsheet that has several statuses. For example; out of the list of open issues, create a summary of the items that are on time, open, high priority. Thank you, Ray

[@fhjhgoirt]

You r doing a great job for learner and keep it up, I really enjoying to learn from ur video, And if possible can u make us to download worksheets to do practical with such worksheets

[@cwf88]

Could you please tell me how to convert pivot table to a regular table ? Thanks a ton

[@RomanGarmash]

Man, you are doing great videos! almost all I know in excel, I got it on this channel! :)))

[@JadeCoon]

I am working a project where I have to take the max weight allowable for a shipping container and using the number of pallets controled with a button and the weight of the pallets and come up with the number of containers needed for shipment. Can you advise me on how to do this?

[@ugustavo]

Got a few column like Item, Qty Order (C2), Qty Supply(D2),Back Order(E2) and Status(F2). The status column (F2) i want to show is either completed or back order quantity. Back Order (E2) value based on D2 - C2. My problem is how to combine false value with character inside if function. =if(E2=0,"COMPLETED",E2). The result shown either 0 or 2 based on E2 quantity. How to show if got back order, the value back order combined with character let say Back Order 2

[@Almanar4newRealizing]

could you pleas give me the link of the video which explains what you were talking about from minute 2:14 to 2:19 and i want the video which explains "the double click" that makes the cells beneath have the same equation and when you hit f4 the equation changes as example " from m2 to m$2 " what is that please, sent me these videos which explains all of that becuase you have alot of videos and i can't search among them, it gonna .. take a lot of time regards .

[@mattkishon1]

Hey i got a project and iam suppose to Calculate the court date of the customers, Court date is calculated as: IF Court is YES then add 45 to payment date otherwise they are exempted. I already have written up the a column called STATUS that shows the if the person is going COURT or NOT and The Payment date is also in its own column with dates, that should now have 45 added to it

[@Avenger509]

can i use defined names as the logical value of the if-function? now those defined names are entire columns, how can i do that?

[@lebavo]

May I please ask for HELP: What would the formula be for: The user either puts in a YES or NO in a cell Yes = adds 5 in another coulum No = 0 (zero) Long shot to ask, though I am a bit lost. THANK U oVe

[@TheProberts87]

If i wanted to add a colour to the cell if the value was less than I wanted how would you do that?

[@MusicAddictz331]

Hi, I have a project that I have to do that involves the calculation on weighted averages. My teacher want us to modify the formula for weighted average so that any cell containing the string “one hundred” (all lower case) instead of a numeric score is treated as if it contains the score 100 and use the IF function. How would I do this?

[@bclopez03]

I have an excel project where I have to do an IF function that involves 2 cells, one has the Word Yes or No in them and the other cell has a number value in gallons. The problem asks that IF cell 1 says No and the value of cell 2 is less than 25000, display a 0. otherwise display the value of cell 2. How can I achieve this?

[@ATLTraveler]

no one says if statements are ADVANCED excel...that's like the first thing you learn lol....advanced I would say like really complicated word length problems...boy those are fun...like using the LEN, RIGHT, LEFT, FIND functions with concatenate to extract bits of information out of cells....SO FUN!

[@melissad3390]

can you test whether a cell has a formula (concatenate)?

[@jasminmemic]

Hi, I have a project where I need to count how much number 5's I have in any cell.Please help and thanks for the tutorial :)

[@illinny]

Hello, I'm having trouble in Excel. I want to search a column for any cells that contain "-L". Any cells that contain this, I want the column to the right of it to say "Large". What formula should I use? Can you point me in the right direction? Thank you!

[@lauracastillopereira2312]

Can you explain when to lock the column, the celll or both , I dont get how that works , in min 9: 35 I would've lock the cel AND the column

[@shez4izzie25]

hi MrExcel, can you please state what this formula means? =IF(U2="yes",IF(Y2="YES",IF(Z2="YES","COMPLETE","INCOMPLETE"),"INCOMPLETE"),"INCOMPLETE")

[@kmurakam25]

@ExcelIsFun where do i go to download the spreadsheets?

[@t1mech1ldtc60]

You the man, I was soo looking for that ! Thanks :)

[@XxfLuXpRidExX]

Please help, say I wanted to tax 19.5% to people with income of less than $38,000 and 33% tax to those with income higher than $38,000. Which formula would best suit this equation?

[@LienNguyenllt911]

please tell me how to use IF in jugding student's ability: awesome, good, normal, weak according to the marks. thanks a lot

[@MusicAddictz331]

I think that's exactly what I have to do, thank you! I was very confused.

[@dukk77777]

how to apply if function for "2 or more" how we should write it in excel?

[@MrPouHan]

Great vid, thanks. Is there a way to do multiple IF's (conditions) within the same cell. i.e. Conditions within the same cell (A2) IF Cell A1 < 33.3% than print "C"; IF Cell A1 >= 33.3% but < 66.6% than print "B" IF Cell A1 >= 66.6% print "A"

[@jasonweiss5461]

Thank you for helping me to understand how locking a row works. So by not doing that Darius Raffity would've got a bonus b/c his sales would've been compared to $750 if I'm correct.

[@AlphaPromethium]

Everything made sense and I understood what to do. Thanks mate!

[@excelisfun]

alphatom493 | Promethium45 , Glad it helps!

[@Brydon22]

Questions? what is the function do I need to use if a cell was bigger than a number (eg A5 was bigger that 1) and I wanted it to apply a math problem. (eg (E5-(D5/C5))/E5 ) and if it was smaller apply another math problem. (eg (E6-D6)/E6 ) If anyone can help that would be great thanks

[@together1478]

Thank for your hard work

[@excelisfun]

You are welcome!

[@megangaylan4541]

hello sir, we want to know about the "if" formula. In the "IF" formula why is there a "0" where can we use the formula.. please answer this we really need the answer.. Thanks :)

[@pirepreri3083]

ExcellsFun Could you help me to solve my problem.. I was trying to do a IF Function that wasnt working and if you know a way around it would be much appreciated. =if(A1

[@dnice2420]

Once again your video helped me figure it out. thanks!!

[@amaan1359]

thanks😃 helped me alot Seen this video 5time before my exams this helps me remember all😃

[@AA-lq5pu]

So well explained, thank you.

[@kebinxp]

Is it incorrect to lock the Bonus Hurdle completely? Instead of using B$9 I thought it was correct to use $B$9

[@excelisfun]

Either one is fine in this case!

[@zaladane19]

Nicely presented

[@msmass2826]

I need 2 formulas: I need to know if one column is higher by at least 6 then another column, then the cell needs to say "yes" also, I need to know the percentage of "yes" out of the total # of people PLEASE HELP!!! :)

[@paveldkohout]

Hello Excelfun! You made me leard a bunch so far! Thank's Teacher Is it possible to do a logic test that would display 3 results?

[@paveldkohout]

Found it: Class 35: IF Function Vs. VLOOKUP! Thanks again!

[@excelisfun]

Try this video at 7:58 min: Excel Magic Trick 452: IF Function Formula 16 Examples Excel Magic Trick 452: IF Function Formula 16 Examples

[@paveldkohout]

Thank you again!!!!!! Happy new year!!

[@adriancortinas2565]

Excel Master, my boss wants me to add a formula to a spreadsheet using the "if" formula, but I don't know if it's possible. We have a purchases sheet where our managers list all purchases made throughout the week from multiple vendors. He wants a formula that adds them all up on a separate tab, however, the vendors are listed randomly, not alphabetically. The problem is, the likeliness of a misspelling of the vendors is quite high. So, for example, if we have a vendor whose name is "Smiths" I would want to have a formula the identifies the first 3 letters (it is much more unlikely they would misspell the first 3 letters) and proceeds to add up all purchases made from "Smiths". So how do I make the formula- "IF" a word begins with these letters, add the cell next to it to all other words that begin with those letters adjacent cells. So hard it to explain really.

[@excelisfun]

I do not know. You can try THE best excel question site: mrexcel.com/forum

[@OldmavericWoW]

Thank you! This is a wonderful video.

[@hudazainal1719]

Thank you! Would really help for my test :)

[@itsmarialifestyle4355]

Thank you, I've just discovered your videos and my work life became so much better :-)

[@excelisfun]

I love to hear that!!!

[@jimmys4189]

Thank you, great lesson!!!!

[@excelisfun]

@Slipknotfan13666 , , I am glad that the videos helped, but robot KZbin teachers would not work like this KZbin teacher who is a human...

[@rollygawali4417]

Great, Clear, Helpful..

[@excelisfun]

Glad it helped!

[@missmirusrobinson6913]

learnt something great!

[@excelisfun]

Glad it helped!

[@tikichock5813]

Thank you for over delivering and making positive impact

[@excelisfun]

You are welcome for the positive impact, tiki : )

[@hanynaguib92]

Thanks for ur Great Effort

[@thecrow4711]

Ok I have a hard one, maybe not for you but for me it is impossible so far. I like to compare info from about 9 different fields with different numbers Then if those numbers are > then or = to it has to add 1 point so +1 If the number is lower it has to stay 0 The fields are all over my spreatsheet, it are game results. The lists they have to go in are wins, losses, and points. In the result fields are 16 different games with 32 teams This event returns 3 times so al together it are 96 games I don't know how to explain it to be honest, but i hope you understand what i mean. Some way to put multiple IF functions in one funtion. Is this possible/doable? Kind regards

[@excelisfun]

Try THE best Excel question site: mrexcel.com/forum

[@salvatorecm7002]

thank you so much man you're number one thanks

[@iankelly2962]

Excellent, thank you very much!

[@latinac]

Thank you for your post!

[@excelisfun]

+latinac You are welcome!

[@marlenehutchison9113]

Great video!

[@excelisfun]

Glad you liked it!

[@dramaqueen4640]

5:40 is the thing

[@iankelly2962]

Agreed

[@orenji13]

Thank you bro!

[@Intel-i7-9700k]

oh MAN this was annoying i kept on using the "." as seperator but i had to use the ";" instead. fail :D

[@marcohernandez1816]

learn something new everyday

[@tadishettysantosh4453]

Thanks alot sir

[@excelisfun]

You are welcome a lot! Thanks for clicking Like!

[@monster911isme]

ExcelIsFun cAN YOU HELP ME? Base on my sheet the M7 cell which the joined date have got a formula like this =VLOOKUP($C$3,VALIDATION!$A$2:$M$500,9,FALSE), so when I choose in C3 (drop-down list different employee number) the joined date in cell M7 will change, so from there the number of months in J11 will change according to the joined date. IF M7 GOT 1/3/2011 (IT NEED TO BE CHANGE AS 1/3/2013) SUBTRACT TO TODAY DATE 26/10/2014 SO I NEED THE ANSWER OF 19MONTHS (because 1/3/2013 to 1/3/2014 = 12 months or 1 year then + the 1/3/2014 to today date 26/10/2014 = 7 months so MUST BE TOTAL OF 12+7 = 19MONTHS... then only I can count how many entitled leave they can have to date. thank you again...

[@bclopez03]

awesome thanks