| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula help
Hello,
Here is some sample data: 1 1 #N/A #N/A #N/A #N/A #N/A #N/A 12 #N/A #N/A 25 So basically, I am looking for a formula that will tell me if the item in column 1 is a number and item in column 2 is a number then it will put "DONE", if item in column 1 is a number and item in column 2 is "#N/A", then it will put "START", if item in column 1 is "#N/A" and item in column 2 is a number it will put "END", otherwise if both columns are "#N/A", it will leave blank. Any ideas. Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula help
=IF(AND(ISNA(A1),ISNA(B1),"",IF(AND(ISNA(A1),ISNUM BER(B1),"END",IF(AND(ISNUMBER(A1),ISNA(B1),"START" ,"DONE")))
Dave -- Brevity is the soul of wit. "A.S." wrote: Hello, Here is some sample data: 1 1 #N/A #N/A #N/A #N/A #N/A #N/A 12 #N/A #N/A 25 So basically, I am looking for a formula that will tell me if the item in column 1 is a number and item in column 2 is a number then it will put "DONE", if item in column 1 is a number and item in column 2 is "#N/A", then it will put "START", if item in column 1 is "#N/A" and item in column 2 is a number it will put "END", otherwise if both columns are "#N/A", it will leave blank. Any ideas. Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula help
I get an error with this, any thoughts why?
Thanks for the help "Dave F" wrote: =IF(AND(ISNA(A1),ISNA(B1),"",IF(AND(ISNA(A1),ISNUM BER(B1),"END",IF(AND(ISNUMBER(A1),ISNA(B1),"START" ,"DONE"))) Dave -- Brevity is the soul of wit. "A.S." wrote: Hello, Here is some sample data: 1 1 #N/A #N/A #N/A #N/A #N/A #N/A 12 #N/A #N/A 25 So basically, I am looking for a formula that will tell me if the item in column 1 is a number and item in column 2 is a number then it will put "DONE", if item in column 1 is a number and item in column 2 is "#N/A", then it will put "START", if item in column 1 is "#N/A" and item in column 2 is a number it will put "END", otherwise if both columns are "#N/A", it will leave blank. Any ideas. Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula help
Nevermind. Got it, Thanks! It was a paranthesis issue.
"Dave F" wrote: =IF(AND(ISNA(A1),ISNA(B1),"",IF(AND(ISNA(A1),ISNUM BER(B1),"END",IF(AND(ISNUMBER(A1),ISNA(B1),"START" ,"DONE"))) Dave -- Brevity is the soul of wit. "A.S." wrote: Hello, Here is some sample data: 1 1 #N/A #N/A #N/A #N/A #N/A #N/A 12 #N/A #N/A 25 So basically, I am looking for a formula that will tell me if the item in column 1 is a number and item in column 2 is a number then it will put "DONE", if item in column 1 is a number and item in column 2 is "#N/A", then it will put "START", if item in column 1 is "#N/A" and item in column 2 is a number it will put "END", otherwise if both columns are "#N/A", it will leave blank. Any ideas. Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula help
Sorry about that. Parentheses are key.
-- Brevity is the soul of wit. "A.S." wrote: Nevermind. Got it, Thanks! It was a paranthesis issue. "Dave F" wrote: =IF(AND(ISNA(A1),ISNA(B1),"",IF(AND(ISNA(A1),ISNUM BER(B1),"END",IF(AND(ISNUMBER(A1),ISNA(B1),"START" ,"DONE"))) Dave -- Brevity is the soul of wit. "A.S." wrote: Hello, Here is some sample data: 1 1 #N/A #N/A #N/A #N/A #N/A #N/A 12 #N/A #N/A 25 So basically, I am looking for a formula that will tell me if the item in column 1 is a number and item in column 2 is a number then it will put "DONE", if item in column 1 is a number and item in column 2 is "#N/A", then it will put "START", if item in column 1 is "#N/A" and item in column 2 is a number it will put "END", otherwise if both columns are "#N/A", it will leave blank. Any ideas. Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula help
Now to follow-up on this, is there a formula that when a "DONE" is
recognized, then it says "SOLD", otherwise if blank "UNSOLD", and if if sees a "START" it marks its as "SOLD" and everything in between to the next "END". In other words, with the data I had befo 1 1 DONE SOLD #N/A #N/A #N/A #N/A 12 #N/A START SOLD #N/A #N/A SOLD #N/A #N/A SOLD #N/A 25 END SOLD #N/A #N/A |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula help
This formula will meet your original conditons.......and then some
=IF(AND(ISNA(A1),ISNA(B1)),"",IF(AND(ISNA(A1),ISNU MBER(B1)),"END",IF(AND(ISNUMBER(A1),ISNA(B1)),"STA RT",IF(AND(ISNUMBER(A1),ISNUMBER(B1)),"DONE","Cond ition not specified")))) As for your new condition, that is a very dangerous situation to have the result of one row depend on "the one above", for if the data is ever sorted, things will go crazy... Vaya con Dios, Chuck, CABGx3 "A.S." wrote: Now to follow-up on this, is there a formula that when a "DONE" is recognized, then it says "SOLD", otherwise if blank "UNSOLD", and if if sees a "START" it marks its as "SOLD" and everything in between to the next "END". In other words, with the data I had befo 1 1 DONE SOLD #N/A #N/A #N/A #N/A 12 #N/A START SOLD #N/A #N/A SOLD #N/A #N/A SOLD #N/A 25 END SOLD #N/A #N/A |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula help
Try this:
With A1: (Start Value: a number or #N/A) B1: (End Value: a number or #N/A) This formula returns the status C1: =CHOOSE(SUMPRODUCT(--ISNUMBER(A1:B1)*{1,2})+1,"","start","end","Done") Does that help? *********** Regards, Ron XL2002, WinXP "A.S." wrote: Hello, Here is some sample data: 1 1 #N/A #N/A #N/A #N/A #N/A #N/A 12 #N/A #N/A 25 So basically, I am looking for a formula that will tell me if the item in column 1 is a number and item in column 2 is a number then it will put "DONE", if item in column 1 is a number and item in column 2 is "#N/A", then it will put "START", if item in column 1 is "#N/A" and item in column 2 is a number it will put "END", otherwise if both columns are "#N/A", it will leave blank. Any ideas. Thanks. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula help
That's cool.
-- Brevity is the soul of wit. "Ron Coderre" wrote: Try this: With A1: (Start Value: a number or #N/A) B1: (End Value: a number or #N/A) This formula returns the status C1: =CHOOSE(SUMPRODUCT(--ISNUMBER(A1:B1)*{1,2})+1,"","start","end","Done") Does that help? *********** Regards, Ron XL2002, WinXP "A.S." wrote: Hello, Here is some sample data: 1 1 #N/A #N/A #N/A #N/A #N/A #N/A 12 #N/A #N/A 25 So basically, I am looking for a formula that will tell me if the item in column 1 is a number and item in column 2 is a number then it will put "DONE", if item in column 1 is a number and item in column 2 is "#N/A", then it will put "START", if item in column 1 is "#N/A" and item in column 2 is a number it will put "END", otherwise if both columns are "#N/A", it will leave blank. Any ideas. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Hybrid Mode
