Spreadsheets #3: IF Statements
Vložit
- čas přidán 5. 08. 2024
- How to use simple and nested IF statements in an Excel, OpenOffice Calc or LibreOffice Calc spreadsheet, including the incorporation of AND and OR functions. This video follows on from my first two spreadsheet tutorials:
Spreadsheets #1: Introduction:
• Spreadsheets #1: Intro...
Spreadsheets #2: Absolute & Relative Cell Addresses:
• Spreadsheets #2: Absol...
More videos on computing-related topics can be found at: / explainingcomputers
You may also like my ExplainingTheFuture channel at: / explainingthefuture
He has all three traits. To be a good teacher, you must have all three skills. First, know the material, second, be able to teach others, and third, have good communication skills. He ticks all of those boxes. Well done!!!!!!!
Watched 30 minutes of spreadsheet videos with no answer to what I wanted to know. watched 2 minutes of Explaining Computers, got my answer.
Well done Christopher, you make easy learning for young and old alike... wish there was more teachers like you in our schools both in the UK and around the world too. It would be great if you had a place where your students could go to too download your examples as well as give feed back too. Take care and keep up the great work👍
Many thanks for your kind feedback.
Excellent series of videos Christopher, I'm going to make the new guy at work watch these and then perhaps he may start to live up to the claims he made on CV!
Thank you for these spreadsheet videos! I'm appreciative for your time and knowledge.
Wicked! Was looking forward to the next video in the series, well done!
If only I knew how much potential Spreadsheets can have, I would've started learning them a long time ago.
Looking forward to seeing more of the world of Spreadsheets. :)
Fantastic tutorial Christopher, Thank you for your enlightening videeos!
Thank-you for making this video. It helped me solve a problem I was stuck on.
Thank you. Awesome! I was not even looking for $G$6 technique, but that was also VERY useful.
Christopher actually makes learning fun and relative.
Absolutely!
I wish Christopher had been my teacher all those years ago. I especially like how thorough he is while seeming to keep things simple. The links to earlier related videos in the notes box above is very welcome. I wonder if CZcams might allow established content creators to publish downloadables such as worksheets and pdf's that could accompany the video? Thank you again for another top video, Christopher. I wish you a great week.
great idea and I agree
+havingMC Thank you.
+Kevin H Thanks Kevin. A very interesting idea indeed.
A github repository would be cool idea for stuff like this.
Clear, concise, thanks !!
This is some real education I never got in school.
Wowzer this is another video. And yes, very exciting! Thank you, Chris!
Greetings!
These videos are amazing ! and so much easy to understand. Please I request you to do more spreadsheets videos! You're great !
Thanks.
These were so helpful. Thanks so much.
Thank you much for posting this (and parts 1 and 2). You teach well and explain in easy terms.
Thanks for watching!
Sir, I enjoyed your videos. It's like I am in classroom, face-to-face with you. Thanks.
Thx so much for your easy to understand explanation! was able to implemented two nested ifs after watching.
Excellent. My spreadsheet videos have been my least popular, so I am glad to hear one of them has helped you. :)
its incredible technology and you explained this smart computer trips very effectively, thank you so much
Thank you so much!! It's very clear and easy to understand. Looking forward to more tutorials!
Thanks for this! :) Sadly very few people are watching my spreadsheet videos.
@@ExplainingComputers we are many
An excellent presentation. Thank you
Thank you very much! Very usefull and right to the point tutorials, hope you will continue them! :)
Thanks for this. Sadly these spreadsheet videos have not proved popular -- even though I really liked them! :) But maybe, following your feedback, I will do some more.
One note on the syntax in MS Excel: function arguments separator actually depends on your regional settings. In Polish MS Excel we use ; as the separator. Reason for that is that the , is decimal mark in our language. So to calculate the square of 3.141 we use =POTĘGA(3,141; 2).
You are awesome...
This is how to explain something to anyone..
Please make some more videos on spreasheet..
My humble request..
Best video on open office, very inspiring, thank you.
Glad you liked it!
Thank you sir you make it easy for me to understand ..Thank you again
Very clear. Not easy to explain. Well done!
Just learning. Spread sheets at home myself and you Are very clear to Keep up With and not. Complicated thank you This is great For me. 👍👍👍😊
Glad it was helpful!
This is what I need I only have spreadsheet like this not excel. Thank you
Now I actually know what a spreadsheet is for...
Thanks for yet another helpful totorial.
Glad it was helpful!
This was very useful
Good work sir
Wow, you've made this so painless for a beginner. You are my new favorite geek ..and I mean that in the most endearing terms :) I realize these are older videos but I'm wondering if you'll be making more on Calc spreadsheets? I have your 3 posted videos in my playlist and already hungry for more.
Thanks so much for the generous contribution of your time and knowledge. Much appreciated!
Thanks for this. I enjoyed making the spreadsheet videos, and had many more planned. But sadly they have not proved that popular. But perhaps I will make a few more anyway. Your feedback encourages me to do so! :)
It's hard to believe yours aren't the most popular Calc vids on CZcams. After seeing some of the others, I've stopped wasting my time on them. Thanks so much for considering some more Calc follow-ups. I'll certainly be watching for them!
Thank you its very help ful
Thank you! I wonder who invented the spreadsheet. That person actually found a way to make computers useful! I would add that I hope we can give Sledge some encouragement. The bloke just had a bad year is all. As do we all right from time to time.
Poor old Sledge. :) In terms of software, the spreadsheet very much started the personal computer revolution, with the spreadsheet being VisiCalc for the Apple II in 1979. As you say, for the first time, it made a computer useful for many people.
@@ExplainingComputersI see, ta. I used an Apple II equipped with an Analog to Digital converter to collect data. Changed my life. I didn't own a Lisa but bought a 125k Mac as soon as they came out. I still have it!
better than the school teachers ngl-
How would you call the 2nd cell; and turn it's BACKGRND yellow? Thank you!
Thank u to the moon and back
:)
How Many times can i catch your videos early? Nice video BTW
if i had a condition where one cell had to equal 5 and 2 different cells had to equal a 1 to produce a 1 how would i do that so it can increase like 10, 2 & 2 = 2, but 7, 2 & 2 = 1
Please where can i find the rest of these videos. please help Im really learning a lot from it.
Thank u so much this helped me so much.
I am glad to hear this! :)
Adrian and Sledge are such underachievers.
Thank you so much!
You're welcome!
Brilliant tutorial many thanks. Can you tell me how to do this please. I have an interactive quiz scoreboard (Open office Calc) that keeps track of individual team cumulative scores over 10 rounds of questions. Is it possible using the IF function to get the top three scoring team names after each round to appear automatically. EG If the top 3 team scores after any round are greater than the rest to display the top 3 teams and their respective scores
This could probably be automated (to a point) -- eg using a macro to perform a data sort.
You are a savior. Thank you sir. My only question is for the inclusion of extra bracket in the end. Will the inclusion or noninclusion of it will result in the change of outcome?
Thanks. If you open two IF statements, you need brackets on the end to close each of them. Without them, it will not work.
@@ExplainingComputers Appreciate for getting back to me. Subscribed 🙏🏼. Sir, if time ever allows then could you please bother to make a short video on how credit and debit transaction formula to be applied in a spreadsheet?
thank you very much
thank you u are great teacher
Thank you! 😃
It would be nice to have something like a switch / case instead of having to nest the if clauses. The case construct typically only tests for equivalence but there are tricks to make them work with ranges.
There is another way to do things like making decisions against ranges of values though. In treating the spreadsheet as a relational database, I created a couple of lists to serve as lookup tables, each with a column as an index to act as a foreign key and primary key pairing.
I used the MATCH function to enforce integrity, and used the ISNA to handle incomplete fields. A third array can hold analysis of the scores by range (tying into the lookup range array).
e.g with D6..21 holding an index value (1..4) created by the MATCH into the lookup list I14..I17 where Fail=1, Pass=2, Merit=3, Distinction=4.
. =FREQUENCY(D6:D21,I14:I17) will show a count against each result, Pass, Merit etc.
It works, and allows lookup values to be changed without messing about with nested IF statements.
@ 02:06, you clearly, for LbeOff Calc, show use ' ; ' between statmnts. However, my system auto-chnges that to ' , '. Whatz.up?
Thank you
How do you add the $ signs on Mac?
I am using MacOS Mojava version 10.14.6
On Apache OpenOffice 4.1.8
I am working on spreadsheet. I copy and past my 2020 spread sheet into a new file naming it 2021 records, now I can not open the old file, why?
when I click the old file a box comes up it says
ASCII Filter Options:
Properties
Characters Set drop down box Western Eu Dos/OS2 OK OK
Default fonts drop down box Wassem Cancel
Language dropdown box English (USA ) Help
Paragraph brake O CR & LF O CR O LF
Just the video I need as I switched from Excell to Open Office. But now I need some help. I would like to figure out my elect bill. If the unit of usage is 200 units or less then the charge per unit is $0.13. If the usage is between 201 and 800 until used, then the first 200 is subtracted from the total. Whatever that total is then the charge per until is $0.1085 per until. And finally, if the charge is over 1000 then it's minus 200 and 800 respectively, then the remainder is $0.121. I have it figured out so far, but it's not right.
What version of OpenOffice is this? I know it's from 7 years ago. I'm actually looking for this version but mention of Version... oh well.
Hi Christopher, thank you again for a great video.
If you got time, and if you are up for it, it would be super if you could do the same kind of videos for Access or the Libra office program that is similar. I would really like to get better at working with database editing and creation.
Keep up the good work :)
+Stoiss Thanks for this kind feedback. A database series is a great idea -- I used to teach that as well! :) It just depends how well the spreadsheet series does . . .
+ExplainingComputers Nice :) Sure it's all about the viewers, so i hope everything goes well for the spreadsheet series.
I know there is a lot of different guides and "Prophets" on youtube, but i like your approach to the different office distributions and pc education in general. So i hope you will do it eventually, and maybe other office programs :)
Is it possible to say "=if(A1=1 OR 2; "true"; "false" )? I want to switch the 1 and 2 with strings inside the cells and then let the calc do the calculation. So, If A1 is either "banana" OR "apple" say "too expensive" for example.
The IF statement compares on thing to another, so you need to use nested IFs as I show in the video to handle what in effect here are multiple conditions.
I need A2 to hold the highest input of B2 over time where a new input into B2 is entered daily. So as an IF statement how would I do that?
I imagine an ">" check
=IF(B2>A2,A2=B2,?)
and if > then is "yes" you make the change in the value of A2 but if the result is "no" how do I retain the current value in A2?
What is the directive for "no change"??
Thanks much :)
..
A spreadsheet can only work from data it holds -- what you are asking is for a cell to contain the maximum value of a range of data, most of which you have deleted! You will have to enter your B2 data into a range, and then A2 can contain @MAX(B2..B100), or however large your range is.
hmm yes if i use the max function i would be asking it to formulate off deleted data but..
I simply want it to retain its current value if the check returns as NO and I have no idea how to do that.
I've tried storing the current value in a third column and then referencing it but it doesn't like that loop.
Is there no directive to simply "remain unchanged"?
thanks
..
Thanks
tanks teacher
Now I need to download open office to try this for myself. Thanks!
+John Ratko If your Raspberry Pi has a recent version of Raspbian installed, you will have Libre Office Calc on there and can try this straight away! :)
ExplainingComputers Raspberry Pi is on my bucket list. For now I'll have to settle for working on my Windows laptop since I'm..um..financially challenged.
+John Ratko Sorry, I made an assumption following your comment on me Pi printer setup video. Calc will of course work fine on your Windows laptop. :)
A good way too start learning about programing, I've just got ras pi too play with.
+Darren McManus Thanks. :) This afternoon I have been working on some new Raspberry Pi videos, writing code to read and control GPIO pins. So keep watching! :)
In Excel 2016 (and I believe 2013 as well) the notation is IF(condition; THEN_VAL; ELSE_VAL) as well. So a semi-colon, not a comma.
+Bas Groothedde The Microsoft support page referring to Excel 2016 down to 2010 and below indicates the use of commas: support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
ExplainingComputers that is rather special, considering I cannot get a comma to work on my installation. This bugs me as a software engineer, as I am used to commas for parameter separation. It truly is a semi colon here on all my systems. UK language set
ExplainingComputers Here's more information; superuser.com/questions/509742/if-function-in-excel-not-accepting-commas. If the system uses commas rather than points for decimal points, it will not allow commas in parameter separation. It will then default to semi colon.
It's something to remember, I guess. Many countries use a comma for decimal points.
+Bas Groothedde Very interesting. I've read around a bit on the decimal points and commas issue now -- and which may explain why OpenOffice and others have always used the semicolon. It is indeed something to remember. We have both it seems learned something new today! :)
ExplainingComputers Indeed we have! I also like how OpenOffice standardized the use of the semi colon. I wish MS did the same.
Thanks for the video!
The main problem I have with spreadsheets both Open Office and Microsoft Works Spreadsheet is when I download data from my bank account or credit card and try to simply add all numbers in a column, the result is always 0, I have no idea why and it never works and no formatting to numbers or currency or general or anything else helps. Copy and paste the whole column and trying in different spreadsheet - same result - it works sometimes but no idea why sometimes yes and sometimes no.
Almost certainly s because the data you have downloaded is text, and if you sum a column of text entries, you will indeed get zero. Formatting to currency does not change the data in the cell, just how it is displayed (ie text formatted as currency is still text).
@@ExplainingComputers I think yes, you are right. And what is the solution? You import data, there are nothing but figures in one particular column and the software regards it as text, cannot recognize it is numbers and cannot add it. That sounds stupid.
Last example showed the same thing, why use OR over AND or vice versa?
I am just showing alternatives to introduce different functions.
thanks...
This is actually what activates the Eastereggs when certain conditions are met, don't know where the actual Eastereggs is hidden maybe one of the *.dll files in Microsoft software which I have stopped using.
You made this 2016, i am watching in 2022
Hi could you add more samples.....
Using Ubuntu:absolute reference is not Shift +F4 it is F4 only
👍
How to give rank based on mark
Highlight all of the data, select "Data" and "Sort from the menu/ribbon, and select the appropriate column to sort on (the one containing the mark).
hi, great videos, im and "old" computer user, since 97 (windows 3.1/95 days), and a linux user since 2000´s, just for trying different UI´s, your last openffice calc (staroffice) libreoffice has improved a lot, you could make a review when 6.0 came out together with ubuntu 18.04 (www.omgubuntu.co.uk/2016/04/ubuntu-infographic). (many mesa, vulkan, SDL & other graphics/gaming related improvements current being developed will came in 18.04); steam, xfce too :), blender 2.8, krita, etc, "just a few ideas" :). next year, your other recent open source video, kdenlive it has improved too.
I suppose you are a part time GNU/free software user, as I said i´ve been using gnu linux for quite a while, not the mainstream OS (servers, and android devices don´t count), although we have very competitive programs like blender or krita, we still have a lot of lacks, for example, a true alternative to adobe suite, in games it has improved a lot, also it would be great a video about gaming in linux.
great projects that are being funded
www.patreon.com/godotengine
www.patreon.com/solus
www.patreon.com/Nekotekina
www.patreon.com/libretro
great channel, thank you for gnu/open source/free software videos
facebook.com/groups/xfce.lxde/ invited
Poor Adrien
Very helpful, I’m waiting for more videos, maybe how use vlookup
☺️
Thanks. You prompt me to add more episode to this series. :)
Any chance for zoom meeting . I sarted new job and have lots problem
This is an amazing series, it has helped me a lot. But can I say one pet peeve of mine, can you please stop saying "ex-etera"? The word is "etcetera" hence "etc" not "ext".
+OhNoNotMyPenis Well, you sound like a defensive bitch, and even worse, defending people that can answer for themselves just fine. You should become a lawyer, or try defending when addressed to. As your reply, I'm not American, nor British. Saying exetera is not a matter of regional pronunciation. It's a common error. Like people typing should of. Do you think should of is an American grammar difference and not an error too?
How do you attain IF B1 [NOT EQUAL TO] A1 ...? Thanks
Use for not equal to.
@@ExplainingComputers Thank you for responding!
Just copied one column from Open Office to Works and half of the figures were changed and altered into datum.
He's wrong about the semi colon and comma. Libre office uses the comma and not the semicolon. It will work but it will replace it with a comma.
Please help! I have 2 criterias and i cannot figure out the formula.
Criteria 1 = A1=DOG
Criteria 2 = B1 contains the word "FB" within it.
The two criteria are =IF(A1="dog") and =IF(isnumber(search("FB";B1)))
I need these as an AND function. =IF(AND(A1="dog";(isnumber(search("FB";B1))) - This is correct
The problem is, when I enter the 'then value' and 'otherwise value', I dont know where to put these.
Also I need a third criteria for A1 does NOT contain DOG, and B1 DOES NOT contain the word FB.
I want the options to be "mother", "father", "baby"
So in total 3 options.
1 = A1 = dog, and B1 contains the word FB. = "mother"
2 = A1 = dog, B1 DOES NOT contain word FB = "father"
3 = A1 = cat, B1 does not contains word FB = "baby"
Can anyone help? lol
Done it hahahah
For anyone interested, I did:
=IF(A1="CAT";"baby";IF(ISNUMBER(SEARCH("FB";B1));"mother";IF(A1="DOG";"father)))
BOSS
OWP+++
first
Not gonna be rude cause this guy put up a really good video but my boy over here looking like petelguse from re zero. He just needs to get the green hair and take off his glasses