ExcelBanter - Need formula help

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Need formula help (https://www.excelbanter.com/excel-discussion-misc-queries/124671-need-formula-help.html)

A.S.

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.

Dave F

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.

A.S.

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.

Ron Coderre

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.

A.S.

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.

Dave F

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.

Dave F

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.

A.S.

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

CLR

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

All times are GMT +1. The time now is 09:19 PM.