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!

 

[box]

CONCATENATE is for text, so I don't know why you think you need it here.

Also you can only stack 7 IF statements together.
Why don't you try a lookup table or using IFs like his:

=IF(condition1,formula,0)+IF(condition2,formula,0)+etc.

To simplify your formula, try:

=(TODAY()-(IF(A2="Compaq 2510p",$K$2,0)+IF(A2="Compaq 6730b",$K$3,0)+...))/365.25

 

[jim may]

If you are running xl2003 or prior I have concatenated full (start to finish) If statements with the "&" character -- and it works great.

For example:

=If(QT1, CT1, "Y","N") & If(QT2, CT2, "Y", "N") & If(QT3, CT3, "Y", "N")

xl2007 introduced CountifS and SumIfS
allowing for, "I think" unlimited tests (greater than 7).

 

[SteveO59L]

Or you could try tabulating your relationships and using vlookup()

 

[AD_Taylor]

To simplify the formula even more hopefully:

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

Then just add all of the conditions you want to test and their respective results to the Lookup function. As you can see the first value in the Lookup relates to the first cell address in the range specified.

 

[box]

Adam - Today() is supposed to be divided by 365.25 too. So amending your much more elegant formula:

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

If your labels happened to be in say, column S such that S2 was Compaq 2510p, S3 was Compaq 6730b, etc... you could use:

=(Today()-Lookup(A2,$S$2:$S$4,$K$2:$K$4)) / 365.25

 

[mobyzone]

CONCATENATE is for text, so I don't know why you think you need it here.

Also you can only stack 7 IF statements together.
Why don't you try a lookup table or using IFs like his:

=IF(condition1,formula,0)+IF(condition2,formula,0)+etc.

To simplify your formula, try:

=(TODAY()-(IF(A2="Compaq 2510p",$K$2,0)+IF(A2="Compaq 6730b",$K$3,0)+...))/365.25

I tried =VALUE(CONCATENATE,IF( etc but still didn't work... Don't know how the IF formula + another one will work either

 

[box]

I think you misunderstand concatenate. If cell A1 contains 123 and A2 contains 456

=CONCATENATE(A1,A2) returns 123456.

 

[mobyzone]

I think you misunderstand concatenate. If cell A1 contains 123 and A2 contains 456

=CONCATENATE(A1,A2) returns 123456.

Ah I see, how can I do 15 IF statements in the same formula?

 

[rpetkevi]

limit is 7