Re: Understanding this formula:=IF(TC[[#This Row],[Page]]="/","Home",IF(TC[[#This Row],[Page]]="/blog/","Blog Home",IF(ISNUMBER(SEARCH("/blog/category/",TC[[#This Row],[Page]])),"Blog Category&am
Hi @
Pingbat, sounds like you need some really good basic training in Excel formulas. I can recommend YouTube Excelisfun.
But in broad terms, this is what you need to know. All functions in Excel have the same syntax - so once you have learned one, you can use them all. In generic terms, any function can be written like this:
=FUNCTION(argument1, argument2, … argn)
where:
The first character must be
= as this is a formula. This is not strictly part of the function, and does not appear before internal (nested) functions)
FUNCTION is the name of the function, whatever it might be. There are about 500 of them - take your pick!
Arguments are the pieces of information that your chosen function needs in order to do its job. 500 different functions, 500 different jobs. You can have from 0 to 255 arguments.
0 arguments e.g. TODAY(), NOW(), PI()
1 argument e.g. AVERAGE) - B3:B8 is a RANGE of cells, expressed by giving top left : bottom right.
2 arguments e.g. ROUND(B9,2)
3 arguments e.g. IF(condition to test, result if test is TRUE, result if FALSE)
etc.
Which brings me to your formula:
=IF(TC[[#This Row],[Page]]="/","Home",IF(TC[[#This Row],[Page]]="/blog/","Blog Home",IF(ISNUMBER(SEARCH("/blog/category/",TC[[#This Row],[Page]])),"Blog Category",IF(ISNUMBER(SEARCH("/blog/",TC[[#This Row],[Page]])),"Blog Post","Other"))))
First, let me replace
TC[[#This Row],[Page]]
with TCP (!) for ease of typing - and reading (don't do this in Excel, though, it will break your formula!). (BTW if you upgrade from Excel 2007, you'll find that they tidied up Table references in later versions and they become a lot more readable!). So your formula is now:
=IF(TCP="/","Home",IF(TCP="/blog/","Blog Home",IF(ISNUMBER(SEARCH("/blog/category/",TCP)),"Blog Category",IF(ISNUMBER(SEARCH("/blog/",TCP)),"Blog Post","Other"))))
So what we have is:
=IF(TCP="/", "Home", ...
If TCP is "/" put the entry "Home" Otherwise...
IF(TCP="/blog/","Blog Home", ...
… if TCP is "/blog/", put "Blog Home", Otherwise...
IF(ISNUMBER(SEARCH("/blog/category/",TCP)),"Blog Category", ...
... if what you get when you look for the partial string "/blog/category/" is a number (the start position), put "Blog Category", Otherwise ...
IF(ISNUMBER(SEARCH("/blog/",TCP)),"Blog Post", ...
... if your search finds "/blog/", put "Blog Post" - (note that you have already eliminated Blog Category), Otherwise ...
"Other"
Put "Other"
)
Close the bracket for the 4th (inside) IF function as it is now complete. This was the final argument in the 3rd IF function so ...
)
Close the bracket for the 3rd IF function as it is now complete. This was the final argument in the 2nd IF function so ...
)
Close the bracket for the 2nd IF function as it is now complete. This was the final argument in the 1st IF function so ...
)
Close the bracket for the 1st IF function as it is now complete.
Phew! I hope you followed that! The brackets can be confusing, but Microsoft tries to help by colour-coding them. Remember that each function is following the rule: function name, open bracket, arguments, close bracket. Also, if you click inside a formula, you will see a yellowish tooltip box, describing the function you are in. Everything in this is a hyperlink: the function name will take you to Help on that function, while the arguments will highlight exactly the part that is that argument.
Good luck and Happy Excelling!
Claire
==
