Learn How To Use the IF Function In Numbers
HTML-код
- Опубликовано: 6 сен 2024
- macmost.com/e-... The IF function is how to test values in spreadsheets. You can use it to simply test a value and show different results. But the key to making complex spreadsheets to learning more about the IF function. You can pass through values when a condition is met, combine conditions with AND and OR functions, and nest IF functions for more than two possible results.
FOLLOW MACMOST FOR NEW TUTORIALS EVERY DAY
▶︎ RUclips - macmost.com/j-y... (And hit the 🔔)
▶︎ Weekly Email Newsletter - macmost.com/ne... ✉️
▶︎ Twitter - / rosenz and / macmost 🐦
▶︎ Facebook - / macmost 👍
SUPPORT MACMOST AT PATREON
▶︎ macmost.com/pa... ❤️
#macmost #numbers #macnumbers
How can i add for example color red to the text if the result of the function becomes 'no'?
Use Conditional Highlighting. ruclips.net/video/m5QkGYvfYRk/видео.html
Let's, I can read the help section on my Mac for an hour and come away still not knowing anything, or I can watch your video for 9 minutes and learn a whole lot of useful knowledge. (almost sounds like an IF statement)
That is so true, Gary explains Mac Apps so simply & effectively.
I learnt how to do ‘IF’ formulas over 20 years ago in Excel, but I haven’t used them for many years, so I wanted to refresh my memory, now that I’ve just started using numbers. Apple have done a great job, making the creation of these formulas, much more simple and you did a great job of explaining it. With Excel there was a lot of tying parentheses and it got messy when you wanted to nest them. This is much cleaner. Thanks for the tutorial.
Gary, I can't thank you enough!!!! You're a huge help to me, glad your on here provided this valuable visual and clear instructive help!!! Thanks again!!!!
What "IF" I want to do Wins Losses for my children softball teams? Am I on the wrong program or am I not doing it right? I want to list the score from both teams, Team A 9, Team B 5, I'd like it to calculate the winner/loser, and add a point to their Wins/Losses stats. Does that make sense?
I don't know this is checked any more. GREAT VIDEO! I was struggling with a spreadsheet, this video gets to the point quickly and clearly. Thanks for planning it so throughly as well. All that being said, Is there a way to return YES and NO responses with BLACK print ( font size as well) and RED print and font for YES? Thanks in advance.
Yes. Use conditional highlighting. ruclips.net/video/-4wn6-f4gRg/видео.html
Hi Gary, can you please tell me how to do IF (cell number is “non-numeric) THEN (numeric formula)? Thank you
Use ISNUMBER to test a cell for whether it is a number or not. So NOT(ISNUMBER(cellreference)) would do the opposite.
I've been racking my brain with nested IFs...this video cleared it up, thank you!
Thank you so much Gary for being my Mac Guru.
I just want to mention that when you moved the formula for "Test Based on Calculation ( 3rd sample) to the bottom of the screen, the CC covered it. Just a friendly FYI.
Great video Gary
Always so helpful with numbers! Keep em coming
Excellent, that is clearer than I could have explained it myself. Well done!
You are awesome...you just helped me do the one thing I could not figure out...thank you.
Wonderful, thanks Gary! I really love your Numbers tutorials.
But how about if i wanted to use the same forumal in multiple rows without changing the formula each time
This is great Gary. As a newbie to numbers it is very helpful, but I have to confess, I don't fully understand it. I have tried repeatedly to use the "if " and the "and" commands but no matter what I do, I get a syntax error. I am basically trying to enter the following formula/condition in all cells in column C .If you or any followers could put this right I would be most grateful. Thank you.
if c18 equals “Home” then e18 equals d18 x..411 and if C18 is blank then e18 is left blank too
What is the text of the formula you are using that gives you the error? Hard to know where you could be going wrong without seeing what you have.
@@macmost Hi Gary. Thanks for your kind an very prompt response. i am writing =if c18"home" e18 = d18 x .441 if c18 "" e18="" Like I said, I am a newbie to this and the formula and the if statement is clearly all wrong. I am trying to compile a spreadsheet that shows my electrical costings on charging an electric car. When I charge at a public charging station I enter the location in column c and enter the Kw and price in columns D and E. No formula is required. But in column E I want to fill the column with a formula that only kicks in when I write "Home"( in column C) for the location so that I can multiply the kilowatts used by the rate of .441p to get my costs. But for rows yet to be filled in, column C will have no entry, so I don't want any entry shown in column E. Does that make sense? Many thanks for your time in considering this
@@lisreynolds9180 Is that what you have? =if c18"home" e18 = d18 x .441 if c18 "" e18="" ??? That doesn't even look like a formula. You left out all of the parenthesis. Look at the examples in the video closely to see how it is done.
Thanks brother, when I can't easily find it, you always have the answer. So well explained. Peace, Love and Cheers!... No if Mac could only plot a natal chart, Mac as most systems don't use polar plots ie Angle vs length or lat/long etc.
Hi,
At 4:54 when you check the box in cell A2 and it shows the number 10 in B2 can you add the number 10 with another number? The reason why I ask is because I made a google sheet and I am trying to figure out how I can get this function =IF(J2=TRUE,"$2.99","0.00") and =IF(J3=TRUE,"$6.41","0.00") to be added in a total on my spread sheet???Please help or offer advice. Thanks!
Never mind lol I found out about the term "boolean".
Amazing Video. Thanks so much. Very informative and you saved me so much money and time on a course to fix such a big problem I had.
Very Grateful ❤
Thank you, thank you, thank you!
thanks
Great video, Thank you! Exactly what I needed
Trying find if there is an IF formula or any formula where any text data inputted in one worksheet will automatically be added into another worksheet ? Thank you
A formula can't force another empty cell to suddenly have a value. Formulas work on the current cell and pull values from other cells to give a calculation result.
@@macmost thanks for clarifying
Very helpful, thanks a lot!
So I could use this simple what if formula for a check register on a pages spread sheet? I am trying to do credit OR debit and get the current balance. Thank you for the simple video :-)
Thank you! Great videos! Quick question. How to I assign numerical values to a name? For example, I'm doing a Nutritional List where "Wings" is the name the row, and the values are Carbs = 13, Protein = 40, Fat = 30, Calories = 506. So when I type "Wings" to a row, all the values automatically fill in the columns of Carbs, Protein, Fat, and Calories.
Use the LOOKUP function. ruclips.net/video/JMYHK5FZUjU/видео.html
@@macmost Thank you! You rock!
Hey Gary just found the channel. Is it possible to create an If function to add a new table that’s prefilled with headers.
Basically the idea is to have a block that will ask how many people and add a table below that that contains one row with columns for full name, address and phone number headers . I want to have the if function work by if I put “2” people in the how many people slot. A pre filled ready to go table will appear below the the previous one table and appear completely separate. I am basically wanting negative space between each person and not have a solid grid. I know this may be complicated but any help would be appreciated
How can I get a higher, lower or equal result ? For example win lose or draw in soccer?
Just compare two numbers with =, < or > symbols.
IF(M64=0,M64,IF(AND(M64=15,P64),(M64*0.80)-2.12,M64*0.80)))
I continue to receive an error stating that I have too many arguments. Everything works as it should until I attempt to next the second "IF/AND" statement. Greatly appreciate any help available.
Brilliant, Gary! Thank you!
How can I do more than just one thing when something is tested? Like
"IF A6;
DO B6 = 5, B7 = 9
ELSE B6 = "", B7 = "";
A formula in a spreadsheet doesn't reach out and change the value of OTHER cells. It calculates a result and displays a value in the cell with the formula. So in this case you'd need a formula in B6 to check the value of A6 like IF(A6,5,"") and another formula in B7 to also check the value of A6 like IF(A6,9,"")
Question: ref.@02:58 (“Too High”)
As this example shows the text entered here as a “hard-coded” string and so for lack of a better term “private” within this formula/function, I was wondering (and searching for hours) if/how it might be possible to select (or fill) this “if-true” parameter with the string value from user defined “words” populating a range of cells on a seperate table or even on another sheet?
For example: If I wanted to instead define “Too Fast” rather than “Too High” could I have the new sheet:
CellRef./Value(“String”) *col B for easier tracking
B1=“Too High”
B2=“Too Low”
B3=“Too Fast”
B4=“Too Slow”
and then have something like this on my “front-end” destination cell:
(Pseudo): IF(A2+A3>10,”(Sheet2(Table1_Data(B3))),(A2+A3))
or
_Data(“B3”)
or
_Data(TEXT(B3)) or (VALUE”B3”)
or
Ctrl+Alt+Del and go to the beach?
Please Help Me I’m losing my lollies
You can certainly refer to other cells inside of functions. Not sure what you are trying to show me here. Just click on the cell instead of typing to have the cell reference inserted.
@@macmost just pretend this is a forehead slap emoji and i’m just sending it to myself! - thanks U
Is there any way I can, for example, say if cell B1 has this "text," then this cell will show the following "text" or "word"? Its text, not numbers in my particular need.
Excellent video, by the way!
Yes, sure. Try it.
I am learning so much from your videos, but still cannot find just what I need for my timesheet. Is it possible to have IF function to say “Yes” or “No” from checking entire column (except header) for words written in BOLD or cursive (italic) style?
And then to print out a number in a cell of how many “Yes” there were in a row or column.
Please help me with this :)
Functions can't see the style used by a cell. So use something else, like a column with checkboxes.
@@macmost Thank you, again! I have a similar question - how can I calculate how times a certain letter (multiple choice answer, e.g) appears in a column?
@@talktodayusa Look at the COUNTIF function
thank you, i am newbi btw.
Are there any formulas for plain text. Say if Column A had the word Label in it add it to Column B4? Thanks in advance!
In cell C4 you would use IF to test A4, using string function to do the comparison. Then you would either put B4 into C4 if false, or B4&A4 into it if true.
macmostvideo I appreciate you getting back to me. I actually figured out why my formula wasn't working. I'm trying to pull one word out of a full sentence like. Unfortunately, it doesn't seem possible. 😢
This is awesome. Thank you.
Thanks bro ur the best
Thanks Gary!
Gary, I am stuck using the IF function. Can you help?
Yes. Ask later at macmost.com/ask
You are great!
CAN YOU PLAY AUDIO IF CONDITION IS MET ????? IF SO PLEASE LET ME KNOW THANK YOU IN ADVANCE
No, there's nothing like that.
Please help: how to copy and paste formulas in numbers without changing cell references. I'm consuming a lot of time. Thanks
This explains it: ruclips.net/video/KZJ8KqV0rPg/видео.html
Hi! I study at a university and we have a lecture where we have multiple exams during the semester and the average of these will be the final grade. We have a % scale for those average results. I made an average cell and below that I would like to make a cell that tells me what grade am I going to get by the average % results.
Can you maybe help me please? I hope I was clear, what I would like to do :)
Thank you!
I'm sure you could do it, but the exact formula depends on the details, like the scale you mentioned.
so below 39% i’m failed, between 40-49 the grade is 1, between 50-63 the grade is 2, between 64-77 the grade is 3, between 78-91 the grade is 4 and between 92-100 the grade is 5.
I started using numbers recently, because i just got an ipad and I’m using it with a keyboard. Could you please help me what would be the correct formula?
thank you so much!
@@fannidianahorvath1982 For a long list of ranges like that, you would use the LOOKUP function. See macmost.com/a-simple-numbers-lookup-example.html
@@macmost Thank you so much! Finally I can do it :)
Hi Gary, great video as always.I can't seem to find an answer for my question anywhere, but I'm sure if it's possible you'll know. I'm making a budget spreadsheet and want to track my finances by month. I have made a pop up menu with the items being each month of the year to differentiate and save space. Below the pop up menu table I have three more larger tables, with my income, expenses and savings. What I would like to know is this - is it possible for me to select each month within the pop up menu and have different tables appear underneath with the relevant data for each month. I'm sure it would be an if function but I'm struggling and would be grateful for your help.
Not sure exactly what you are doing here, but you can't have dynamic pop-up menus so probably not.
@@macmost Could I email my document to you? It's quite hard to explain through text.
@@jackcruickshanks Sounds like you may want more support than I can provide. Perhaps consult with a freelance spreadsheet expert?
Thank you for a very informative video!
I'd like to test if a date in another cell is after a certain date. Like this: IF(A1>"22-01-01",TRUE,FALSE) where A1 contains 22-02-02. It doesn't work though, it returns FALSE. But if I change in the formula A1 to "22-02-02", it correctly returns "TRUE". And if I change the content of A1 from 22-02-02 to "22-02-02" (here, it turns to "2022-02-02"), it also works. Unfortunately after this fix, the cell can no longer be used together with Stock and Duration. How can I make this work properly?
First, I think it may be getting confused by your date format. When you select the 22-02-02 cell, what do you see at the bottom left corner? Alway, I used "2/2/2022" to make sure it is a common format. Then you need to compare that to a date, but IF(A1>"22-01-01",TRUE,FALSE) is comparing it to the string "22-01-01." So instead use IF(A1>date(2022,1,1),TRUE,FALSE)
@@macmost Thank you so much! Putting it in DATE() did the trick!
Great video as always Gary. Quick question, i have a numbers sheet which has 4000 keywords and i only want keywords which have 4 words or more, how do i do that? Also if i want t remove/delete the rows which contain a particular word, how do i do that as well? For example i want t delete all the rows that have the word "Walmart" in them. Thank u so much 🙂
What do you mean by "keywords?" I would sort by the column that contains those words so all of the Walmart words are together. Select and delete.
@@macmost hanks Gary. By keyword i mean for example what somebody types on google or Amazon while searching for something example someone typing on google "long sleeve black shirt walmart". Could you kindly tell me how you sort by the column that contains that word? That's where I'm stuck. Thank u so much again for the time Gary!
@@nkwhph So just the text in a cell. To sort, on the right side, choose Organize, Sort. Then add to column to sort by. If your particular case, you can also choose Organize, Filter and use that to just see those rows.
@@macmost Thanks Gary. That was super confusing, do u have a video u have done on this?
@@nkwhph ruclips.net/video/xaENndIKcdE/видео.html
Anyone know how to mix number and text, where the number is variable... same with if we do in excle with function text(A2;”#.##,-“)...
Example: The House will price “2,000,000,-“ USD
I think custom formatting can help with this. You can format a cell to automatically add strings before and after data.
If that doesn’t do what you need I’m sure you could have a CONCAT function in another cell that added strings to the house value
I dont know what Im doing wrong its always giving me syntax error. Im on iPad.
IF(D2≤5;7;IF(AND(;D2≤10;D2>5);10.5;IF(AND(D2≤20;D2>10);14;25))
In your first AND statement you have nothing as the first parameter (it starts with a ;)
@@macmost Thank you!
@@macmost
No more syntax error but when I enter the number 6 in D2 the cell says too high, while it‘s supposed to say 10,5 and I dont know why. I also cant use the . in the formula or else it gives me syntax error.
IF(D2≤5;7;IF(AND(;D2≤10;D2>5);10,5;IF(AND(;D2≤20;D2>10);14;"too high")))
@@jacksonvandenheever8241 I'm still seeing an extra ; in both your AND statements. Hard for me to "debug" your formula for you by just seeing this. I don't know your logic here with this. Try just doing a small part at a time. Get the first AND right and test it. Then get the second AND right and test it, etc. If you still can't figure it out you'll need to have someone you can work with more directly.
@@macmost i managed to figure it out! The problem wasnt the formula, but it was the fact that i entered the AND premise within the preset of the IF condition! I just had to enter it without using the preset. Thanks for the help :)
Thank you so much, your site is NOT working!
There was a server outage this morning. Fixed now.