A simple calculator, how come we do not have one like this?
Category: IF
Skills Grid
A small Excel file to show how we can create a chart-like Excel sheet
Used in my Resume to show different skill sets and the level of expertise in each
IF with AND and IF with OR
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…
Calculate hours between two cells
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…
Workdays – Across months
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:
Week number to Month number
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
iframe in Excel (XLiFrame)
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:
Convert 2-column into wide (column-row) table
This Excel file will convert a table of two columns into a Column-Row table
Using functions only and auto-updated once the Main table update
In another word, analyze the table into wider form
I needed this one few days ago and you will need this one too.
The main formula that you need to use is:
Multiple-Validations (connected to each other)
Here is the free file for a special request by a client
He wanted to have multiple cells having Data – Validation to same list but minus the selected one
In English, once you select one from cell1, cell2 will bring the same list but without the selected one, then when you select another item is selected in cell2, cell3 will bring what left from the list.
Does that make sense, check out the screenshots
Read More “Multiple-Validations (connected to each other)” »
Insert Blank rows into table using functions
Here you will see how to work with functions to insert a blank row every certain number of rows in a table using a blank sheet to copy all values of that table into the new sheet Then using Copy > Paste Special to make them constants The new thing is that this is all…
Change Calculator – calculate change in multiple bills/coins
An Excel file that will determine what cash to return to the buyer (How much of each cash unit, or how many of Fifty dollars and how many of Twenty dollars and so on) in a very interesting way using as less functions as needed So, if you need to return 15.45, this file will…
Read More “Change Calculator – calculate change in multiple bills/coins” »