Formula based on 3 True or False values

Posted by Alan H on April 01, 2001 3:29 PM

I have 3 cells which read values of TRUE or FALSE from 3 different option buttons on a user form with only one reading TRUE at one time. I have another cell which I aiming to make dependent on the 3 TRUE or FALSE values. If one cell reads TRUE I want the cell to perform one calculation based on the value of 2 other cells, and likewise if the 2nd cell reads TRUE I want another calculation to be performed. If the 3rd cell reads TRUE I want a value of zero to be shown. I've tried using an IF statement like =IF ("B1"=TRUE,(("A1"/2)^2)*"C1",(("B1"/2)*"C2") which only works fine if there are only 2 cells to be used but not for 3. I've also tried nesting but without success. Any ideas anybody? Perhaps in VBA? Solutions gratefully received. Alan H.

Posted by Aladin Akyurek on April 01, 2001 3:51 PM

Alan

What are the refences of these 3 cells: A1, B1, and C2?

Which cells are exactly the first, the second, and the third?

Aladin

Posted by Alan H on April 02, 2001 1:18 PM

The 3 references for the cells with the TRUE or False values are B1, B2 & B3.

Sorry, the ("B1"/2) in the formula should read ("A1"/2). Cells A1, C1 & C2 contain numerical values.

Thanks, Alan H

Posted by Aladin Akyurek on April 02, 2001 2:19 PM

Alan

I'm assuming that you want the following:

B1 TRUE
B2 FALSE
B3 FALSE
==> computation1

B1 FALSE
B2 TRUE
B3 FALSE
==> computation2

B1 FALSE
B2 FALSE
B3 TRUE
==> computation3

B1 FALSE
B2 FALSE
B3 FALSE
==> no computation

I'm not sure about the computations involved so cooked up some of them, but you can adapt the following to your situation:

=IF(AND(B1,B2+B3=0),(A1/2)^2,IF(AND(B2,B1+B3=0),(A1/2)*C2,IF(AND(B3,B1+B2=0),SQRT(C1)*A1,"")))

Is this what you asked for?

Aladin

Posted by Alan H on April 03, 2001 1:14 PM

Thanks Aladin. With a little bit of tinkering about it worked just fine.