I Just Don't Get Nested IF Statements

FreshlySqueezed

New Member
Joined
Sep 22, 2019
Messages
10
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
128
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

New Member
Joined
Sep 22, 2019
Messages
10
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

New Member
Joined
Sep 22, 2019
Messages
10
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

Board Regular
Joined
Jul 12, 2016
Messages
128

ADVERTISEMENT

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

New Member
Joined
Sep 22, 2019
Messages
10
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

New Member
Joined
Sep 22, 2019
Messages
10

ADVERTISEMENT

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

New Member
Joined
Sep 22, 2019
Messages
10
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,826
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Re: "l have to manually enter it for each single digit number to begin with."
You can do that with code also.
 

Forum statistics

Threads
1,144,370
Messages
5,723,960
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top