How can i make it so the text "Yes" is in green color and "No" in red color, of course using if formula. And how can i make it so if the cell value is none it says "0", but when it changes to other value it says "Yes" or "No"
hey, great tutorial! I was wondering if you could colour fill a cell for the "true" and "false" values? i.e. set a cell to 'green fill', if the value is true
Nice video! I was wondering how I could use a dropdown menu to subtotal all the cells in the column next to it in each category. Example: travel expense is subcategorized into lodging, entertainment, transportation etc...by using a drop down menu in the cell to the left of the expense amount. I want it to total all transportation ... into a single cell. Thank you so much in advance.
Thanks for the tutorial. However, I need information on how to write the formula in this case: If the expression A3 (as in paper format) occurs among other words in one cell, then I want an other cell (same sheet) to respond with a number (for instance 20). Can that be done?
If with "run a script" you mean run Apps Script I would suggest you use the onEdit() trigger. Find out more about triggers: ua-cam.com/video/KC7pBjD3GGw/v-deo.html
I want to make formula suppose I am entering value '1.15' it is less than 1.5 so considered as 1, if it is greater than 1.5 considered as 2 Like 1.25 as 1 1.75 as 2 44.25 as 44 44.80 as 45 please help me
Hi Shrikant. Sound like you're looking for one of the following function: ROUND, ROUNDUP or ROUNDDOWN. Read the official documentation to figure out how to use it for your needs: support.google.com/docs/answer/3093440?hl=en-GB
Is it possible to use this or IFS formula for multiple values? For example, I am working on a tip calculator sheet where if the tips are up to $375, 10% would be allocated to the kitchen, if the tips are $376-475, 15% would be allocated to the kitchen, and if the tips are $476+ the kitchen would get 20%. I feel like it should be possible but I tried nesting the formula and keep getting an error.
I'm trying to do something really complex for bids where I'm writing like 12 rules but it only takes 3 which sucks but trying to figure out a workaround haha
@@saperis so I want to put a number in column one and in column two and three a zero or a one will show up so that I can get a tally down below how many we above 25 and how many were above 25
@@elusive.firstname.lastname I still don't understand what you're trying to achieve. But if the IF function doesn't do the trick maybe check out the IFS as you can chain conditionals to be checked.
if we use if function that this value is equals to this and this value is equals to that then ow can we subtract value with if and function is it possible
Hello - I need to build something on a separate tab that says if email is xxxx, then the name on another tab will equal xxx name. Multiple names, multiple email addresses. I just can't think of the name of the function in Google Sheets. Thanks
What if i want to add a numeric value in the "bonus" cells, for example IF a "x" do the job, profit is "% of the price", if not, is "other % of the price", is that possible?
I've created a staff league table a d depending on actions they take they get points, then I have a column that totals up that staffs points. I need a cell to populate with (their name) whoever has the most points. Is this some thing that can be done with IF function?
Not really. An IF function is great when your looking at a per row basis. But what you're looking for is more like a VLOOKUP(support.google.com/docs/answer/3093318?hl=en-GB) where you want all score in a column to be analyzed and then give back the name of a person, which is in a different column.
I'm not really spreadsheet expert myself either. I would have to invest some time into getting this done. Why don't you contact a Google Sheets freelancer if you need some help on this one: www.upwork.com/hire/google-spreadsheet-freelancers/
If you want to format a cell by changing its color, I would use conditional formatting and not an IF formular. Find out more here: support.google.com/docs/answer/78413?hl=en&co=GENIE.Platform%3DDesktop
This helped tons, but when I put it into my sheet I think it is interfering with my other script. I have a script that checks the total time someone was on the clock and totals it up and puts it into a column, and I am trying to get the script to check if the total time is over a certain amount of minutes, and its giving a false true.
If you are running an Apps Script script to evaluate times, then maybe something is wrong with the date format. Apps Script is essentially JavaScript and in JavaScript dates and times are notoriously difficult to work with. developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date
Here's what I'm trying to do. If B2 is equal or less than 2 then return the value of (B2+C2)*100), else return the value of (B2+C2)*50) Thank you, your video almost got me there but I still need a bit of help :)
what if I want the true/false answer to be determined by subtracting two fields? for example Field A and B if B>A then I want it to say "gain" and subtract A from B If A>B I want it to say "loss" and subtract B from A would it look something like =if(B>A,"gain"A-B,"Loss"B-A) needless to say this didn';t work. What am I missing?
I don't know what you are missing. I'm terrible at just looking at a formula and seeing the mistake. The only solution for me is to A) check the Sheets formula documentation and B) do a Google search to see if others have achieved what I'm looking to do.
please tell about equal to if 105=105 i need command nill instead of getting yes or no help me with this or or give me another formula which gives result yes no nil to same example you explained
@@saperis not understood i will give example help me (B3) (C3) (output) 10 10 yes 33 55 yes 50 10 no 100 120 yes formUla i used is =if(B3>C3,"no","Yes") but when 10 = 10 it gives "yes" which is wrong but i want to wright "Nill" is both are same how i can do that so give me new formula which has output yes , no , Nill
I am trying to figure out if there's a formula that I could use for ranges of data. Like the condition is 1 - 19 Facebook Reactions = 10 points, so if I will type values between 1 and 19 on a specified cell, next cell would return 10. Been trying to figure this out but can't find any.
Substitute the "YES" for TRUE (without quotation marks!) and the "NO" for FALSE. Then you have to select the column with the TRUE / FALSE and from the menu choose Insert > Tick box. The cells with the TRUE values will be ticked, the ones with FALSE will be unticked.
Maybe try =if(B1, A1). That would copy the content of A1 if B1 weren't empty. Please check the specs to see that you reference a cell and not a column: support.google.com/docs/answer/3093364?hl=en
Check out the IFS function that checks for multiple values: support.google.com/docs/answer/7014145?hl=en That allows you to check for two separate values and then print either "temporary" or "permanent".
Hi, I have a question: how do do conditional substraction? for example, if the value is equal or less than 100,000. I need to substraction it to 50. if A1 =< 100,000. then B1 = A1 - 50. thank you ❤️
How do I make this happen: Box 1 is TRUE. Box 2 is FALSE. Box 3 is TRUE Box 4 then checks if box 1 is true, then takes 10 from 30, and then shows 20 in box 4, box 2 is false so nothing happens, box 3 is true so it takes another 5 from the 20 to make it 15. How do I do that?
itym: "yes or no in quotes" not parenthesis (at 4:03) syntax is syntax and words have meaning and a picture is worth a thousand dollars if you sell your feet pics... 😂
Million dollar question. I have this kind of If statement in Google Sheets: if(ISNUMBER(B9), INDEX(B:B,9,1), "") . Now, if it is Number in Cell B9, it will enter the number in another cell (C9), but if it is not number, it will not show anything, or it will show FALSE (if I remove ""). What I need is if it is FALSE, then don't do anything, just exit, don't show empty or FALSE, keep the previous number. So, if B9 is number 3, I want it to show up in C9 cell number 3, and it does that, but if B9 shows maybe text "home" (before it was number 3), I don't want it to show text "FALSE" or empty in C9, but keep previous number (number 3). So, essentially, exit right away after TRUE statement.
Hi, I have a question: So I know how to print text if it is an if-then statement, but I want it to Highlight a column instead of printing text. How do I do that?
I want B3-C3 only if there is a number in b3. But if it doesn't I don't want it to calculate anything because I don't want to have a -200 for example when b3 has nothing and c3 has 200. Can I do that?
I think I figured it out. =if(IFBLANK(B3),"",B3-C3) I understand it as saying if cell B3 is blank, then enter in nothing, but if it has something in it, then B3-C3. Thank you. Your video really helped me problem solve this.
I heard a rumor that Etienne's Actual Revenue was 85k, so I think he should still get a bonus ;-)
Really? Well then for sure we will give him his bonus. 😉- Chanel
Very helpful video. Thank you for sharing!
Thanks for watching! 👍
How can i make it so the text "Yes" is in green color and "No" in red color, of course using if formula. And how can i make it so if the cell value is none it says "0", but when it changes to other value it says "Yes" or "No"
hey, great tutorial! I was wondering if you could colour fill a cell for the "true" and "false" values? i.e. set a cell to 'green fill', if the value is true
Yes, that's possible and I would use conditional formatting to do so: support.google.com/docs/answer/78413?co=GENIE.Platform%3DDesktop&hl=en
@@saperis that's really helpful, thanks!
Thank You for the tuto 🙂
How can i insert several options into the formula? Like achived, overachieved?
Thank you. So clear
You're welcome 😀
Wow, you're amazing!
Thank you! 😊
Thanks for the help!!!
Happy to help! 😀
Really good tutorial. Thanks
You're welcome! 😀
thank you so much!! this was so helpful!
Glad it was helpful!
Nice video! I was wondering how I could use a dropdown menu to subtotal all the cells in the column next to it in each category. Example: travel expense is subcategorized into lodging, entertainment, transportation etc...by using a drop down menu in the cell to the left of the expense amount. I want it to total all transportation ... into a single cell. Thank you so much in advance.
Thanks for the tutorial. However, I need information on how to write the formula in this case: If the expression A3 (as in paper format) occurs among other words in one cell, then I want an other cell (same sheet) to respond with a number (for instance 20). Can that be done?
What if I want an If function for the a cell being populated with anything? I want to run a script but it only needs to run once the cell is filled.
If with "run a script" you mean run Apps Script I would suggest you use the onEdit() trigger. Find out more about triggers: ua-cam.com/video/KC7pBjD3GGw/v-deo.html
Thank you mam. i have a question, can i add "or" statement on if statement? like for example =if(A3="Restday" or "Absent", 0, 65) ?
Maybe you should check out the OR formula instead of using the IF formula: support.google.com/docs/answer/3093306?hl=en
@@saperis thank you so much :)
Morning...Is there a formula that can queue automation? Essentially, if a student scores X then it would queue up a certificate of achievement.
I don't think such a formula exists in Sheets.
I want to make formula suppose I am entering value '1.15' it is less than 1.5 so considered as 1, if it is greater than 1.5 considered as 2
Like
1.25 as 1
1.75 as 2
44.25 as 44
44.80 as 45 please help me
Hi Shrikant. Sound like you're looking for one of the following function: ROUND, ROUNDUP or ROUNDDOWN.
Read the official documentation to figure out how to use it for your needs: support.google.com/docs/answer/3093440?hl=en-GB
@@saperis thank you solved
Is it possible to use this or IFS formula for multiple values? For example, I am working on a tip calculator sheet where if the tips are up to $375, 10% would be allocated to the kitchen, if the tips are $376-475, 15% would be allocated to the kitchen, and if the tips are $476+ the kitchen would get 20%. I feel like it should be possible but I tried nesting the formula and keep getting an error.
Hey trying to do something similar just figured I would try to help! It only takes 3 arguments unfortunately but you can use this formula: IF(cell
I'm trying to do something really complex for bids where I'm writing like 12 rules but it only takes 3 which sucks but trying to figure out a workaround haha
@ 4:00 * Yes and No in "quotation marks"
Is there a way to say if a number is less than a certain number it makes that field of one or zero for a subtotal tally?
Hm...sorry, but I don't understand your question.
@@saperis so I want to put a number in column one and in column two and three a zero or a one will show up so that I can get a tally down below how many we above 25 and how many were above 25
@@elusive.firstname.lastname I still don't understand what you're trying to achieve. But if the IF function doesn't do the trick maybe check out the IFS as you can chain conditionals to be checked.
Thanks!!!
Welcome! 😃
if we use if function that this value is equals to this and this value is equals to that then ow can we subtract value with if and function is it possible
Do you want to do something like this?
=IF(C32>D32, C32-D32 , "do nothing because it is false")
@@saperis if A=A:A AND B=B:B THEN D2-D
Hello - I need to build something on a separate tab that says if email is xxxx, then the name on another tab will equal xxx name. Multiple names, multiple email addresses. I just can't think of the name of the function in Google Sheets. Thanks
You want a formula to rename a tab? That doesn't exists.
how will we use if then if we want to check an one variable is greater than second and third by specific %then yes or no
Sounds more like something IFS would take care of which checks for multiple variables.
What if i want to add a numeric value in the "bonus" cells, for example IF a "x" do the job, profit is "% of the price", if not, is "other % of the price", is that possible?
The value can be either text or numeric. So you should be able to achieve want you want to do.
I've created a staff league table a d depending on actions they take they get points, then I have a column that totals up that staffs points.
I need a cell to populate with (their name) whoever has the most points. Is this some thing that can be done with IF function?
Not really. An IF function is great when your looking at a per row basis.
But what you're looking for is more like a VLOOKUP(support.google.com/docs/answer/3093318?hl=en-GB) where you want all score in a column to be analyzed and then give back the name of a person, which is in a different column.
@@saperis you are a star, it seems easy but knowing me it's more complicated 😢
I'm not really spreadsheet expert myself either. I would have to invest some time into getting this done.
Why don't you contact a Google Sheets freelancer if you need some help on this one: www.upwork.com/hire/google-spreadsheet-freelancers/
How do I write the formula if I want the cell to be a certain color instead of a word?
If you want to format a cell by changing its color, I would use conditional formatting and not an IF formular. Find out more here: support.google.com/docs/answer/78413?hl=en&co=GENIE.Platform%3DDesktop
This helped tons, but when I put it into my sheet I think it is interfering with my other script. I have a script that checks the total time someone was on the clock and totals it up and puts it into a column, and I am trying to get the script to check if the total time is over a certain amount of minutes, and its giving a false true.
If you are running an Apps Script script to evaluate times, then maybe something is wrong with the date format.
Apps Script is essentially JavaScript and in JavaScript dates and times are notoriously difficult to work with. developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date
Here's what I'm trying to do.
If B2 is equal or less than 2 then return the value of (B2+C2)*100), else return the value of (B2+C2)*50)
Thank you, your video almost got me there but I still need a bit of help :)
Maybe this? =IF(B2
@@saperis It worked, thank you so much. Now I understand how this works :)
what if I want the true/false answer to be determined by subtracting two fields? for example Field A and B if B>A then I want it to say "gain" and subtract A from B If A>B I want it to say "loss" and subtract B from A would it look something like =if(B>A,"gain"A-B,"Loss"B-A) needless to say this didn';t work. What am I missing?
I don't know what you are missing. I'm terrible at just looking at a formula and seeing the mistake. The only solution for me is to A) check the Sheets formula documentation and B) do a Google search to see if others have achieved what I'm looking to do.
is there a way to have more than 2 conditions? eg
if B3 = x, then D3 = y
if B3 = p, then D3 = q
if B3 = r, then D3 = s
You should check out the IFS formula for multiple conditions.
please tell about equal to
if 105=105 i need command nill instead of getting yes or no
help me with this or or give me another formula which gives result yes no nil to same example you explained
Do you want a string "nil"? In that case just write that as the last parameter of the IF formula.
@@saperis not understood
i will give example help me
(B3) (C3) (output)
10 10 yes
33 55 yes
50 10 no
100 120 yes
formUla i used is =if(B3>C3,"no","Yes")
but when 10 = 10 it gives "yes" which is wrong
but i want to wright "Nill" is both are same
how i can do that
so give me new formula which has output
yes , no , Nill
@@sahilkisanthakur Try the following =IFS(B3>C3, "no", B3
@@saperis thankyou
I am trying to figure out if there's a formula that I could use for ranges of data. Like the condition is 1 - 19 Facebook Reactions = 10 points, so if I will type values between 1 and 19 on a specified cell, next cell would return 10. Been trying to figure this out but can't find any.
I would suggest you give the IFS formula a try. It should work.
support.google.com/docs/answer/7014145?hl=en
Hi, is it possible to add current time in 24 hours format?
example, if A1>1, then set B1=current time?
I would suggest you try out the =NOW() function in Google Sheets.
hi thanks for explaining it so simple way, also can you please tell how to insert a check box instead of "yes" or "no"?
Substitute the "YES" for TRUE (without quotation marks!) and the "NO" for FALSE.
Then you have to select the column with the TRUE / FALSE and from the menu choose Insert > Tick box.
The cells with the TRUE values will be ticked, the ones with FALSE will be unticked.
Great Video! How do I create a formula for COLUMN C that reads: If COLUMN B is NOT EMPTY, THEN copy contents of COLUMN A to COLUMN C
Maybe try =if(B1, A1).
That would copy the content of A1 if B1 weren't empty.
Please check the specs to see that you reference a cell and not a column: support.google.com/docs/answer/3093364?hl=en
How do you get the average of all "YES" and all "NO" from this ?
Maybe try AVERAGE.
support.google.com/docs/answer/3093615?hl=en
Okay I do have a question, let's say that we need to display the word "temporary" if true and the word "permanent" of false how do we write that?
Check out the IFS function that checks for multiple values: support.google.com/docs/answer/7014145?hl=en
That allows you to check for two separate values and then print either "temporary" or "permanent".
@@saperis omg thank you this was really helpful!
AWESOME!!!!
Glad you think so!
How about an IF then zero formula so the cell doesn't show #DIV/0!? Thanks
Maybe? I don't know. I usually have to write formulas myself to see if they work.
merci
De rien. 😀
Hi, I have a question:
how do do conditional substraction?
for example, if the value is equal or less than 100,000. I need to substraction it to 50.
if A1 =< 100,000. then B1 = A1 - 50. thank you ❤️
You're condition is wrong (A1 =< 100,000). You have to check for A1
I enterred this formula EXACTLY and it still gives me the "#ERROR!"
How do I make this happen:
Box 1 is TRUE.
Box 2 is FALSE.
Box 3 is TRUE
Box 4 then checks if box 1 is true, then takes 10 from 30, and then shows 20 in box 4, box 2 is false so nothing happens, box 3 is true so it takes another 5 from the 20 to make it 15.
How do I do that?
Sorry, but you totally lost me after your third line.
@@saperis No worries, I figured it out 2 hours after posting the comment! :P
itym: "yes or no in quotes" not parenthesis (at 4:03) syntax is syntax and words have meaning and a picture is worth a thousand dollars if you sell your feet pics... 😂
Million dollar question. I have this kind of If statement in Google Sheets: if(ISNUMBER(B9), INDEX(B:B,9,1), "") . Now, if it is Number in Cell B9, it will enter the number in another cell (C9), but if it is not number, it will not show anything, or it will show FALSE (if I remove ""). What I need is if it is FALSE, then don't do anything, just exit, don't show empty or FALSE, keep the previous number. So, if B9 is number 3, I want it to show up in C9 cell number 3, and it does that, but if B9 shows maybe text "home" (before it was number 3), I don't want it to show text "FALSE" or empty in C9, but keep previous number (number 3). So, essentially, exit right away after TRUE statement.
Sounds like a case for the IF formula: support.google.com/docs/answer/3093364?hl=en
Hi, I have a question:
So I know how to print text if it is an if-then statement, but I want it to Highlight a column instead of printing text. How do I do that?
You would have to use conditional formatting for that. You can't highlight cell values with a formula as far as I'm aware.
Hi! FYI now it's ; symbol instead of , symbol
My version: =IF(J5>10; "GOOD"; "BAD")
THOSE ARE FUCKING QUOTATION MARKS
Thank you for pointing that out. 👍
I need the digits...
Which digits?
I want B3-C3 only if there is a number in b3. But if it doesn't I don't want it to calculate anything because I don't want to have a -200 for example when b3 has nothing and c3 has 200. Can I do that?
I think I figured it out. =if(IFBLANK(B3),"",B3-C3) I understand it as saying if cell B3 is blank, then enter in nothing, but if it has something in it, then B3-C3.
Thank you. Your video really helped me problem solve this.
Happy to hear that 😀