Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Return
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? -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=493233 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Return
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:G21,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. "rsenn" wrote in message ... 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? -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=493233 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Return
Try this:
=--(COUNTA(A2:G2)0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frick" wrote in message ... 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:G21,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. "rsenn" wrote in message ... 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? -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=493233 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Return
Okay, try this. =IF(ISERROR(CODE(A2:G2)),0,1) -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=493233 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Return
Take that back. I didn't test it sufficiently. It behaves erratically. -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=493233 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Return
Sorry about that. Try =IF(ISBLANK(A2:G2),0,1) -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=493233 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
Excel - return a picture or range rows as the result of a formula | Excel Worksheet Functions | |||
How do create a formula to evalute a # to return 1 of 4 conditions | Excel Worksheet Functions | |||
if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions |