Excel: Nested IF's

[Excelerate2004]

Is it possible to nest the following IF's in an excel function or would i be easier to do this in VBA?

IF C2 = "C" Then
   C3 = "C" AND C4 = "C"
ELSEIF C2 = "CT" Then
   C3 = "C" AND C4 = "T"
ELSEIF C2 = "T" Then
   C3 = "T" AND C4 = "T"
ELSEIF C2 = " " Then
   C3 = "?" AND C4 = "?"

I'd like to be able to copy the code in nested format throughout a worksheet so that it automatically assigns the values.

Thanks for any help I can get.

 

[bluedragon2]

A formula can only change the status of the cell in which it is in. But:

In C3 you can have:
=IF(OR(C2="C",C2="CT),"C",IF(C2="T","T","?"))

and in C4:
=IF(C2="C","C",IF(OR(C2="CT",C2="T"),"T","?"))


[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[egodette]

You could nest it but VBA would be much simplier.

a nesting funcion would be IF(C2="C",C3="C" and C4="C",IF(C2="CT",C3="C" and C4="T",IF(C2="T"......

get the picture? Just nest the IF function in the false conditional area.

and don't forget to close all the (((((('s

See why VBA would be easier?

 

[SilentAiche]

Keep in mind you can only nest seven IF functions...

Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[TonyJollans]

Hi Excelerate2004,

If you really mean to check for a single space (as opposed to an empty cell) and cell C2 will never have any other values then you can use ..

[blue][tt] =IF(C2=" ","?",LEFT(C2,1))[/tt][/blue] in C3, and
[blue][tt] =IF(C2=" ","?",RIGHT(C2,1))[/tt][/blue] in C4

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Excelerate2004]

Hmm I'm thinking this would be best done in VBA, this is actually a small part of a larger IF statement that uses other letters as well. I think its just going to get to unwieldy using excel nested IF formulas

Thanks for all the above info...I'm off to write something in VBA.

Cheers

 

[jdhilljr]

You could use a VLOOKUP.

Set your data to:

A B C
'C' 'C' 'C'
'CT' 'C' 'T'
'T' 'T' 'T'

and so forth.

your formula would then be in C3
=VLOOKUP(C1,Sheet1!1:65536,2,0)
your formula would then be in C4
=VLOOKUP(C1,Sheet1!1:65536,3,0)

Good luck.

Jim

 

[bluedragon2]

Ways around that also SilentAiche

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SilentAiche]

Thanks Blue,

I posted in haste (always a bad idea). After logging out, I had this nagging feeling I had actually seen a workaround on TT (combining IFs with ANDs, perhaps?).

Must go now. I've the sudden urge to work on a pun connecting [red]Duds[/red] with [red]Threads[/red]. But I can't type while standing, and I can't sit due to the self-inflicted noodle lashing on my [blue]post[/blue]erior.

Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[xlbo]

Tim - correct - prolly posted by me !!

=IF(test1,true_result,"") & IF(test2,true_result,"") & IF(...etc etc ad nauseum

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question