How to create an Arrayformula for combining multiple values in columns in Google Sheets

0
votes

I have a sheet (shared here) where I am trying to produce an age range based on combining values in two columns (age minimum, age maximum).

  • If both age minimum and age maximum are specified, I want to combine both values with a dash in the middle I.e. "10-15"
  • If no age minimum is specified, I want to display: "0-{age maximum}" I.e. "0-15"
  • If no age maximum is specified, I want to display: "{age minimum}+" I.e. "18+"

Problem #1: I built the below formula, which mostly works, but produces a dash "-" in the empty cells. I need to have blank cells when there is no age data specified.

=if(and(A2<>"",B2=""),A2&"+",if(and(A2="",B2<>""),"0-"&B2,(A2&"-"&B2)))

Problem#2: I need to wrap this in an arrayformula, so I don't have to manually copy and paste the formula when new rows are added, but my attempt (below) does not produce the desired results (see Age range (arrayformula) column):

=arrayformula(if(and($A2:$A<>"",$B2:$B=""),$A2:$A&"+",if(and($A2:$A="",$B2:$B<>""),"0-"&$B2:$B,($A2:$A&"-"&$B2:$B))))

I'm pretty much a noob when it comes to complex formulas, so I greatly appreciate the help ???? . thanks

1
google-sheetsformula

1 Answers

0
votes

I have found an answer to my question:

=arrayformula(IF(LEN($A2:A) + LEN($B2:B) = 0,,(if(($A2:$A<>"")*($B2:$B=""),$A2:$A&"+",if(($A2:$A="")*($B2:$B<>""),"0-"&$B2:$B,($A2:$A&"-"&$B2:$B))))))