How To Use Frequency Function In Excel

Geek School 4

In this lesson we'll discuss categories of functions – what they do and various examples – and to illustrate how they work, nosotros'll show you lot several example spreadsheets. We encourage you lot to follow along by creating your ain spreadsheets.

There are many other functions, of course, and past no ways are these the but ones that y'all should larn. Nosotros're just giving you a set of actually useful functions that you'll need to utilize on a regular basis. The next lesson will demonstrate some more than advanced functions that yous might employ, but a fiddling less often.

Math and Financial Functions

Once again, this is not a complete list of math or financial functions, merely an instance of a few that you should get to know. We recommend testing them in a spreadsheet of your own.

Functions

Purpose

SQRT

Square root, due east.g., SQRT(4)=ii because 2*2=4

PMT

Loan payment

DEGREES

Used by engineers, for example, to catechumen degrees (due east.g., 360⁰) to radians (e.g. 2π)

GCD

Finds the greatest-common divisor between two numbers. For case, GDC(5,15)=5, because 5 is the largest number that divides 15. iii also divides fifteen, but that is non the largest divisor they have in common.

RAND(23)

Generate a random decimal number between or equal to 0 and 1. Yous could use this for example, to option a contest winner (see example below). RANDBETWEEN is easier to use, since you do not have convert this decimal number to a whole number, due east.one thousand., 3 is easier to apply than 0.3.

Instance: Random Contest Winner

Here is an example of how you could use RANDBETWEEN(). Suppose we have eight employees in the office and the boss is giving away an iPad via a random cartoon.

Don't use a lid and pieces of newspaper to pick a winner, use high engineering such as Excel!

Make a spreadsheet such as the one below:

We use =RANDBETWEEN(1,8) in prison cell B10 to generate a whole number between 1 and eight.

Each employee's name is in the range A2:A9 since there are viii employees, and so we need to pick a random number between or equal to 1 and 8. The person whose number comes upwards wins the iPad.

You lot know the employee's position in the list – James is 1, Mark is ii, etc. Employ the Index function to go the employee's proper name to assign the randomly generated number to the corresponding name. This is how we thus selection our winner.

A2:A9 is the range.
B10 is the number of the employee who won.

In this case, ane means option the first column in the range and =Alphabetize() is a function that retrieves the value of a cell (Jam.es =one so he is our winner).

If yous want to pick some other name – such as if the winner must be present to win – so all you demand to do is change the spreadsheet. You can attempt information technology yourself, merely change the width of a column or add information. Anytime the spreadsheet updates, it regenerates a new random number and picks a new winner.

Logical Functions

Logical functions are used to test whether something is true or false.

Function

Purpose

If(<exam>,Ten,Y)If <test> is true, so the value is X, otherwise the value is Y.For example this formula =IF(C2=5,"C2=5″,"C2<>5″)Displays "C2=5" if C2=5, otherwise it will display "C2<>5"

AND

=AND(C3=v,C4=v)Displays "Truthful" if both C3=5 and C4=v

FALSE

Sets the cell to false

TRUE

Sets the cell to truthful

Appointment and Fourth dimension Functions

Dates are stored in excel as numbers, which means you can do math with them. You tin can use +1 to calculate the adjacent twenty-four hours and you can subtract 1 appointment from another to find how many days accept elapsed between two dates.

Formula

Event

Purpose

=NOW()

9/sixteen/2013

Get the current date and time

=DATEVALUE("ix/16/2013")

41533

Convert date in text format to number. This number is called the "serial number". Number i is Jan ane, 1900. 41,533 is 41,532 days after that or ix/16/2013.

=WEEKDAY(Now())

Extracts the date as a number. 1 is Sunday and 7 is Sat.

=Now()+1

9/17/2013

Since dates in Excel are stored every bit numbers, you can do math with them.

For example, Excel cannot simply add =nine/16/2013 + 1. Information technology must first convert it to a number that makes sense to information technology. In the screenshot, y'all see we first had to assign the DATEVALUE() function to the formula and then Excel knows the text'due south "value" is a "engagement."

Example: When to Accept Vacation

Hither is an instance that demonstrates how to use various date functions.

The boss says you can take vacation based upon your sales. If you make $1 meg in sales y'all become two weeks (10 working days). If you make $10 1000000 in sales you get three weeks (15 working days). Brand less than $i million in sales and you get a new job elsewhere.

You are a corking salesman, so you're planning to make at least $10 million in sales and do it quickly. You estimate it volition accept you lot l days to meet your quota. You lot want to project when yous tin go along holiday, so you make a spreadsheet.

To find the vacation date, you cannot simply add 50 days to the first of the year. Information technology has to be fifty working days – not including holidays. Further, since no i wants to go on holiday in the middle of the week, y'all have to find the following Saturday, unless the date calculated already is a Saturday.

How do you exercise this calculation? Let'due south look closely at the spreadsheet beneath:

If yous projection $fifteen 1000000 in sales, then enter that value in cell B2. Y'all also estimate it volition take 50 work days to accomplish that goal, which you enter into B3.

From here, we can calculate what date it will be 50 piece of work days from January i, and and then display the following Saturday'southward date – your beginning twenty-four hour period of vacation!

The formula =WORKDAY(B1,B3,A13:A20) adds B3 (50 work days) to B1 (January 1) excluding the holidays listed in the range A13:A20.

Next, you must detect the side by side Saturday. This is a bit tricky. The whole formula is =IF(WEEKDAY(B4)=7,7,7-WEEKDAY(B4))+B4.

To explain, retrieve that Friday and Saturday are the 6^thursday and 7^th week days, respectively. March 14, which is our projected first day we'll exist eligible to go on holiday is March 14 (cell B4), which is a Friday (half-dozen).

We then take the number 7 (Saturday) and subtract the value from B4.

Once Excel determines how many days' departure there is between your projected vacation day and the post-obit Sat, it adds that difference, in this instance 1, to the value in B4, and displays it in B5.

Once you determine when to go along holiday, you can effigy out when to come back. If your sales are greater than or equal to $i,000,000 and then you have 10 vacation days. If they are greater than or equal to $10,000,000, you get xv holiday days.

The formula we're going to utilize to decide our return date is =IF(B2>=A10,WORKDAY(B11,B5),WORKDAY(B11,B4)).

Here we first determine the value of B2 (sales made) and then compare it to the value in A10 ($10,000,000).

If the value is greater than or equal to $10,000,000, then the formula will add 15 days to cell B5 and print the issue in B6, which is your return date.

Otherwise, it volition add the value in B9 (10 days) and impress that in B6.

Nosotros don't care almost holidays because we will be on vacation, so we do non include them in the WORKDAY() function.

Sadly, as useful as this is, Excel still cannot tell you lot whether you will even want to render to piece of work one time your vacation ends!

Coming up Side by side …

Wrapping upwards today, you should have a decent working cognition of math and financial functions, logical functions, and appointment and fourth dimension functions. Tomorrow, in our final Lesson five to this grade, we will explore reference functions, the solver, financial functions, and how to create a pivot tabular array.