I Just Don't Get Nested IF Statements

[FreshlySqueezed]

3 columns
1 row:

$X1
$Y1
$Z1


If there's a number in any of these, l want to trigger a concatenation involving the data in that specific cell.

The row will be filled in left to right. So, if X1 is empty, then Y1 and Z1 will be too.
If Y1 is empty, then Z1 will be empty too, but X1 may have data.
If Z1 is empty, then X1 and Y1 may have data


Typical data:

01 05 07 ---> Desired output: Cobalt - Day 01, Cobalt - Day 05, Cobalt - Day 07




Another example:

03 12---> Desired output: Cobalt - Day 03, Cobalt - Day 12





Formula l've been trying:

=IF(ISNUMBER($Z1),concatenation formula that includes data in x1 and y1 and z1,
IF(ISNUMBER($Y1),concatenation formula that includes data in x1 and y1 only,
concatenation formula for x1 only
)
)



Or alternatively, for the final "or else", i would add if X1 is a number, then just give the concatenation formula for x1 only, otherwise just state ""





BUT: None of this is working. I'm using LibreOffice in Excel 2003 mode. I know it's a very old version and l could easily go for a higher version emulation but please, what am l doing wrong?

This should be a no-brainer for me. I thought l knew nested IFs. Apparently it's done differently now though? But l tried a suggested alternative, where l have complete self contained IFs, in serial, but that didn't work either.

 

[stroffso]

By my understanding this should do it for you

=IF(ISNUMBER($Z1), X1&" "&Y1&" "&Z1,IF(ISNUMBER($Y1), X1&" "&Y1,IF(ISNUMBER($X1), X1,"")))

However you need to keep in mind what happens if there are numbers in multiple cells or rearrange the above to have in order of preference of what overrules what.

3 columns
1 row:

$X1
$Y1
$Z1


If there's a number in any of these, l want to trigger a concatenation involving the data in that specific cell.

The row will be filled in left to right. So, if X1 is empty, then Y1 and Z1 will be too.
If Y1 is empty, then Z1 will be empty too, but X1 may have data.
If Z1 is empty, then X1 and Y1 may have data


Typical data:

01 05 07 ---> Desired output: Cobalt - Day 01, Cobalt - Day 05, Cobalt - Day 07




Another example:

03 12---> Desired output: Cobalt - Day 03, Cobalt - Day 12





Formula l've been trying:

=IF(ISNUMBER($Z1),concatenation formula that includes data in x1 and y1 and z1,
IF(ISNUMBER($Y1),concatenation formula that includes data in x1 and y1 only,
concatenation formula for x1 only
)
)



Or alternatively, for the final "or else", i would add if X1 is a number, then just give the concatenation formula for x1 only, otherwise just state ""





BUT: None of this is working. I'm using LibreOffice in Excel 2003 mode. I know it's a very old version and l could easily go for a higher version emulation but please, what am l doing wrong?

This should be a no-brainer for me. I thought l knew nested IFs. Apparently it's done differently now though? But l tried a suggested alternative, where l have complete self contained IFs, in serial, but that didn't work either.

 

[FreshlySqueezed]

By my understanding this should do it for you

=IF(ISNUMBER($Z1), X1&" "&Y1&" "&Z1,IF(ISNUMBER($Y1), X1&" "&Y1,IF(ISNUMBER($X1), X1,"")))

However you need to keep in mind what happens if there are numbers in multiple cells or rearrange the above to have in order of preference of what overrules what.

Right, so what is wrong with this line:

IF(ISNUMBER($Z1),CONCATENATE($X1 ... $Y1 ... $Z1 ...),IF(ISNUMBER($Y2),CONCATENATE($X1 ... $Y1 ...),IF(ISNUMBER($X1),CONCATENATE($X1 ...),"")))

(the concatenation strings are not actually what l'm putting in the formula but they are meant to convey the ingredients l am putting into the resulting concatenation string)

 

[FreshlySqueezed]

Here is the actual formula (concatenation part slightly shortened for brevity, note also that the columns are actually N,O,P):

=IF(ISNUMBER($P413),CONCATENATE("/",$N413,"/",$O413,"/",$P413),IF(ISNUMBER($O413),CONCATENATE("/",$N413,"/",$O413),IF(ISNUMBER($N413),CONCATENATE("/",$N413),"")))

Yet it's just wrong!!!

 

[stroffso]

Rather then use Concatenate just use what I have replacing the references obviously where I have " " to "/" then it shouldnt be an issue

Here is the actual formula (concatenation part slightly shortened for brevity, note also that the columns are actually N,O,P):

=IF(ISNUMBER($P413),CONCATENATE("/",$N413,"/",$O413,"/",$P413),IF(ISNUMBER($O413),CONCATENATE("/",$N413,"/",$O413),IF(ISNUMBER($N413),CONCATENATE("/",$N413),"")))

Yet it's just wrong!!!

 

[FreshlySqueezed]

still not working

For clarity could you alter the formula l posted: =IF(ISNUMBER($P413),CONCATENATE("/",$N413,"/",$O413,"/",$P413),IF(ISNUMBER($O413),CONCATENATE("/",$N413,"/",$O413),IF(ISNUMBER($N413),CONCATENATE("/",$N413),"")))

And write it how you think it should be?

I have tried this: =IF(ISNUMBER($P413), "/"&$N413&"/"&$O413&"/"&$P413,IF(ISNUMBER($O413), "/"&$N413&"/"&$O413,IF(ISNUMBER($N413), "/"&$N413, "")))

No success

 

[FreshlySqueezed]

Solved it. The source columns (X Y Z aka N O P) were formatted to text.

Unfortunately, when l format them to number, they cannot show the leading zero for single digit numbers, which is essential in the resulting concatenations!!!

I can input 6, and it shows as 06, but the concatenated result has it as 6 again.

I can input 06 and it shows as 06, but the concatenated result has it as 6.

Blast!!!

 

[FreshlySqueezed]

Solved the final barrier - changed IF(ISNUMBER to IF(ISTEXT

That way, the leading zero stays in the concatenated string, though l have to manually enter it for each single digit number to begin with.

I'm happy. LOL at the thread tags.

 

[jolivanes]

Re: "l have to manually enter it for each single digit number to begin with."
You can do that with code also.