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!
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.
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"))
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 :)
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
,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.
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 :-)
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 ????
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.
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
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!).
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!
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
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...
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"
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
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
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
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 :)
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
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?
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!!! :)
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?
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?
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!
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?
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.
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 .
![BabyChiefDoit - Too Slow (feat. STAR BANDZ) [Official Music Video]](http://i.ytimg.com/vi/zayaxws7RPg/mqdefault.jpg)
I am glad that the videos help! That is why I put them out there!
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!
You are welcome!
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.
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!
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.
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"))
thanks😃 helped me alot
Seen this video 5time
before my exams
this helps me remember all😃
EXACTLY what I needed. Thanks a billion. Now I can finish my Accounting assignment.
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!
Yes. Here is more about IF:
Excel Magic Trick 452: IF Function Formula 16 Examples
I am glad that the videos help!!
You are welcome!
It means if the 3 cells have yes, then put complete, otherwise put incomplete.
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 :)
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
,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.
Here it is:
Excel Magic Trick 167p1
Excel Magic Trick 167p2 IF
Highline Excel Class 33: IF AND OR Customer Credit Analysis
@saraliseth , Cool! I am glad it helped - and I learned a new word: EXPECTACULAR!!
@thethingsdestroyer7 , you are welcome!
Thank you for over delivering and making positive impact
You are welcome for the positive impact, tiki : )
man I wish I had a teacher like you
Everything made sense and I understood what to do. Thanks mate!
alphatom493 | Promethium45 , Glad it helps!
Thanks :)
I just needed many cells but thank you for the single cell formula.
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 :-)
Thank you, I've just discovered your videos and my work life became so much better :-)
I love to hear that!!!
Once again your video helped me figure it out. thanks!!
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
????
@bluebell264 , you are welcome!
Thank for your hard work
You are welcome!
Maybe:
=IF(A1="Final Fantasy","Correct!","Incorrect")
A1 = cell.
Conditional Formatting is how you do that:
Here is a video title:
Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples)
Try:
=IF(AND(Cell1="Yes",Cell2
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.
=IF(CellWithYes="Yes",CellWithDate+45,"Exempt")
Thanks!!! you made this soo easy to understand!!! YOU'RE EXPECTACULAR!!!
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
learnt something great!
Glad it helped!
So well explained, thank you.
For Excel, start at video #20 and go to 40.
I am not sure, but maybe AND function in IF function logical_test argument?
Thank you! Would really help for my test :)
You do, here at RUclips!
@Ashybearification , I am glad the video helped!
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!).
Maybe 2 IFs like:
IF(ISNA(A1),0, IF(A1="yes",A2,0) )
I think that's exactly what I have to do, thank you! I was very confused.
Maybe: =IF(ISNUMBER(SEARCH("L",A1)),"Large","No L")
Read statement at beinging of video or click on the link belo the video.
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!
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
=IF(AND(A1>=15,A1
Great, Clear, Helpful..
Glad it helped!
Excellent, thank you very much!
Man, you are doing great videos! almost all I know in excel, I got it on this channel! :)))
thank you so much man you're number one thanks
Thanks for ur Great Effort
Nicely presented
Thank you, great lesson!!!!
learn something new everyday
thnx alot u helped me lots with ma skl work...i think u need to come teach ma class lol :)
Thank you! This is a wonderful video.
Thanks alot sir
You are welcome a lot! Thanks for clicking Like!
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...
You the man, I was soo looking for that ! Thanks :)
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?
Found it: Class 35: IF Function Vs. VLOOKUP! Thanks again!
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
Thank you again!!!!!!
Happy new year!!
Could you please tell me how to convert pivot table to a regular table ?
Thanks a ton
Thank you for your post!
+latinac You are welcome!
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"
Just wanted to quickly say thank you and that you have know idea how much you helped me. :-)
can you test whether a cell has a formula (concatenate)?
Thank you bro!
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
@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
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
please tell me how to use IF in jugding student's ability: awesome, good, normal, weak according to the marks. thanks a lot
hi MrExcel, can you please state what this formula means?
=IF(U2="yes",IF(Y2="YES",IF(Z2="YES","COMPLETE","INCOMPLETE"),"INCOMPLETE"),"INCOMPLETE")
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
If i wanted to add a colour to the cell if the value was less than I wanted how would you do that?
Great video!
Glad you liked it!
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 :)
@Slipknotfan13666 , , I am glad that the videos helped, but robot RUclips teachers would not work like this RUclips teacher who is a human...
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 :)
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
awesome thanks
double click!! boom. Thank you very much!! It's fun and useful :)
You are welcome!
ExcelIsFun What is the best way to ask a question directly for a dataset I am having trouble with, can I email you directly?
Is it incorrect to lock the Bonus Hurdle completely? Instead of using B$9 I thought it was correct to use $B$9
Either one is fine in this case!
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
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?
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?
@dukk77777 ,
=IF(A1>=2,What to do if it is true, what to do if it is false)
Thank you :)
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!!! :)
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?
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?
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!
how to apply if function for "2 or more" how we should write it in excel?
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?
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.
I do not know. You can try THE best excel question site:
mrexcel.com/forum
awesome
thanx
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 .