Everything You Need To Know About The If Formula In Excel
HTML-код
- Опубликовано: 15 июл 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
Learn how to use the IF Function to write formulas that return results based on a condition or logical test. This tutorial also includes training on logical tests, comparison operators, nested IF formulas, multiple conditions with AND and OR functions, and common causes of formula errors.
If you’d like to read the accompanying blog post on our website, you can access it here: www.excelcampus.com/functions...
Related Videos:
IF vs VLOOKUP vs XLOOKUP - Commission & Tax Rate Calculations in Excel: • If Vs Vlookup Vs Xlook...
Introduction to Pivot Tables, Charts, and Dashboards in Excel: • Introduction to Pivot ...
Excel Tables Tutorial: Beginners Guide for Windows & Mac:
• Excel Tables Tutorial:...
VLOOKUP Tutorial for Excel - Everything You Need To Know:
• Excel Vlookup Tutorial...
#MsExcel #ExcelCampus
00:00 Introduction
00:40 If Formula Tutorial for Excel
01:04 If Function Explained
03:48 Write an If Formula
05:01 Optional Arguments
05:46 Grouping Data with IF
07:47 Cell Reference Technique
12:08 Nested IF Formulas
15:53 Causes of Errors
17:54 If Formula Challenge
As always Jon......excellent. You are a natural teacher. Just the right pace for newbies and no padding in there at all. Concise and easy to understand. Thank you.
Jon, I just have to say what an awesome tutor you are! It's partially about what you reveal but mostly about how you do it..... You are as logical as an Excel spreadsheet but > it is your ability to express the process with concise clarity that makes you excel. I have learned - and more importantly, understood, so much through your tutorials. Keep on, keeping on - ad infinitum!
These are really helpful videos thank you. I’ve paid for a course recently and I find your videos much better and understand and practice with
Thank you Jon, you are truly a gift. I improved my excel skill by watching your tutorials. God bless you!
This video is a life saver every time I needed a refresher on nested ifs. Thank you Jon!
Thank you Jon for showing how to use this really useful function on building simple report.
thanks from somalia Jon, they helped me too much.
This is the best video I have seen on the topic. Great examples and easy to follow along!
real excel expert this man, tks goodness for him
An excellent description of the IF formula usage. Thumb up!
HOLY CRAP. THANK YOU! YOU ARE SO EASY TO FOLLOW!
Thank you so much! You tutorials were better than the course I recently paid for.
This is the best tutorial. Thank you
Great explanation. Very detailed.
Thank you, Jon. Your videos are very helpful.
Excellent Job!
i learned clearly and sharply use of inbuilt function of IF
What a refreshing of knowledge !!! Thanks Jon ...keep sharing please
Really enjoyed this one and the pace of explanation.
I always find your tutorials simple and easy to follow, Naturally a teacher keep it up
Happy to hear that, Beatrice! 😀
Thanks for your video. It was very well explained and easy to understand.🙏
WOW! You explain the process brilliantly. Thank you!!
You are so welcome! 😀
Excellent tutorial! Thank you.
I had to come comment because I couldn't find the answer for the nested IF statements for a project I am working on for a class, but you were able to make it very clear on how to write them. Thank you so much, I was getting so frustrated!
Thank you very much for this tutorial, I appreciate.
Jon, love your videos! Thanks!
Thanks, Dear Jon. Good Luck, Go Ahead dear . . .
Thank you so much for such a discriptive video.
Needed this video! Thank you for sharing your knowledge.
You are so welcome! 😀
Thank you for this video!
Thanks for the video as it was very helpful!
Great Tutorial On What I Would Class As One Of The Most Important Functions To Learn In Excel..Thank You Jon :)
Thanks Darryl! 👍
Thanks for this training
An excellent video, Jon! You explained it all really clearly, thanks.
Thanks Chris! 😀
Watching your videos always feel like having a teacher patiently teaching me one on one, thank you for all the clarity and contextual explanations, dear Professor Jon :)
You are a Rockstar! Thank You!
Thanks for the video Jon, will definitely come in handy as a reference video.
Thanks Paul! I appreciate your support. 🙌
Really nice, have learned something new today✌✌
Awesome toturial
this is a good tutorial, thank you sir
Formally studying Excel for an apprenticeship, and these videos are a real help. The videos on the apprenticeship aren't this good, and having missed a live session, I was really struggling
Great to hear your feedback about our videos. 😀
Fantastic video ,love you so much , 💖💖💖
Thank you so much!! 😀
I have a job interview coming up that will include an Excel task, so I need to brush up on my skills and knowledge. This video, along with your others, is handy, clear and concise. Many thanks, Jon.
Goodluck to your interview! 😀
Great Work!
thank you, excellent tutor.
You are welcome, @user-jz6yl8cg8q ! 😀
Thank you Jon.
thankyou sir your content is really amazing and helpful plz keep posting them
Thanks for your support!
Hi Jon, thank you so much for these videos. They are definitely very helpful. Question for you though….is there a way to look at a group of logical test v. individual logical test to yield a result? I’ve created a enormous embedded IFAnd statement ( to where I’ve reached the maximum allowed by my version of excel) but I need to figure out a way to consolidate so that the formula isn’t so large. Any insight that you can provide would be greatly appreciated. Thanks
Simple and great
Thank you Jon
thank you, Jon
Thank You 👍🏽
very good, thanks.
THANK YOU .
Excellent explanation- how about comparing data on one sheet against info on another sheet ( normally I use the vlookup function- can u do it with the IF function too ?) thanks
Excellent
Great !!
Great work thanks pleas can you make in come and expenses daily report
what an amazing tutor, highly organised video, well explained, good demonstration, you have no idea how if feel owe to u
I appreciate that! Thanks for watching!
Nice work
thank yo Jon Acampora
You're welcome, @mustafayusuf4112 😀
Hi Jon, tour tutorials are awesome.
On nested If, where you have the second IF, after the argument "small", I suspected that you should close and end there. *13:55 to *14:15*.
There is no room or option for "false" argument). I did try for the sake of clearing the doughty and it gave "you've entered too many arguments for this function"
PS i subscribed ... lots of videos to learn from 😀😀
Oh you are the best formula instructor ever for me. contrary to your kind and step by step illustartions of formulas, other instructors just skipped them and i dont get what they are sayin. thank you . would you like to join in Excel Mos2016 pre exam instructors in Korea, YBM. i do believe they should all fire instructors with no english experiences of excel skills that cannot specify step by step formulas and their languages. i was listening to Korean Excel instructions but with no idea of what they are saying, then i googled here to find out and try to understand of formulas explained step by step here
Hi John nice video, so is the ( IF ) function one you would use if for example you wanted to check the value of say cell C1 $500 and cell C2 $400 , if the value's don't match then perform a calculation etc
subtract G2 from G3 ??
Do you have a list of your simplified definitions? I would love that cheat sheet printed at my desk
Hello Jon Good job man
i have a question i formed as an excel file and I'm gonna send it later to if i can match multi different counter condition to give also different results . I know my question seems confusing but in xlsx file i explain it thoroughly
Hi Jon. Can I use the IF function to do a calculation if the logical test is yes. Example, if LT is yes A minus B if no B minus A.
Can you please show us how to make Macros faster in a huge data.
Great video however if we have a list of email address on one tab and first name on another tab what formula can we use to bring the email and the first name of that email address together?
If we have 3 columns with Schedule start date, Schedule end date and Actual end date.
Can we find the number of days between two dates based on text from a cell .. if text value is “resolved” I need the date formulas as current date minus scheduled end date. If status is “resolved late “ then Actual end date minus schedule end date.
Thank you Jon! Is there an IF formula or is there another function that would allow me to look at how many times "a specific value" appeared and what dates were associated with it? For example: Let's say I have 10 Pickers on my farm and every day the total number of fruit picked are logged, by date, amount, type, and picker.
DATE (A) FRUIT (B) Picker ID (C) Amount Picked (D)
10/19/2021 (A) Apples (B) Picker 1 (C) 60 (D)
10/19/2021 (A) Apples (B) Picker 2 (C) 92 (D)
10/19/2021 (A) Oranges (B) Picker 3 (C) 90 (D)
10/19/2021 (A) Oranges (B) Picker 4 (C) 74 (D)
10/20/2021 (A) Oranges (B) Picker 1 (C) 60 (D)
10/20/2021 (A) Oranges (B) Picker 2 (C) 92 (D)
10/20/2021 (A) Apples (B) Picker 4 (C) 90 (D)
10/21/2021 (A) Apples (B) Picker 4 (C) 74 (D)
If I I wanted to know how many times the value "apples" occurred for each date adjacent to it within Column A (Date), how would I count how many times Column B (Fruit) returned the value for apples on that given date? I would like a return value that looks at all the dates within the date column where both Apples and a Date occur and count how many times it appears for that date. From my data snapshot above my ideal result would say:
10/19/21 2 (which means there is a total of 2 occurrences for apples on 10/19/21), and 10/20/20 1 (which means there is a total of 1 occurrence for apples on 10/20/21), and
10/21/20 1 (which means there is a total of 1 occurrence1 for apples on 10/21/21).
Any help you can offer is GREATLY appreciated!
Video is awesom.where is excel sheet .
What about when you want the If/OR function to define and use a constant for an equation?? (Division specifically)... I'm getting stuck.
I'm on a MacBook Pro using Excel and I'm using the if/and function and even though the value is true it is returning the false value. I've started a new worksheet and pasted all the data as values and I'm still having the same issue. Here is my formula"
=IF(AND(Table1[@Type]="Other fee",Table1[@Description]="Ad Fee Standard"), "Ad Fee Standard","")
Basically, Table1 has a 'Type' column in that column the word "Other fee" is there, also Table1 a 'Description' column in that column the word "Add Fee Standard" is there - so it should be returning the value Ad Fee Standard, but it is leaving the cell blank. I would welcome any insight, as I'm quite frustrated as to why this will not work.
At the 4:45 mark, you say to get a number it doesnt need quotations. I tried and it gives me a false or true result. I need a formula that will provide the difference amount between two sets of numbers in certain columns. So if a number is greater than another number then the result should formulate the difference amount. Hope that makes sense.
where is the excel download to follow along?
Hi Jon, I saw you touched briefly on the VLOOKUP, I'm having issues sometimes with the VLOOKUP not being able to pick value sometimes even though there is an exact match with no invisible spaces or wrong characters, I wonder if you experienced that as well and what is causing it... The reason I know the target which I'm comparing is an exact match is because when I copy and paste those columns on a new sheet it all of a sudden pick up the value.... I also make sure the formatting is the same or clearing the formats. A similar issue happens on search function sometimes.
May be try to ensure that there are no spaces by using Text to columns under Data bar. It works for me when using vlookup
Hi Benny, are the values numbers or dates? If so, it could be a rounding or decimal place issue. Vlookup requires the values to match exactly, including decimals.
If the values are text then it will likely be a different issue.
We don’t have a public forum but we do have a community forum inside our Elevate Excel Training Program where members can ask questions and post files.
You can also submit a question and sample file at www.excelcampus.com/ask/
Due to the high volume we receive, we can’t promise to answer all questions submitted there but do consider them for future YT videos.
I hope that helps.
How to code a cell that I want two conditions. For example I want the cell to return 1 if
Can someone direct me to a link that talks about two cells comparison? e.g. if A1=1 and if B1=2 then C1=2 and if A1=2 and if B1=1 then C1=1 and so on. I have four conditions for C1. How do I write all of them in one formula?
How I find large 3 salary from a salary list
What if I don't have a table? this won't work for non-table. What if I want to select a range from eg: H6 to H28 and want to show which ones are larger/smaller.
That's why when I tutor people, I always think of an alternative because people might not have the same information as me.
How to use if formula to provide numerical results of difference greater or higher than said amount?
I'm not sure I fully understand your question. Would you mind providing more explanation? Thanks!
HELLO JON,
I want two fill land type with column a , b , c , d. If A is greater then all 3 then type A or if B is greater then A, C and D then type B or if C is greater then A, B and D then Type C or if D is greater then A, B, C then type D in E2 cell.
pls help
Hi Neelesh, It sounds like you can use the MAX function for this. MAX will return the largest value in the range of numbers specified in the function.
I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Hi JON,
Thanks for your response. I don't want to write largest value in the range. I have 3 columns with different number with title A, B, C AND D. I want to write title name instead of values.
If I understand correctly, You could combine the MAX function with a lookup function to lookup the max value and return the adjacent column. Something like the following.
=XLOOKUP(MAX(B1:B10),B1:B10,A1:A10)
I hope that helps. Not sure I fully understand the question.
Couldn't get to that excel sheet.
Greetings. I hope you can help me figure this out. I have numerous customers, all with various charges for our servicer. Is there a formula whereby I can set up a column that will automatically enter the amount charged to each customer when their name is entered into column A, then column B enters the amount we charge them? For example, Smith's Flower Shop pays $50.00 per service and Joe's Auto Shop pays $35.00 for the service we render to him. How can I set up a column that automatically enters the amount each customer pays? I hope that that makes sense 🙂. Thanks in advance for your help.
Hi there! 😊 One way is to use a VLOOKUP. Create a table in the same file but on another worksheet with servicers in the first column and known dollar amounts in the second column. For example, the newly created table is on Sheet1 and takes up Cells A1:B10. Then, in Column B of your original table, use a VLOOKUP function. If your first name is in Cell A2, then in Cell B2 enter something like:
=VLOOKUP(A2,Sheet1!A1:B10,2,FALSE)
That will take the servicer name from Column A ("A2")in the original table, look it up in the newly created table ("Sheet1!A1:B10"), find the corresponding dollar amount ("2" for the second column), and output the exact (FALSE) match to your original table into Column B. Hope that helps! 🙂
8:01 Hi May I know the formula if same company and will total sum of same company amount in the new cell？
Hi Kahshen! If I understand correctly, you probably want to use the SUMIF or SUMIFS function for that. It will calculate the sum of a range based on the criteria in one or more ranges.
We'll add this to our list for future videos. Thanks! 🙂
Hi, why this function dont working =IFS(S6S6>1801,"B", 2800>S6>2251,"C", 3100>S6>2801,"D",S6>3101,"E")
Instead of using two carats with x>Cell>y, try using one for each condition, such as x>Cell. For example, try something like IFS(S6<1800, "A", S6<2251, "B", S6<2801, "C", ...). Any number less than 1800 will be handled by the first condition ("A"), so any other numbers would have to be greater than or equal to 1800 and would be handled by the successive conditions.
I don't see the downloadable file.
Ok Never mind I found it hehe
@@laconan I did not see downloadable file too
I'm trying to use multiple IFs in a single cell. Not IFs nested inside of each other, but rather one IF followed by another and then another and so on. But everything I tried comes up with an error. What should I do?
Hi Burst! 😊 If you want to join the separate IF results together as a string of text, use the ampersand (&) symbol. You may want to separate the results with a space, slash, or pipe ( | ), which can be done like &" | "& in between the IF formulas. Hope that helps! 😊
@@ExcelCampus That worked, thanks.
@@ExcelCampus New question. The IFs I'm using have results that output blank text. I want to make it so that if every IF in the cell outputs blank text, the cell will show "None" instead of appearing blank. Is there a way to do that without nesting those IFs inside another IF?
I don't see the file
Hello! you can download the file from this link www.excelcampus.com/functions/how-to-write-if-function/ under "Download the Excel File" Thanks! 😀
@@ExcelCampusthis link is not working can you send the valid link
Thanks Jon :), i have an issue calculating total sales delivered by delivery truck, i used the sumifs formula but it gave the wrong answer! can I email you the spreadsheet?