 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / December 2005Value Return
In cells from C2:K2, some cells have either a number or a letter. What I like to have is a formula like If(sum(C2:K2) have anything in them numbers, letters, return value 1, otherwise return 0) It's easy if there are numbers in any given row, but the letters are throughing me off. How can this be handled. What do you want your answer to be if some of the cells have numbers some have letters, some have blanks, and some have characters othe than numbers and letters, such as the % sign If there is anything in the range I want it to return a value of 1 otherwise a value of 0. For example: If(sum(A2:G2>1,1,0) In this formula if there is any number in any cell I would be a value of 1 if not I would get a value of 0. My problem is that in some rows I might not have a number in any given cell but may have a letter like A or G or whatever. In those rows if any cell has a letter I need the target formula to still return a value of 1 otherwise 0. I hope that clears it up. > What do you want your answer to be if some of the cells have numbers, > some have letters, some have blanks, and some have characters other > than numbers and letters, such as the % sign? Try this: =--(COUNTA(A2:G2)>0)  SignatureHTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== > If there is anything in the range I want it to return a value of 1 otherwise > a value of 0. [quoted text clipped - 12 lines] > > some have letters, some have blanks, and some have characters other > > than numbers and letters, such as the % sign? Okay, try this. =IF(ISERROR(CODE(A2:G2)),0,1) Signaturersenn Take that back. I didn't test it sufficiently. It behaves erratically. :mad: Signaturersenn |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.