criteria 1(a,b,c), criteria 2 (T,F) - Results (3 answers) achievab

K

Kikkoman

in cell A1: from a dropdown list - choose a, b or c
in cell B1: from a dropdown list - true or false
in cell C1: Results will depend on A1 and B1 - give 1, 2 or 3
 
B

Bob Phillips

You don't say how so a guess

=IF(AND(A1="a",B1=TRUE),1,IF(AND(A1="b",B1=FALSE,2,3))
 
K

Kikkoman

sorry didn't make myself clear...

if choose a & T, result 0
if choose a & F, result 1
if choose b & T, result 0
if choose b & F, result 2
if choose c & T, result 0
if choose c & F, result 3

Thanks for helping
 
N

N Harkawat

=MATCH(A1,{"a","b","c"},0)*NOT(B1)



Kikkoman said:
sorry didn't make myself clear...

if choose a & T, result 0
if choose a & F, result 1
if choose b & T, result 0
if choose b & F, result 2
if choose c & T, result 0
if choose c & F, result 3

Thanks for helping
Click to expand...
 
K

Kikkoman

The problem just got a whole lot bigger...
if:
column a - choose "Region", "interstate", "overseas" meeting
column b - enter "No. of regional people attending to meeting"
column c - enter "no. of interstate people attending to meeting"
column d - enter "no. of foreign people attending to meeting"
column e - enter "total cost" of meeting
column f - calculate "Payable"
column g - calculate "not-payable"

Rule:
(if region people attend "region" meeting then, {if average cost is <101,
"not payable","payable"} "not-payable")
(if interstate people attend "interstate" meeting then, {if average cost is
<101, "not-payable","Payable"} "not-payable")
(if foreign attends any meeting- all "Payable")

example:
roll a1 = region, b1 = 1(region) , c1 = 1(interstate), d1 = 1(foreign), e1 =
$600 then f1 = 400 (region + foreign), g1 = 200 (interstate)

roll a2 = interstate, b2 = 2, c2 = 1, d2 = 1, e2 = $500
then f2 = 100 (foreign - Payable), g2 = 400 (interstate attending interstate
meeting but under <101, non payable)

roll a3 = oversea, b3 = 1, c3 = 1, d3 = 1, e3 = $303
then f3 = $101, g3 = $202

question:
what formula should be used in column f and column g?

thanks for all for your help
 
Top