Excel f(x)s = Excel Functions
Welcome to WordPress. This is your first post. Edit or delete it, then start writing!
Get total number of days per month
This was a question from one of my friends, This set of formulas will calculate how many days (Networkdays) in each of the months in the list for a given Start and End dates
If Range of dates are across multiple years, user can copy columns C, D, E and F to fill those years
The months for the list are automatically populated starting from Jan of the year of the start date
The main formula in F7 should be:
I need that more often than I thought It is basically extract the full workbook name that we are in, with no folders, and with no extension =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) So, here it is…. And if we want to go 1 step further, I often have the version of the tool as part of the filename,…
Now we need to convert week number to month number
This is exactly the same as week number to date (reverse weeknum)
So, when the week number is in A5, then formula below will get you the month number
I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT()
Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things changed
Along with ADDRESS, ROW and COLUMN and some text tricks like SEARCH, LEFT, LEN, RIGHT, etc, you can do a lot.
You see INDIRECT does a simple job
So…
If you do
=INDIRECT("G4")
It gives you the value in G4
Notice that “G4” has the double-quotations, means
=INDIRECT("G"&SUM(S1:S4))
Excel already has the formula ROW()
=Row()
Which as we may already know will give us the number of the row we are in, if you add no parameters
However, the COLUMN() is kind of tricky
Because of the fact that cell addresses (or references) are based on the old style of R1C1
=Column()
Read More “Get column name (columnname as A,B,C, etc) as input inside cell” »
This was a request from one of my work managers
She wanted to convert a week number she got in a column into thier dates
Excel already have the WEEKNUM that converts a date into its week number, but the reverse is what needed here
So after my mouth was drooling while she was asking her question, I started a head, and below is the result formula
You just need to place the week number in cell B3, then paste this formula anywhere:
Convert one long column into two
This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column
This was the answer for this question
Yahoo! Answers question http://answers.yahoo.com/question/index?qid=20081225182043AAc7ZEB
Show two columns in one page as four columns using functions only. This will help you to show the next x number of rows for a certain column to the next of the first one to reduce the number of pages to print.
This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.
Formulas used to bring actual data is:
=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))
While formula used to bring header labels:
Small file that will show how to do INDEX + MATCH formulas to get the abbreviation of the state based on its name or the name of the state based on its abbreviation
Main formula to do that is:
=INDEX(Abbr_States,MATCH(B4,States,0),1)
Enter a zip code, and Excel will tell you in which state it is
It also has a list of all zip codes for all states and a technique (INDEX and MATCH functions) to get the state from a given zip code.
Simple file to help learning INDEX and MATCH
The formula used is: =INDEX(Data!$D:$D,MATCH(TEXT(Search!C4,"00000"),Data!$C:$C,0),1)
