- Select A Blank Cell
- Select your range of cells, in my example below it’s cells A2:A20
- Hit Copy
- Hit Paste Special
- Click Add
- Hit Ok
Count The Most Frequently Occurring Entry In Your Data Set
Ever need to find out which value in your data set occurs most frequently?
Yes I have as well more often than I thought I wold when I learned this Excel tip. Well here is a really easy way to do it. Using the MODE function. Lets start with and example- I have a data set in my workbook MY_DATA.
The syntax of the function is
=MODE(MY_DATA)
This will return the value 123 as it occurs the most often in my data set.
Let’s take this even further and count the number of times the most frequently occurring value appears in the range i.e the frequency of the mode. The syntax of this is-
=COUNTIF(MY_DATA,MODE(MY_DATA))
This will return 4 as the modal value (123) appears four times in MY_DATA range.
MODE only works for numeric values and it will ignore cells that contains text.
Excel Tip- Add Comments To Your Excel Formulas
If you need to share your train of thought or logic in your excel formulas or need a reminder to yourself of the formula purpose then you can add comments directly to the formula itself.
After entering your formula all you need to is type +N(“Your Comment Here”)
For Example if you have a formula that produces an average selling price, the formula will look like this
=AVERAGE(B2:B7)+N(“Average Selling Price”)
See the result below. If you need to see what your formulae relate to, then you can do so without cluttering up the worksheet with comments…..
Use A Wildcard With A VLOOKUP Formula
Ever needed to use a VLOOKUP but don’t you know exactly how to spell what you are looking for, for example you may not remember how to spell a product name or the spelling of a Sales Person. Is it Josh Or Joseph?.
No worries though you can go a bit wild with the use of a wildcard.
Here’s an example to search for the Sales of the person whose name begins with Jos
As you can see VLOOKUP uses the wildcard to search for any sales person in Column C that begin with the letters Jos and returns their sales values.
Below are some more awesome uses of the wildcard with VLOOKUP using the same data set.
Do you think you would use wildcard in your VLOOKUP formula?
YouTube Video- Create Your First Pivot Table
Calculate the Date After A Specified Number of Workdays In Excel
If you use Excel to project manage then you will know that most people work a five day week – Monday to Friday (if they are lucky). So if you have a project start date and the number of days it will take to complete then if you just added the number of days to the start date, it would not take into account the weekend days in your project.
By using the =WORKDAY() formula allows you to specify a beginning date, the number of workdays until the end of the project.
An example.
My VBA project has a start date of 01 July 2013 and I know it will take 56 days of resources.
So, as you can see below I have entered into cell C5 the formula =WORKDAY(C3,C4) which calculates 56 workdays or Monday though Fridays for 56 days.
Excel has therefore calculated my estimated finish date which is 17 September 2013- EASY!
- « Previous Page
- 1
- …
- 49
- 50
- 51
- 52
- 53
- …
- 55
- Next Page »