Google Sheets IF THEN Formula

[@etiennebesson6586]

I heard a rumor that Etienne's Actual Revenue was 85k, so I think he should still get a bonus ;-)

[@saperis]

Really? Well then for sure we will give him his bonus. 😉- Chanel

[@awodako1799]

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"

[@wDec149]

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

[@saperis]

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

[@wDec149]

@@saperis that's really helpful, thanks!

[@LuisVillanueva45]

Very helpful video. Thank you for sharing!

[@saperis]

Thanks for watching! 👍

[@adventuremitchde]

Thank You for the tuto 🙂 How can i insert several options into the formula? Like achived, overachieved?

[@DerekPierce]

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.

[@saperis]

If with "run a script" you mean run Apps Script I would suggest you use the onEdit() trigger. Find out more about triggers: kzbin.info/www/bejne/gXSaoXWgeZh6fdk

[@michaelpavlovich5288]

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.

[@shrikantnikam2426]

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

[@saperis]

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

[@shrikantnikam2426]

@@saperis thank you solved

[@oddvagsmyr2086]

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?

[@kelseapiquette8566]

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.

[@rumblewraps]

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

[@rumblewraps]

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

[@phoebetheimer2877]

Wow, you're amazing!

[@saperis]

Thank you! 😊

[@TheresaSmith-qy8sv]

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

[@saperis]

You want a formula to rename a tab? That doesn't exists.

[@ignaciotorresbarsanti8810]

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?

[@saperis]

The value can be either text or numeric. So you should be able to achieve want you want to do.

[@johnnyanderson2-roblox185]

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.

[@saperis]

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

[@DavidWilliams-yz7uh]

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?

[@saperis]

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.

[@DavidWilliams-yz7uh]

@@saperis you are a star, it seems easy but knowing me it's more complicated 😢

[@saperis]

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/

[@elusive.firstname.lastname]

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?

[@saperis]

Hm...sorry, but I don't understand your question.

[@elusive.firstname.lastname]

@@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

[@saperis]

@@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.

[@jaysonsales3005]

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) ?

[@saperis]

Maybe you should check out the OR formula instead of using the IF formula: support.google.com/docs/answer/3093306?hl=en

[@jaysonsales3005]

@@saperis thank you so much :)

[@synkconsultancy5460]

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

[@saperis]

Sounds more like something IFS would take care of which checks for multiple variables.

[@ronaldhawkins2680]

Morning...Is there a formula that can queue automation? Essentially, if a student scores X then it would queue up a certificate of achievement.

[@saperis]

I don't think such a formula exists in Sheets.

[@anubhatia1147]

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

[@saperis]

Do you want to do something like this? =IF(C32>D32, C32-D32 , "do nothing because it is false")

[@anubhatia1147]

@@saperis if A=A:A AND B=B:B THEN D2-D

[@painfullyhonest]

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?

[@saperis]

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.

[@asymmetricmarketing]

@ 4:00 * Yes and No in "quotation marks"

[@TimmerTheChipper]

Really good tutorial. Thanks

[@saperis]

You're welcome! 😀

[@emmanlopez8623]

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.

[@saperis]

I would suggest you give the IFS formula a try. It should work. support.google.com/docs/answer/7014145?hl=en

[@buzznorcal]

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 :)

[@saperis]

Maybe this? =IF(B2

[@buzznorcal]

@@saperis It worked, thank you so much. Now I understand how this works :)

[@sahilkisanthakur]

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]

Do you want a string "nil"? In that case just write that as the last parameter of the IF formula.

[@sahilkisanthakur]

@@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

[@saperis]

@@sahilkisanthakur Try the following =IFS(B3>C3, "no", B3

[@sahilkisanthakur]

@@saperis thankyou

[@enedinacuetlach229]

How do I write the formula if I want the cell to be a certain color instead of a word?

[@saperis]

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

[@AlexandraBaza-es2ce]

Thank you. So clear

[@saperis]

You're welcome 😀

[@kriztenkk]

Hi, is it possible to add current time in 24 hours format? example, if A1>1, then set B1=current time?

[@saperis]

I would suggest you try out the =NOW() function in Google Sheets.

[@siebevermijs3756]

Thanks for the help!!!

[@saperis]

Happy to help! 😀

[@simpforyongbokk]

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

[@saperis]

You should check out the IFS formula for multiple conditions.

[@kasaluv]

thank you so much!! this was so helpful!

[@saperis]

Glad it was helpful!

[@rimanabelsi613]

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?

[@saperis]

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".

[@vianca811]

@@saperis omg thank you this was really helpful!

[@dentalelements]

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

[@saperis]

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

[@mohammedmuzammilyaseenece9914]

How do you get the average of all "YES" and all "NO" from this ?

[@saperis]

Maybe try AVERAGE. support.google.com/docs/answer/3093615?hl=en

[@cheeseburger6924]

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 ❤️

[@saperis]

You're condition is wrong (A1 =< 100,000). You have to check for A1

[@rickengelhard208]

How about an IF then zero formula so the cell doesn't show #DIV/0!? Thanks

[@saperis]

Maybe? I don't know. I usually have to write formulas myself to see if they work.

[@dreamscapechronicles11]

hi thanks for explaining it so simple way, also can you please tell how to insert a check box instead of "yes" or "no"?

[@saperis]

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.

[@oduartube]

AWESOME!!!!

[@saperis]

Glad you think so!

[@mezoori]

Thanks!!!

[@saperis]

Welcome! 😃

[@Humanbeing01919]

I enterred this formula EXACTLY and it still gives me the "#ERROR!"

[@jamesschuldes181]

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... 😂

[@frankie4073]

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?

[@saperis]

Sorry, but you totally lost me after your third line.

[@frankie4073]

@@saperis No worries, I figured it out 2 hours after posting the comment! :P

[@juliusfreund7477]

merci

[@saperis]

De rien. 😀

[@nebojsa1976]

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.

[@saperis]

Sounds like a case for the IF formula: support.google.com/docs/answer/3093364?hl=en

[@raymond2869]

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?

[@saperis]

You would have to use conditional formatting for that. You can't highlight cell values with a formula as far as I'm aware.

[@alex_crane]

Hi! FYI now it's ; symbol instead of , symbol

[@FakeName-ry5lw]

2:10

[@outoftheblue5550]

My version: =IF(J5>10; "GOOD"; "BAD")

[@usdaprime1092]

THOSE ARE FUCKING QUOTATION MARKS

[@saperis]

Thank you for pointing that out. 👍

[@Bannedbooks440]

I need the digits...

[@saperis]

Which digits?

[@JacobsenBros]

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?

[@JacobsenBros]

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.

[@saperis]

Happy to hear that 😀