If statements

[Guest]

I am trying to set-up a formula that will fill-in a value in a cell.

In a column (c7:c210) they are three value, either S, D, T. Based on what
alpha character is found, I want to insert a value, either 100 if S, 200 if
D, or 300 if T. I went to the bottom of the worksheet and set-up a location
for S(A212), D(A213), and T(A214) and a value for each in the adjacent 'B'
column.

I can get a single logic statement to work:

If(C7:c210,a212,b212)

and that works great for a single condition. But my question is how do I set
up a formula to cover all three conditions?

 

[JulieD]

Hi

not sure i'm fully understanding you ... the range C7:C210, does it have a
separate S,D,T in each row or is there only one occurance of one of these in
the whole area?

if you have a letter in each row and in column D of each row you want to
return the associated value - then with only three options i would use a
simple nested IF statement and not worry about the table or values in
A212,A213,A214

so in D7
=IF(C7="S",100,IF(C7="D",200,IF(C7="T",300,0)))
and then copy this down the rest of the column using the autofill handle
(bottom right hand corner of cel D7, when you see a + double click)

If, however either S, D, or T is going to be entered somewhere once in the
range C7:C210 and you want to return the associated value then
something along the lines of
=IF(COUNTIF(C7:C210,"S")=1,100,IF(COUNTIF(C7:C210,"D")=1,200,IF(COUNTIF(C7:C210,"T")=1,300,0)))

Hope this helps
Cheers
JulieD

 

[travelguy]

Julie:

Thank you, that solved the problem.

-----Original Message-----
Hi

not sure i'm fully understanding you ... the range C7:C210, does it have a
separate S,D,T in each row or is there only one occurance of one of these in
the whole area?

if you have a letter in each row and in column D of each row you want to
return the associated value - then with only three options i would use a
simple nested IF statement and not worry about the table or values in
A212,A213,A214

so in D7
=IF(C7="S",100,IF(C7="D",200,IF(C7="T",300,0)))
and then copy this down the rest of the column using the autofill handle
(bottom right hand corner of cel D7, when you see a + double click)

If, however either S, D, or T is going to be entered somewhere once in the
range C7:C210 and you want to return the associated value then
something along the lines of
=IF(COUNTIF(C7:C210,"S")=1,100,IF(COUNTIF(C7:C210,"D") =1,200,IF(COUNTIF(C7:C210,"T")=1,300,0)))

Hope this helps
Cheers
JulieD





.

 

[JulieD]

you're welcome and thanks for the feedback

Julie:

Thank you, that solved the problem.

 

[bobf]

-----Original Message-----
I am trying to set-up a formula that will fill-in a value in a cell.

In a column (c7:c210) they are three value, either S, D, T. Based on what
alpha character is found, I want to insert a value, either 100 if S, 200 if
D, or 300 if T. I went to the bottom of the worksheet and set-up a location
for S(A212), D(A213), and T(A214) and a value for each in the adjacent 'B'
column.

I can get a single logic statement to work:

If(C7:c210,a212,b212)

and that works great for a single condition. But my question is how do I set
up a formula to cover all three conditions?
.
why not a simple IF statement

if(c7="s",100,if(c7="d",200,300))