Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-09-2022, 01:30 PM
teza2k06 teza2k06 is offline IF Statement (again) Windows 10 IF Statement (again) Office 2010 32bit
Advanced Beginner
IF Statement (again)
 
Join Date: Feb 2013
Posts: 37
teza2k06 is on a distinguished road
Default IF Statement (again)

Hi All,
Unfortunately I am stuck on what seems to be a simple formula (in my head), but I can't seem to get it working... surprise surprise .

So in "A3" I want an IF Statement which shows "FC", if cells N3,Q3,T3,W3,Z3,AC3,AF3 all equal 0 (there is currently a formula in these cells giving me a remaining total of 2 other cells). If not all of the above equal 0 (e.g. one of the 7 cells shows "2", then I want the cell "A3" to say "NC".

Any help would be much appreciated as always.

Thanks all
Reply With Quote
  #2  
Old 02-09-2022, 03:42 PM
Alansidman's Avatar
Alansidman Alansidman is offline IF Statement (again) Windows 10 IF Statement (again) Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

if(and(N3=0,Q3=0,T3=0,W3=0,Z3=0,AC=0,AF=0),"FC","N C")
Reply With Quote
  #3  
Old 02-10-2022, 01:18 AM
teza2k06 teza2k06 is offline IF Statement (again) Windows 10 IF Statement (again) Office 2010 32bit
Advanced Beginner
IF Statement (again)
 
Join Date: Feb 2013
Posts: 37
teza2k06 is on a distinguished road
Default

Thank you. That is what I done in the first place, but I have just tried it again and I'm getting "#NAME?" appear instead. It's not liking something
Reply With Quote
  #4  
Old 02-10-2022, 01:22 AM
teza2k06 teza2k06 is offline IF Statement (again) Windows 10 IF Statement (again) Office 2010 32bit
Advanced Beginner
IF Statement (again)
 
Join Date: Feb 2013
Posts: 37
teza2k06 is on a distinguished road
Default

Just tried again. I did have to amend the above formula to show as AC3 rather than just AC.

It did not show "#NAME?" this time. But showed "NC" which is strange as all the cells mentioned in the formula have the value of 0, so it should be showing "FC".
Reply With Quote
  #5  
Old 02-10-2022, 04:42 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF Statement (again) Windows 7 64bit IF Statement (again) Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Please post a sample sheet ( no pics please) Thx
(also AF3 instead of AF in Alan's answer)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #6  
Old 02-11-2022, 12:39 AM
Alansidman's Avatar
Alansidman Alansidman is offline IF Statement (again) Windows 10 IF Statement (again) Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

Thanks for the correction Pepe. Air code/formulas are not always trustworthy.
Reply With Quote
  #7  
Old 02-11-2022, 12:47 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF Statement (again) Windows 7 64bit IF Statement (again) Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

No worries Alan, I've already learned so much from you I thought it time to do something back
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #8  
Old 02-11-2022, 01:22 AM
teza2k06 teza2k06 is offline IF Statement (again) Windows 10 IF Statement (again) Office 2016
Advanced Beginner
IF Statement (again)
 
Join Date: Feb 2013
Posts: 37
teza2k06 is on a distinguished road
Default

I have attached a rough copy of the template I currently have set up. I have removed some columns and information, so the cells in the formula are slightly different than mentioned in previous replies.

The current formula is - =IF(AND(M3="0",P3="0",S3="0",V3="0",Y3="0",AB3="0" ,AE3="0"),"FC","NC")

I only need it to apply for the "Remaining" columns for the column numbers highlighted in blue (you'll see what I mean)
Attached Files
File Type: xlsx Template.xlsx (13.3 KB, 8 views)
Reply With Quote
  #9  
Old 02-11-2022, 08:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF Statement (again) Windows 7 64bit IF Statement (again) Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Your mistake is confusing "0" and 0.
All strings between double quotes are text, the rest is number
As there are no text strings 0 you always get NC

Your values in col M, etc are numbers
This is obscured by the fact that you applied manual alignment probably for cosmetic reasons
You should avoid that when building your sheet, and only apply it on the finished product
Why ? Text is always left aligned in a cell and numbers always right-aligned. This feature helps finding mistakes
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting a Select statement in Excel to an update statement shabbaranks Excel Programming 5 10-31-2018 11:47 PM
IF Statement (again) IF statement mrry475 Excel 1 09-06-2016 09:30 AM
IF Statement (again) Need a little help with an if statement cangelis Excel 2 04-08-2015 05:55 PM
IF Statement (again) IF Statement teza2k06 Excel 1 03-11-2015 12:44 AM
IF Statement (again) Need help with If, Then Statement Please cangelis Excel 4 01-03-2014 09:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft