Using if() to made varied concatenate() formulas easier

In the process I talked about last week, there were a lot of different styles that the cells could have, and there were a few different situations the cells could be in. In the example I posted, I manually put the different types of codes where they needed to be. However, I could have used if() to make my life easier in a couple of ways.

If I had used if() statements, I could have had the same formula in all of the cells and used logic to make it produce the right format, thus completely eliminating the risk of putting the wrong formatting in a given cell. Doing this would also have the advantage that you could see the coding for the format even without being in the cell, which makes it much easier to edit and compare to the other formats.

Here’s how I could have done it:

Step 1: Count all of the possible formats
In this case there’s a format for the row and column headers, and formats for the two alternating rows. That’s three right there. However, the “Total” column picks up the colors from the rest of the row, but has a different style of font. That means there’s another three, for a total of 6. The “Total” row also has multiple formats, but they’re the same as the ones in the column. Lastly, there’s the upper right and lower left corner, which don’t have borders, making them a 7th formatting option.

Step 2: List brief names for all the formats, in a column
You can do this wherever you want, but if you’re comfortable using ctrl-pageup and ctrl-pagedown to switch between worksheets, I recommend doing it in another worksheet. That way it’s easy to toggle between the code and the results, to see whether any changes you made had the result you want, without losing track of which cell you were changing.

For my table a good set of names might be: Header, Normal1, Normal2, Total_Header, Total1, Total2, and Blank.

Step 3: Name the cell next to each format name, with that name
In other words, if Header is in A1, then A2 would be named “Header”.

Step 4: Enter the opening HTML tag for each style in the appropriate cell
For example, the opening tag for the Header cell of the Total column would go in the Total_Header cell. Since the cell is supposed to have a blue background, a thick border, and center-aligned italic text, the tag might be:

I recommend two things at this point:
1) Putting your formatting from most common to least common. For example, every format has a border specified, so that’s the first one. Only some formats have the background color specified, so that’s towards the end, and the same is true of italic font.

2) Sort your list of tags, if you haven’t already, to group similar ones together. For example, Total_Header is very similar to Header, except that it’s italic instead of bold. Likewise Total1 and Total2 are just Normal1 and Normal2 with italics added. So if you move up and down between any pair, the only difference should be the addition of “font-style:italic;”. Alternatively, if you put italic before background color, sort it so that all the “Total” ones are together. Or you could do what I ended up doing and breaking it into two columns, so I can move right/left to check the normal code vs the total code, and up/down to check the first style against the 2nd and the 2nd against the header.

Doing this is really surprisingly helpful. I discovered that I’d entered “text-align:center;” twice in one tag, omitted the “border:” from the beginning of another, and managed to insert a stray semicolon into yet another one.

Step 5: Set up your if() statement
If there were only two types of formatting, this would be MUCH simpler. For example, if you just wanted alternate rows to be different styles, the formula could be something like:
=if(mod(row(),2)=0,style1,style2)
Remember, mod() just takes the remainder of the first number, divided by the second one. The function row() is similar to rows(), but instead of telling you how many rows are in a range, it tells you which row the formula is in. So that formula just says that if the number of the row is even, it should have style1, and if it’s odd it should have style2.

Similarly, if you wanted a checkerboard pattern you could use the formula:
=if(mod(column()+row(),2)=0,style1,style2)

If we just had formatting that completely overrode other formatting, such as the formatting for the headers, the formula would be just a little more complicated:
=if(OR(row()=1,column()=1),headerstyle,if(mod(row(),2)=0,style1,style2))
That’s just saying that if it’s the first row or the first column (assuming your table starts in A1 — you should adjust it if it starts elsewhere), then it should have the style of the header. Otherwise, if it’s an even row it should have style1, and if it’s not then it should have style2.

However, for the table I created, the if() formula is…intimidating, to say the least:
=IF(OR(AND(ROW()=1, COLUMN()=1), AND(ROW()=12, COLUMN()=18)), Blank, IF(OR(ROW()=1, COLUMN()=1), IF(OR(ROW()=12, COLUMN()=18), Total_Header, Header), IF(OR(ROW()=12, COLUMN()=18), IF(MOD(ROW(), 2)=0, Total1, Total2), IF(MOD(ROW(), 2)=0, Normal1, Normal2))))

Fortunately, there’s an easier way. You just have to think about it somewhat indirectly.

The items you’re concerned with are whether it’s a header, whether it’s a total, whether it’s a corner, and whether it should be highlighted or not. That’s four things, and you can treat them as four digits of a primary number.

So you can now break it into essentially a binary number that represents all of these variables. Assign each variable to a digit, and you’re good to go. All you need now is two relatively simple formulas: one to find the number, and one to find the style associate with that number.

style=if(or(and(row()=1, column()=1), and(row()=12, column()=18)), 1000, mod(row(), 2)+if(or(row(), column()=1), 10, 0)+if(or(row()=12, column()=18), 100, 0)

The results of this could be 0, 1, 10, 11, 100, 101,1 10, 111, or 1000. So the second formula could be:

=if(style=1000,blank, if(style>=110,total_header, if(style=101,total2, if(style=100,total1, if(style>=10,header, if(style=1,style2,style1))))))

You could make it even simpler by using a choose() statement or a vlookup() statement. I’ll discuss those in future posts, but in the meantime the formulas would be:
=choose(bin2dec(style)+1,style1,style2,header,header,total1,total2,total_header,total_header,blank)

=vlookup(table_styles,style,2,0) Note: this requires a table named table_style where the first column is the style numbers and the second column is the styles for those numbers.