Excel f(x)s = Excel Functions
The powerful function If can already do a lot of tricks, but we can for sure do more when we use it with AND or OR functions.
Also, once we understand that, we can use the power of combining AND and OR inside the Logical test of IF, to make it even smarter
Once good example is when we need to see if a certain date is within the same month as this month like below…
We have already gotten used to use VLOOKUP, MATCH and other functions that generates errors inside an IF
But, we do not need that anymore
We can use the new guy, IFERROR instead, which will show the result if it did not generate error, otherwise will show something else if it resulted an error.
One of the power of combining functions in one cell is using the best of these functions
An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.
VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time
We needed several times to locate a row by using a column on the right, then get the value from a column left to it.
This is when VLOOKUP can not help us.
Excel 2010 was great because you can easily open another Excel session while your current session is working.
This allowed us to run multiple tools at the same time, since those two sessions are separated and do not touch.
However, starting Excel 2013, the interface changed, making it harder to run new session or open Excel while we have another Excel session running some long process.
Found this in an old file while doing some digital cleaning This will show you how you can easily count how many times a letter (or any string) into a cell. So the main formula doing that is … =LEN(I12)-LEN(SUBSTITUTE(I12,”o”,””)) Of course, this assumes you are looking to find how many the letter “O” is…
We needed that several times in the past Then I found it in an old file in my laptop, thought to share it for others to help So, when A1 has the start time, B1 has the end time Time different in one day will be from formula =IF(OR(A1=””,B1=””),””,ABS((HOUR(A1)+MINUTE(A1)/60)-(HOUR(B1)+MINUTE(B1)/60))&” hrs”) I added to prevent…
To do a search in a column or a row, MATCH is the function to look for It is very simple, you set the value to look for and where to look, then of course there is some another option to set how to do the search. So, we are looking for 87 in column…
You must already know that when you press Enter after you modify a cell, the cell content will be saved and you jump down to cell below (we can change that behavior by the way, but that is another post).
And you could have already know that if you want to have multiple lines inside a cell, you need to press ALT+ENTER to do that.
But, how can we do that in formula ??
I got a request on how to do that in a spreadsheet, so, the client wanted to convert two columns having two parts of address, Column C has Street address and street name, and column D has city, state and Zip code.
I don’t believe I don’t have this here yet. This is one of my oldest tricks
When we have to do links between two workbooks (One cell in Workbook1 to have value updated from a cell in Workbook2), we usually just do links like this…
Go to workbook1, select a cell, type in ‘=’ then go to Workbook2 and select a cell, then ‘ENTER’
To get something like this
Which will look like this if try1.xlsx is closed
However, if you open try2.xlsx alone before you open try1.xlsx, you get this message
Read More “Links between two workbooks – dynamically using functions” »
I have been asked for a while now to just put a simple VLookup function, here it is, I know this might be stupid to post it here, but we need to fill requests from all types of visitors.
Vlookup is the most famous function in Excel, some people actually refer to Excel as VLookup.
Even though I do not like that fact, especially when we have other powerful functions like INDEX, OFFSET, INDIRECT and others.
VLookup is going to search for an item in the left column of your table (or selection), as in the screenshot below, then bring me back the value in the same row where it find that item from another column.
The Green box (Lookup_Value) is what the function will search for, and the vertical green box is where, this is because we selected the range (Table_Array) in Blue as the area of D:G, so VLookup will search in D, this is the V part in VLOOKUP, we should also expect to see HLookup.
Means if item is not found in column D, VLookup will return as #N/A error
Excel 2010 has a lot of new exciting things, one of them is the ‘Engineering’ category of functions, this was usually a plugin we have to add to get, but in Excel 2010, it is already there.
The function ‘Convert’ does what you expect it, converts a number from one metric to another.
Of course, we cannot remember all metric systems and units, so just press F1, or click help to see this list to know how you can convert from one unit to another in any of the systems inside that function
The one I most interested is
=Convert( 45, "mi", "km")
More images …
