Microsoft Office Forums Formula for Complete/Incomplete

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-09-2016, 05:07 AM
Phil H Phil H is offline Formula for Complete/Incomplete Windows XP Formula for Complete/Incomplete Office 2010 64bit
Advanced Beginner
Formula for Complete/Incomplete
 
Join Date: Jun 2010
Posts: 79
Phil H is on a distinguished road
Default Formula for Complete/Incomplete

Need a formula in cell G29: if there is an X in any cell in the range H29:Z29, then Incomplete, else Complete. All cells being empty means complete.

=IF((H46:Z46)="x","incomplete","complete") does not work - returns #VALUE!.

Last edited by Phil H; 08-09-2016 at 12:49 PM. Reason: Added formula comment.
Reply With Quote
  #2  
Old 08-10-2016, 06:52 AM
Phil H Phil H is offline Formula for Complete/Incomplete Windows XP Formula for Complete/Incomplete Office 2010 64bit
Advanced Beginner
Formula for Complete/Incomplete
 
Join Date: Jun 2010
Posts: 79
Phil H is on a distinguished road
Default Partial Answer

This formula works for three cells. The range is 19 cells long, so how to condense this formula where all cells are included into one phrase?

=IF(H46="X","Incomplete",IF(I46="X","Incomplete",I F(J46="X","Incomplete","Complete")))
Reply With Quote
  #3  
Old 08-10-2016, 11:21 AM
NoSparks NoSparks is offline Formula for Complete/Incomplete Windows 7 64bit Formula for Complete/Incomplete Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

try this in G29 and drag down

=IF(COUNTIF($H29:$Z29,"x")>0,"Incomplete",IF(COUNT IF($H29:$Z29,"")=19,"Complete","Incomplete"))


EDIT: I can't get rid of the space in the second COUNTIF
Reply With Quote
  #4  
Old 08-10-2016, 12:21 PM
Phil H Phil H is offline Formula for Complete/Incomplete Windows XP Formula for Complete/Incomplete Office 2010 64bit
Advanced Beginner
Formula for Complete/Incomplete
 
Join Date: Jun 2010
Posts: 79
Phil H is on a distinguished road
Default

Thanks NoSparks - works exactly as needed. I toyed around with the count thing but couldn't figure it out. You've saved me a lot of time and aggravation.
Reply With Quote
Reply

Tags
formula, range formula

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for Complete/Incomplete How to show planned% complete and actual% complete on the (Project Overview) dashboard report? zislam14 Project 3 12-17-2015 03:03 PM
Formula for filter "Incomplete Tasks for User..." patrickd123 Project 1 04-07-2015 08:15 AM
Formula for Complete/Incomplete sentences are incomplete in word 2007 jirado Word 1 11-09-2014 01:32 PM
Formula for Complete/Incomplete physical complete %, regular work, vs % complete ketanco Project 1 06-16-2014 11:08 AM
Merge from Excel sheet into Word Incomplete gsandle Office 0 07-16-2013 11:51 AM


All times are GMT -7. The time now is 10:43 AM.


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