Help with Concatenate

[mobyzone]

Hi all,

Should be a nice easy one for you experts, i'm trying to do more than 7 IF statements and I understand I need to use Concatenate to get this to work.. however its not working for some reason! Formula is as below

=CONCATENATE(IF(A2="Compaq 2510p",(TODAY()-$K$2)/365.25,IF(A2="Compaq 6730b",(TODAY()-$K$3)/365.25,IF(A2="Compaq 6910p",(TODAY()-$K$4)/365.25,IF(A2="Compaq 6930p",(TODAY()-$K$5)/365.25,IF(A2="Compaq dc7100",(TODAY()-$K$6)/365.25,IF(A2="Compaq dc7600",(TODAY()-$K$7)/365.25,IF(A2="Compaq dc7700",(TODAY()-$K$8/365.25)))))))))

The minute I add another IF statement it just errors. Any ideas?

Thanks so much!

 

[mobyzone]

limit is 7

I seen on a page though if you want to do more than 7 IF statements you need to CONCATENATE them?

 

[box]

Try what I posted in #2 or #8.

If you wanted to test if A2 contained a letter from A to Z, and returned the corresponding number (i.e. A=1 and Z=26) with multiple IF statements, you could use:

=IF(A2="A",1,0)+IF(A2="B",2,0)+IF(A2="C",3,0)+...+IF(A2="Z",26,0)

Now let's put "D" in A2. All the IF statements return 0 except the IF(A2="D",4,0) one so the sum of all the IF statements will be 4. This is a way to "stack" 15 IF statements.

 

[mobyzone]

Try what I posted in #2 or #8.

If you wanted to test if A2 contained a letter from A to Z, and returned the corresponding number (i.e. A=1 and Z=26) with multiple IF statements, you could use:

=IF(A2="A",1,0)+IF(A2="B",2,0)+IF(A2="C",3,0)+...+IF(A2="Z",26,0)

Now let's put "D" in A2. All the IF statements return 0 except the IF(A2="D",4,0) one so the sum of all the IF statements will be 4. This is a way to "stack" 15 IF statements.

We have 15 Models of machines and basically if a machine matches the list it returns a value after a calculation

 

[SteveO59L]

Tabulate the relationship and use vlookup()

 

[box]

Let me be clear. The answer has already been posted.

Take this formula:

=(TODAY()-LOOKUP(A2,{"Compaq 2510p","Compaq 6730b","Compaq 6910p"},$K$2:$K$4))/365.25

After "Compaq 6910p", add your 12 other models with commas in between and speech marks. Change $K$4 to $K$16.

 

[VoG]

This may help you http://www.cpearson.com/excel/nested.htm

 

[mobyzone]

Let me be clear. The answer has already been posted.

Take this formula:

=(TODAY()-LOOKUP(A2,{"Compaq 2510p","Compaq 6730b","Compaq 6910p"},$K$2:$K$4))/365.25

After "Compaq 6910p", add your 12 other models with commas in between and speech marks. Change $K$4 to $K$16.

WOW THAT WORKED :D thanks so much!!!!!