Google Sheets Help: If-Then formula

[WaveHunter]

Can anybody help me with this in Google Sheets?  I used to be able to do it and now I can't remember how.

 

I want a formula that will make cell 13 equal zero, if the sum of cell 14 + 15 is greater than 2,000.  Assume the column is "A" for the purpose of giving me the formula.

 

 

Thanks!

 

 

Edited November 28, 2022 by WaveHunter

[scubascuba3]

If google sheets is similar to Excel it will be something like this

 

=if((A14+A15)>2000,0)

 

it will show false if not >2000

Edited November 28, 2022 by scubascuba3

[WaveHunter]

1 hour ago, scubascuba3 said:

If google sheets is similar to Excel it will be something like this

 

=if((A14+A15)>2000,0)

 

it will show false if not >2000

Thanks!  Is there any way it can show the numeral "0" instead of "false"?  That was how it was working before.  Not a big deal I guess since "FALSE" still computes as zero, but the numeral "0" just looks cleaner on a spreadsheet.

Edited November 28, 2022 by WaveHunter

[scubascuba3]

5 minutes ago, WaveHunter said:

Thanks!  Is there any way it can show the number zero "0" instead of false?  That was how it was working before.

That formula will show 0 if greater than 2000 which is what you asked for, but if not greater than 2000 you haven't said what you want it to show?

Edited November 28, 2022 by scubascuba3

[mrfill]

26 minutes ago, WaveHunter said:

Thanks!  Is there any way it can show the numeral "0" instead of "false"?  That was how it was working before.  Not a big deal I guess since "FALSE" still computes as zero, but the numeral "0" just looks cleaner on a spreadsheet.

=if((A14+A15)>2000, 0,"something else")

 

will do the job and you can decide what goes in the cell if <1999. I chose 'something else' but you can have a numeric (such as the total)  by using =if((A14+A15)>2000, 0, A14+A15)

 

[Slip]

I have posted the answer on your other topic, which was seriously confusing me for a while lol.

[WaveHunter]

14 hours ago, scubascuba3 said:

That formula will show 0 if greater than 2000 which is what you asked for, but if not greater than 2000 you haven't said what you want it to show?

Sorry, you're right...I forgot to mention the value of Cell #13 if the sum of Cell #14 + #15 is less than or equal to 2,000.

 

So, the formula I need is:  

"If the sum of cell #14 + #15 is equal to or greater than 2000, cell #13 = 0 and if the sum of cells #14 + #15 is less than 2000, the value of cell#13 = 521.8"

 

 

Edited November 29, 2022 by WaveHunter

[WaveHunter]

13 hours ago, Slip said:

I have posted the answer on your other topic, which was seriously confusing me for a while lol.

Sorry but as ScubaScuba3 pointed out, I forgot to state the value of Cell #13 if #14 + #15 is less than 2000.

 

So the correct expression I'm looking for is:

"If the sum of cell #14 + #15 is equal to or greater than 2000, cell #13 = 0 and if the sum of cells #14 + #15 is less than 2000, the value of cell#13 = 521.8"

[Lemsta69]

9 minutes ago, WaveHunter said:

Sorry but as ScubaScuba3 pointed out, I forgot to state the value of Cell #13 if #14 + #15 is less than 2000.

 

So the correct expression I'm looking for is:

"If the sum of cell #14 + #15 is equal to or greater than 2000, cell #13 = 0 and if the sum of cells #14 + #15 is less than 2000, the value of cell#13 = 521.8"

This should work but test it anyway...

 

=IF(A14+A15<2000,521.8,0)

[WaveHunter]

59 minutes ago, Lemsta69 said:

This should work but test it anyway...

 

=IF(A14+A15<2000,521.8,0)

Works like a charm! Thanks ????

 

[WaveHunter]

Thanks to everyone for who replied.  You all are the best!!

Edited November 29, 2022 by WaveHunter

[Lemsta69]

3 minutes ago, WaveHunter said:

Works like a charm! Thanks ????

 

Thanks also to @Slipfor showing me that you don't need to use an extra set of parantheses around "A14+A15". In the other thread that is. AN website up to its usual tricks ????

Edited November 29, 2022 by Lemsta69
removed annoying bold text

[WaveHunter]

10 minutes ago, Lemsta69 said:

Thanks also to @Slipfor showing me that you don't need to use an extra set of parantheses around "A14+A15". In the other thread that is. AN website up to its usual tricks ????

I love Google Sheets.  It usually points out errors in syntax when you insert formulas but in this case it didn't help me at all.  Glad there are some smarter people than me on this forum when it comes to stuff like this.

 

BTW, AN is always up to its' usual tricks.  That's what makes it so entertaining!

Edited November 29, 2022 by WaveHunter

[Lemsta69]

4 minutes ago, WaveHunter said:

I love Google Sheets.  It usually points out errors in syntax when you insert formulas but in this case it didn't help me at all.  Glad there are some smarter people than me on this forum when it comes to stuff like this.

 

BTW, AN is always up to its' usual tricks.  That's what makes it so entertaining!

Man I hate Google Sheets! I'm an Excel user because of work and can't get anything done without keyboard shortcuts. It pains me when I have to switch to the mouse ????

[WaveHunter]

15 hours ago, mrfill said:

=if((A14+A15)>2000, 0,"something else")

 

will do the job and you can decide what goes in the cell if <1999. I chose 'something else' but you can have a numeric (such as the total)  by using =if((A14+A15)>2000, 0, A14+A15)

 

Thanks!  It was the "something else" I forgot to account for.  I got confused by your reply at first but when @Lemsta69 showed an example it all made sense.  I would never have gotten this right without the help of all of you...MOST appreciated!

[WaveHunter]

17 minutes ago, Lemsta69 said:

Man I hate Google Sheets! I'm an Excel user because of work and can't get anything done without keyboard shortcuts. It pains me when I have to switch to the mouse ????

The main reason I prefer Google Sheets is because it's cloud-based.  Since I use multiple devices, it just makes life a lot easier keeping everything in sync.

 

I use Excel for certain things that Google Sheets can't do, but sometimes Excel can be a little overwhelming with all of its' advanced functions...all very important for some people, but not so much for me.

 

Edited November 29, 2022 by WaveHunter

[Lemsta69]

4 minutes ago, WaveHunter said:

The main reason I prefer Google Sheets is because it's cloud-based.  Since I use multiple devices, it just makes life a lot easier keeping everything in sync.

 

Fair enough. But I was using Excel 8 hours a day every day, or that's what my timesheet said, so I'd have got nothing done without keyboard shortcuts. 

 

They were talking about introducing Office 365 just before I left, I nearly had a bloody heart attack. Try wrangling a 65 column, 300,000 row spreadsheet with just the mouse.

[ozimoron]

1 hour ago, Lemsta69 said:

This should work but test it anyway...

 

=IF(A14+A15<2000,521.8,0)

As an old school code monkey I would still add the extra parentheses for clarity around the sum but that's pure preference.

[WaveHunter]

1 hour ago, Lemsta69 said:

Fair enough. But I was using Excel 8 hours a day every day, or that's what my timesheet said, so I'd have got nothing done without keyboard shortcuts. 

 

They were talking about introducing Office 365 just before I left, I nearly had a bloody heart attack. Try wrangling a 65 column, 300,000 row spreadsheet with just the mouse.

I can understand that most office environments demand you use Excel, and yeah shortcut hot keys are real important to me too in many of my daily applications like Photoshop and Adobe Premiere, not to mention Mac OSX.

 

As far as anything to do with Microsoft, I'm not a fan.  I had so many issues with Windows and MS software, when I got the famous "blue screen of death" and "Fatal Error" that's when I switched to Apple, and never looked back.

 

This was the TV ad that made me switch...still cracks me up watching all of those old Apple Ads with those two guys that sort of look like Steve Jobs and BIll Gates.  There were 66 ads in that series it was so popular.

 

 

 

[JayClay]

1 hour ago, Lemsta69 said:

They were talking about introducing Office 365 just before I left, I nearly had a bloody heart attack. Try wrangling a 65 column, 300,000 row spreadsheet with just the mouse.

Office 365 is just the subscription-model of paying for office apps. Why would you have had to do without keyboard shortcuts?

 

Also which keyboard shortcuts do you use in Excel that don't work in Google Sheets? 

[Lemsta69]

11 minutes ago, JayClay said:

Office 365 is just the subscription-model of paying for office apps. Why would you have had to do without keyboard shortcuts?

 

Also which keyboard shortcuts do you use in Excel that don't work in Google Sheets? 

The way that my company introduced us to Office 365 was via demo that was browser-based and all the normal keyboard shortcuts didn't work. 

 

I've only ever accessed GSheets via Android or via desktop browser. Is there a stand-alone version of it that I missed?

[Photoguy21]

IF(A14+A15>2000,0,A13)

 

 

 

[Lemsta69]

39 minutes ago, WaveHunter said:

As far as anything to do with Microsoft, I'm not a fan.  I had so many issues with Windows and MS software

Yeah they suck big time but I still use Windows 11 at home because it's what I know after working with it since 1990 or so. 

 

At work the BSOD and other issues were a great excuse to waste an hour or two at the company's expense. At home they're just freaking annoying! ????

[JayClay]

16 minutes ago, Lemsta69 said:

The way that my company introduced us to Office 365 was via demo that was browser-based and all the normal keyboard shortcuts didn't work. 

 

I've only ever accessed GSheets via Android or via desktop browser. Is there a stand-alone version of it that I missed?

No, but standard keyboard shortcuts work in Google Sheets for desktop browser as they do in Office 365 web and standalone applications.

Maybe the intro had them disabled for security purposes?

[Lemsta69]

6 minutes ago, JayClay said:

Maybe the intro had them disabled for security purposes?

Possibly. Thankfully I found a way to retire early so don't have to worry about that sort of nonsense anymore ????

[WaveHunter]

1 hour ago, Lemsta69 said:

Yeah they suck big time but I still use Windows 11 at home because it's what I know after working with it since 1990 or so. 

 

At work the BSOD and other issues were a great excuse to waste an hour or two at the company's expense. At home they're just freaking annoying! ????

I have a lot of programs that are windows-based so I still have to use those on my macbook through virtual-windows but that makes my machine run like it is in molasses with the fan at max speed ????. Windows is such a bloated OS compared to Mac.

 

[Lemsta69]

7 minutes ago, WaveHunter said:

I have a lot of programs that are windows-based so I still have to use those on my macbook through virtual-windows but that makes my machine run like it is in molasses with the fan at max speed ????. Windows is such a bloated OS compared to Mac.

 

I've never used a Mac so I don't know what the OS is like. I bought an Intel NUC a few years ago when my Clevo gaming laptop died. The NUC was cheaper than a laptop and also under-specced for games hence a good way of weaning myself off of those time suckers. I'm looking at you, S.T.A.L.K.E.R. Shadow of Chernobyl!

 

Windows 11 is probably as bloated as you say but thankfully on my hardware I don't notice it much. Thanks Samsung NVMe SSD. 

 

But running the thing in VM for actual work stuff? That's nuckin' futs mate ????

[WaveHunter]

17 hours ago, Lemsta69 said:

I've never used a Mac so I don't know what the OS is like. I bought an Intel NUC a few years ago when my Clevo gaming laptop died. The NUC was cheaper than a laptop and also under-specced for games hence a good way of weaning myself off of those time suckers. I'm looking at you, S.T.A.L.K.E.R. Shadow of Chernobyl!

 

Windows 11 is probably as bloated as you say but thankfully on my hardware I don't notice it much. Thanks Samsung NVMe SSD. 

 

But running the thing in VM for actual work stuff? That's nuckin' futs mate ????

LOL, I enjoyed reading your post but with all your acronyms I'm not sure what you were saying.  

 

"S.T.A.L.K.E.R. Shadow of Chernobyl!" ???  I am guessing that must be a gaming term, yes?

 

ALl I was really saying is that coding that goes into Microsoft products is incredibly bloated compared to Apple's clean and efficient coding.

 

Apple ran a series of ads back in 2007 comparing Macs to PC's and they were hysterical to watch.  Here is one of them that kind of sums up what I am saying:

 

 

Edited November 30, 2022 by WaveHunter