A Complicated Logical Formula

[Yuanhang]

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 it’s greater than 30, I should input the value in column B with the same
row into column D; ②if it’s 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 we’ve got some
constraints here. ④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. ⑤After encountering two
consecutive zero, don’t select any other values.

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

 

[Ragdyer]

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(A1>30,B1,IF(A1<30,C1,(B1+C1)/2)))

 

[Yuanhang]

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.

 

[Yuanhang]

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.

 

[Bernd P]

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

 

[Yuanhang]

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]

Hello,

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

I tested it. It works (for me).

Regards,
Bernd

 

[Ragdyer]

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">>>

 

[Bernd P]

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