Thank you so much for this video! I am using this in an educational setting where the score is equal to a specific classification. I am so tired of having to check the grid EVERY time I write a report and I need to classify 40 scores on a single report! "If you know (about score reports) you know" This is going to save me until I retire! Wish I had it 25 years ago.
Mr. Jopa, Great job, thanks for your insides tutoring, it was very self explanatory and very clear, thanks again. What I am looking is about how to select ( any certain number that belong to that group ) for example, if set group of numbers, A = [ 1, 2, 3, 4, 5...], and then I want to say, IF A HAS ONE OF THESE NUMBERS, IS TRUE STATEMENT, IF NOT IS FALSE STATEMENT. Respectfully; Andy
This is great, thank you. But how do I get this to work but going across a row, rather than copying formula down column? I have rows of data and I want to apply the conditional formatting on each cell in the row, how can I do this without having to have an IF formula for every cell in row? Thank you
Awesome presentation of the IF function. How could place all the same test scores ((e.g., 4.0, 5.0, 6.0, 7,0 ...) from your table into another table column with these same values. e.g. Column K=4.0, L=4.5, M=5.0, N=6.0....... But still, maintain the name order for all the scores on the left-most column? Thanks!
Nice video. How can I remove the unwanted temperature by using If condition in excell? I want to the temperature less than17 degree C and greater than -70 degree C
Hi mate! this is a different scenario for your IF function. What if column A, column B and column C has a dropdown option, and you need them for the result of column D. What would be the formula?
Definitely gets the job done but Is there a reason why you didn't use the =IFS() function which can have multiple criteria and should have the same result but easier?
Glad it helped you! The reason I didn't use the IFS is because it's not available in all Excel versions. If you have the most recent versions, you can use IFS, but if you have all the other versions, you can't. I'm always trying to show things that most people can do. 🙏👍
What if the value is the other way around? For example, I want to change the letter grade to a number. It's when I calculate for GPA. Let's say A=4, B=3, C=2 and D=1. I have letter grades (alpha, not numeric) that I need to change to numbers. How do I do that?
How to best solve the following problem? I have two workbooks, WB1 and WB2. In WB1, If M6 in WB1 is equal to K3 in WB2, I want M7 in WB1 to have the same information in Q7 on WB2. Thanks.
I understand you wanted to demonstrate how to use nested IF function but honestly speaking this is such a classic problem for VLOOKUP with TRUE in the 4th argument. The solution could be so much easier by a single formula rather than having so many nested functions.
I am placing/writing a formula in cell C1 In A1 - Its EXPIRY DATE In B1 - Its RENEWAL DATE =IF(TODAY()>=B1,"SEND RENEW REMINDER","WAIT") The above formula is working. Now, I want to apply one more condition in same cell C1 - That if A1 is less than TODAY's date, the result should be "PENDING" and if I write manually (CANCELLED) in A1, the result should be "CANCELLED". Something like =IF(TODAY()
We did this exercise today on the spot. I use ";" for the next "IF". 😂 Doesn't work. New version is "," 😁. Thank goodness I got it before the time ends 😂😂😂
I get lost sometimes too. Sometimes it is a comma or semicolon. And if it's numbers, to separate the decimal places, sometimes it's a "point" and sometimes it's "comma". 🤣
Sir, if 1st condition or 2nd condition match than write A1 or A2 please provide formula for this. (any one condition match) write A1 no condition match write b2. please reply.
Yes, I'm definitely. I have a video about logic functions, maybe it can help you: th-cam.com/video/EI74kor9e80/w-d-xo.html But anyway, I'm going to make a more specific video about this, I think it's very important. The AND and also the OR, can be used with the IF function to amplify, increase the number of criteria you can use.
You got me there... But, searching on Google, it's says you can specify up to 127 range/criteria pairs in your formulas. Or until the computer crashes lol
Hey, Daniel! Thanks for the question. Look, if you use as the criteria a number, that you entered manually, you do not need to lock the reference as the criteria will not change. It will always be the same value. But, on the other hand, if you select a value to use as a criteria that is in another cell, you need to lock that cell ($..$) so that the reference always remains in the same location.
thank you , sir@@JopaExcel . Let's I choose to use a criteria as a number (50) entered manually, is it a must to write it like "50", just 50 can do? when are we supposed to use "..." ?
Do you want to use IF function with only two criteria? Here's the video: th-cam.com/video/tNvSlT4KSPY/w-d-xo.html You can also use IF function with more criteria to check a value, heres: th-cam.com/video/Kk33ubBIhdY/w-d-xo.html Another thing, be more respectful, please. No one owes you anything.
I like your explanations. I went for classroom based course but you make it clear for me after all the confusions.
Thanks basing on your video I was able to figure out how to develop a worksheet's rubrics in the opposite way around ! Huggggs
THANK YOU so much!!!! Been searching everywhere and finally found something that made sense and was simple!
I literally did my class assignment with the help of this tutorial and finally got it right .., thank you !!
Oh, really? That's amazing! Thanks for the feedback. I'm glad this helped you. 🙏👍
Thank you so much! Great video! Solution to my problem! 🤩
Thank you so much for this video! I am using this in an educational setting where the score is equal to a specific classification. I am so tired of having to check the grid EVERY time I write a report and I need to classify 40 scores on a single report! "If you know (about score reports) you know" This is going to save me until I retire! Wish I had it 25 years ago.
I'm glad it was useful. Thank you for the feedback. Excel has a lot of functions that can save time. I'm glad this one helped you 🙏👍
Thanx, i was looking for this example for long time. Finally i got it 💯👌
Mr. Jopa,
Great job, thanks for your insides tutoring, it was very self explanatory and very clear, thanks again. What I am looking is about how to select ( any certain number that belong to that group ) for example, if set group of numbers, A = [ 1, 2, 3, 4, 5...], and then I want to say, IF A HAS ONE OF THESE NUMBERS, IS TRUE STATEMENT, IF NOT IS FALSE STATEMENT.
Respectfully;
Andy
You're a life saver. Thanks alot ❤
Thankyou sooo much 🎉 after repeated mistakes now I properly understood about nested if just in your one video. It was damn helpfull. 😊
I'm glad it was useful! Thanks for the feedback 🙏👍
it really work for me for ranging less than and greater than Gross tonnage calculation. thank you so much
Very helpful! Thank you!
Thank you so much this is exact formula I needed
Finally I have found a very useful video
Thank you
This is great, thank you. But how do I get this to work but going across a row, rather than copying formula down column? I have rows of data and I want to apply the conditional formatting on each cell in the row, how can I do this without having to have an IF formula for every cell in row? Thank you
I'd like to know it too
That this video saves minutes is absolute
Great... Thank you so much... its really very easy....
Glad it was helpful! 🙏👍
thank you so much, finally i found your video is useful
Thanks!
hi there.. thank you for the tutorial.. your guide is very is useful.. tq
Finally find something
What i need actually
❤❤❤❤❤
Well defined. Very helpful. Keep uploading these kind of more helpful videos.
thank you sir.. Hopefully you can also tutor us another vid using if functions that uses computation.
Thanks for the comment. 🙏👍 I'll definitely bring more videos on the subject.
Awesome presentation of the IF function. How could place all the same test scores ((e.g., 4.0, 5.0, 6.0, 7,0 ...) from your table into another table column with these same values. e.g. Column K=4.0, L=4.5, M=5.0, N=6.0....... But still, maintain the name order for all the scores on the left-most column? Thanks!
Thanks bro really its working for me😇
Clearly defined. Thanks.
Thanks for posting, it really help me alot. thanks again
It's a really good explanation.thank you so much
at least, i have found this capabilities of Excel!!!
You solved my problem😂 thank you.
I'm glad it helped you. 🙏👍
It was indeed very useful and informative
this has been very useful to me, thank you so much
Good 👍understand thanks
You are the best!!!❤❤❤
A nice example of nesting! However, my Excel requires that semicolons be used rather than commas.
Nice video. How can I remove the unwanted temperature by using If condition in excell? I want to the temperature less than17 degree C and greater than -70 degree C
Bro Eady and Helpful thanks
For added value, please always share the excel sheet used in the video.
Hi mate! this is a different scenario for your IF function. What if column A, column B and column C has a dropdown option, and you need them for the result of column D. What would be the formula?
Fantastic
Sir please the software you used to draw coloured boxes and arrows,pls again.
Definitely gets the job done but Is there a reason why you didn't use the =IFS() function which can have multiple criteria and should have the same result but easier?
Glad it helped you! The reason I didn't use the IFS is because it's not available in all Excel versions. If you have the most recent versions, you can use IFS, but if you have all the other versions, you can't. I'm always trying to show things that most people can do. 🙏👍
@@JopaExcelIts true in my excel there is no IFS function, the video helped me.
Thanks bro!!!!
What if the value is the other way around? For example, I want to change the letter grade to a number. It's when I calculate for GPA. Let's say A=4, B=3, C=2 and D=1. I have letter grades (alpha, not numeric) that I need to change to numbers. How do I do that?
What software are you using for video editing and screen recording?
How to best solve the following problem? I have two workbooks, WB1 and WB2. In WB1, If M6 in WB1 is equal to K3 in WB2, I want M7 in WB1 to have the same information in Q7 on WB2. Thanks.
Nic explanations
I understand you wanted to demonstrate how to use nested IF function but honestly speaking this is such a classic problem for VLOOKUP with TRUE in the 4th argument. The solution could be so much easier by a single formula rather than having so many nested functions.
Can give a SUM FUNCTIONS SKILLS SUMMARY EXCEL EXERCISE?
I am placing/writing a formula in cell C1
In A1 - Its EXPIRY DATE
In B1 - Its RENEWAL DATE
=IF(TODAY()>=B1,"SEND RENEW REMINDER","WAIT")
The above formula is working.
Now, I want to apply one more condition in same cell C1 - That if A1 is less than TODAY's date, the result should be "PENDING" and if I write manually (CANCELLED) in A1, the result should be "CANCELLED". Something like =IF(TODAY()
thank you
What did you press to lock K2 cell sir?
F4 key, sir! 🙏👍 Or you can manually input the dollar sign $.
Hi, how to create a formulae, eg: if in Column A1 is 1, therefore the Answer shall be the same as shown in it.
is it possible we use nested IF here ? I don't understand this cell locking of a thing...pls, what is the essence of locking cells ?
Yes nested if can also be used
The IFS function is much simpler when working with multiple values.
I tried it but it only outputs the first if and the last else it doesn’t consider the other if conditions I don’t know why
👌
We did this exercise today on the spot. I use ";" for the next "IF". 😂 Doesn't work. New version is "," 😁. Thank goodness I got it before the time ends 😂😂😂
In English I always used commas. I believe it should change mainly due to the language... But I'm glad it worked! Thanks for the feedback
I get lost sometimes too. Sometimes it is a comma or semicolon. And if it's numbers, to separate the decimal places, sometimes it's a "point" and sometimes it's "comma". 🤣
Sir, if 1st condition or 2nd condition match than write A1 or A2 please provide formula for this.
(any one condition match) write A1
no condition match write b2.
please reply.
Can we use IF & AND functions for multiple conditions?
Yes, I'm definitely.
I have a video about logic functions, maybe it can help you: th-cam.com/video/EI74kor9e80/w-d-xo.html
But anyway, I'm going to make a more specific video about this, I think it's very important.
The AND and also the OR, can be used with the IF function to amplify, increase the number of criteria you can use.
HI, IT IS OK IF THE LAST CRITERIA WE ALSO MAKE IT IN THE NEST?
100%, sir. You can nest everything. I'm serious, if it works, it is right. 🙏👍
@@JopaExcel ok noted Sir.. :)
Sir, how much if conditions can add in one formula
You got me there... But, searching on Google, it's says you can specify up to 127 range/criteria pairs in your formulas. Or until the computer crashes lol
What formula will be used in this?
A---------->=15
B----------=60
C-----------=120
D----------
What is the difference between using $..$(absolute reference) and simply typing a number?
Hey, Daniel! Thanks for the question. Look, if you use as the criteria a number, that you entered manually, you do not need to lock the reference as the criteria will not change. It will always be the same value.
But, on the other hand, if you select a value to use as a criteria that is in another cell, you need to lock that cell ($..$) so that the reference always remains in the same location.
thank you , sir@@JopaExcel . Let's I choose to use a criteria as a number (50) entered manually, is it a must to write it like "50", just 50 can do?
when are we supposed to use "..." ?
With number you dont need to use quotations. However, if it is a text, you need to use them. 👍
500000 monthly sales. Give the excel formula to convert this amount into 30 days. Do not repeat the amount
kindly provide excel sheet
❤
Thanks! 🙏👍
Can you upload this excel sheet here? would have helped
why is excel so difficult
Why don't u select L instead of typing
napendto
everybody teaches exactly the same thing. no one can teach how to get two returns? Damn!
Do you want to use IF function with only two criteria? Here's the video: th-cam.com/video/tNvSlT4KSPY/w-d-xo.html
You can also use IF function with more criteria to check a value, heres: th-cam.com/video/Kk33ubBIhdY/w-d-xo.html
Another thing, be more respectful, please. No one owes you anything.
@@JopaExcel Thanks! Just was expressing my frustration bro. Don't meant to be disrespectful. Sorry if I sounded so...
@@JopaExcel I have two if formulas, each one with different "if true" and "if false" values. Is it possible to combine them?
Thank you
🙏👍
Thank you