It took me 2 hours to write a logical formula, but I still could make it.
Basically, I want to make the program choose different cells from a few
columns to input into a new column depending on different situations.
Let's say, we have such columns as follows:

A B C D
4 0 20 #N/A
6 0 16 #N/A
8 1 9 1
10 0 9 #N/A
15 2 8 2
20 5 5 5
30 7 6 6.5
50 10 3 3
55 13 0 #N/A
60 15 2 2
65 16 0 #N/A
71 22 0 #N/A

First, I need to compare the values in column A with a constant number 30.
‘*If its greater than 30, I should input the value in column B with the same
row into column D; ‘¡if its less than 30, rather than choose data from column
B, I input the value in column C into column D; ‘¢if it equals to 30, I have
to use the average value of column B and C as the input into column D.
So basically, there are three different situations. However weve got some
constraints here. ‘£If the value in either column B or C is zero, dont input
any number into column D or show up as an error. ‘¤After encountering two
consecutive zero, dont select any other values.

Now you may know which logical formula's killing me. Please help me out.

I don't understand your last statement ... "2 consecutive zeroes"!

Where are these 2 consecutive 0's ... in a column, in a row?

Anyway, try this, and post back with comments on what's lacking:

=IF(OR(B1=0,C1=0),"",IF(A130,B1,IF(A1<30,C1,(B1+C 1)/2)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Yuanhang" wrote in message
...
It took me 2 hours to write a logical formula, but I still could make it.
Basically, I want to make the program choose different cells from a few
columns to input into a new column depending on different situations.
Let's say, we have such columns as follows:

A B C D
4 0 20 #N/A
6 0 16 #N/A
8 1 9 1
10 0 9 #N/A
15 2 8 2
20 5 5 5
30 7 6 6.5
50 10 3 3
55 13 0 #N/A
60 15 2 2
65 16 0 #N/A
71 22 0 #N/A

First, I need to compare the values in column A with a constant number 30.
‘*If its greater than 30, I should input the value in column B with the
same
row into column D; ‘¡if its less than 30, rather than choose data from
column
B, I input the value in column C into column D; ‘¢if it equals to 30, I
have
to use the average value of column B and C as the input into column D.
So basically, there are three different situations. However weve got some
constraints here. ‘£If the value in either column B or C is zero, dont
input
any number into column D or show up as an error. ‘¤After encountering two
consecutive zero, dont select any other values.

Now you may know which logical formula's killing me. Please help me out.

Thanks for the response. By "2 consecutive zeroes", it should be in a column.
In other words, if there are 2 consecutive zeroes showing up in a column, we
stop inputting data into column D, nomatter it's zero or non-zero number
thereafter.

"Ragdyer" wrote:

I don't understand your last statement ... "2 consecutive zeroes"!

Where are these 2 consecutive 0's ... in a column, in a row?

Anyway, try this, and post back with comments on what's lacking:

=IF(OR(B1=0,C1=0),"",IF(A130,B1,IF(A1<30,C1,(B1+C 1)/2)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Yuanhang" wrote in message
...
It took me 2 hours to write a logical formula, but I still could make it.
Basically, I want to make the program choose different cells from a few
columns to input into a new column depending on different situations.
Let's say, we have such columns as follows:

A B C D
4 0 20 #N/A
6 0 16 #N/A
8 1 9 1
10 0 9 #N/A
15 2 8 2
20 5 5 5
30 7 6 6.5
50 10 3 3
55 13 0 #N/A
60 15 2 2
65 16 0 #N/A
71 22 0 #N/A

First, I need to compare the values in column A with a constant number 30.
‘*If its greater than 30, I should input the value in column B with the
same
row into column D; ‘¡if its less than 30, rather than choose data from
column
B, I input the value in column C into column D; ‘¢if it equals to 30, I
have
to use the average value of column B and C as the input into column D.
So basically, there are three different situations. However weve got some
constraints here. ‘£If the value in either column B or C is zero, dont
input
any number into column D or show up as an error. ‘¤After encountering two
consecutive zero, dont select any other values.

Now you may know which logical formula's killing me. Please help me out.

I tried your formula in my spreadsheet, it doesn't work. The reason I may
guess is that you misunderstood the constraints I mentioned. It should be 2
consecutive zeroes in a column rather than a row. Thank you.

"Ragdyer" wrote:

I don't understand your last statement ... "2 consecutive zeroes"!

Where are these 2 consecutive 0's ... in a column, in a row?

Anyway, try this, and post back with comments on what's lacking:

=IF(OR(B1=0,C1=0),"",IF(A130,B1,IF(A1<30,C1,(B1+C 1)/2)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Yuanhang" wrote in message
...
It took me 2 hours to write a logical formula, but I still could make it.
Basically, I want to make the program choose different cells from a few
columns to input into a new column depending on different situations.
Let's say, we have such columns as follows:

A B C D
4 0 20 #N/A
6 0 16 #N/A
8 1 9 1
10 0 9 #N/A
15 2 8 2
20 5 5 5
30 7 6 6.5
50 10 3 3
55 13 0 #N/A
60 15 2 2
65 16 0 #N/A
71 22 0 #N/A

First, I need to compare the values in column A with a constant number 30.
‘*If its greater than 30, I should input the value in column B with the
same
row into column D; ‘¡if its less than 30, rather than choose data from
column
B, I input the value in column C into column D; ‘¢if it equals to 30, I
have
to use the average value of column B and C as the input into column D.
So basically, there are three different situations. However weve got some
constraints here. ‘£If the value in either column B or C is zero, dont
input
any number into column D or show up as an error. ‘¤After encountering two
consecutive zero, dont select any other values.

Now you may know which logical formula's killing me. Please help me out.

Hello,

You can array-enter into D1:
=IF(ROW()1+MIN(MATCH(1,($B$1:$B$999=$B$2:$B$1000) *($B$1:$B
$999=0),),MATCH(1,($C$1:$C$999=$C$2:$C$1000)*($C$1 :$C
$999=0),)),"",IF(B1*C1=0,1/0,CHOOSE(SIGN(A1-30)+2,C1,AVERAGE(B1:C1),B1)))

But I would prefer to enter only as a normal formula (non-array):
=IF(B1*C1=0,1/0,CHOOSE(SIGN(A1-30)+2,C1,AVERAGE(B1:C1),B1))
and to check for the ending criteria in a separate column (E).

Regards,
Bernd

Thank you very much. The first formula doesn't work. But the second one works
well except from the last constrant (two consecutive zeros). I think I will
just use this one, and then do some extra work manually.



"Bernd P" wrote:

Hello,

You can array-enter into D1:
=IF(ROW()1+MIN(MATCH(1,($B$1:$B$999=$B$2:$B$1000) *($B$1:$B
$999=0),),MATCH(1,($C$1:$C$999=$C$2:$C$1000)*($C$1 :$C
$999=0),)),"",IF(B1*C1=0,1/0,CHOOSE(SIGN(A1-30)+2,C1,AVERAGE(B1:C1),B1)))

But I would prefer to enter only as a normal formula (non-array):
=IF(B1*C1=0,1/0,CHOOSE(SIGN(A1-30)+2,C1,AVERAGE(B1:C1),B1))
and to check for the ending criteria in a separate column (E).

Regards,
Bernd

Hello,

Did you enter the first formula with CTRL + SHIFT + ENTER (not just
with ENTER)?

I tested it. It works (for me).

Regards,
Bernd

For the life of me, I don't see any difference in the returns of your second
formula and my formula, except where yours produces a #DIV/0! error where
mine returns a blank cell, in the rows where the OP's example displays the
#N/A error.

I read the OP as requesting *no* error messages.
<<<"If the value in either column B or C is zero, don't input any number
into column D or show up as an error"

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bernd P" wrote in message
oups.com...
Hello,

Did you enter the first formula with CTRL + SHIFT + ENTER (not just
with ENTER)?

I tested it. It works (for me).

Regards,
Bernd

Hello Ragdyer,

I agree. My formula might even be more complicated than yours.

But: My first one works with the ending criteria :-) (array-
entered...)

Regards,
Bernd