Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Untitled
CA11_Sol_1108
Search document
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Table of Contents
Tip 1: Calculating the Number of Months Service for an Employee Tip 2: Using Custom Format Tip 3: Formula Auditing – Showing Cell Dependencies Tip 4: Finding the Cell with the Highest Value in a Range Tip 5: Combining Text From Multiple Cells into One Tip 6: Use MATCH MATCH and INDEX as an Alternative to The Vertical Lookup Tip 7: Adding 7: Adding Criteria/Conditions to Your Your Sum Function Tip 8: Sumif Between Workbooks Tip 9: Positive and Negative Numbers Tip 10: Formula to Find Duplicate Values in a Data Range Tip 11: Generating of Random Numbers for Testing of Formulas Tip 12: Convert Function Tip 13: Calculating the Periodic Payment for a Loan Tip 14: Using the PMT Function to Reach a Target Tip 15: The SUMIF Function Tip your 16: Thesemester SumIF Function Master with Scribd Read Free Foron 30this Days Sign up to vote title Tip 17: Making Forecasts with the Forecast Function & The New York Times Useful Not useful Special offer for18: students: Only $4.99/month. Tip IF Function
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
1
Download
of 58
Untitled
CA11_Sol_1108
Search document
Tip 1: Calculating the Number of Months Service for an Employee
By using the DATEDIF DATEDIF function one is able to calculate the number month’s month’s service for an employee. Below we explain how: Applies To: To: MS Excel 2003, 2007 and 2010
Use the “DATEDIF” function 1. The date employed is in cell A3 and the current date is in cell B3.
2. Select cell C2 and enter = (DATEDIF(A2,B2,”Y”)*12)+DA (DATEDIF(A2,B2,”Y”)*12)+DATEDIF(A2,B2 TEDIF(A2,B2 3. Format to number.
Master your semester with Scribd e a l u & r The New York Times m o Special F offer for students: Only $4.99/month. s
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 2: Using Custom Format
How to format a number without decimal places, with a thousands tor, tor, and combine it with text, using custom formatting and joining c Applies To: To: MS Excel 2003, 2007 and 2010
1. For example, Cell A1 contains “You “You owe” and cell B1 contains the valu 87777. In cell C1 enter the following formula =A1&” “&TEXT(B1,”$#,##0”).
Master your semester with Scribd e a l u & r The New York Times m o Special F offer for students: Only $4.99/month. s
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 3: Formula Auditing – Showing Cell Dependencies
Some spreadsheets can get very complicated, with many cells rely on other cell calculations to deliver information and a change of on can have dramatic effects. Formula Auditing will show you which c are connected. Applies To: MS Excel 2003, 2007 and 2010
1. To trace all preceding cells: a. Select the desired cell. b. From From the the Formulas Formulas tab, in the the Formula Formula Auditing group, select Trace Precedents. c. If the Precedent cells are found on another worksheet, you will get get dotted line. d. Double click on the dotted line, select the reference, select OK.
2. To trace all dependant cells: a. Select the desired cell. b. From From the the Formulas Formulas tab, in the the Formula Formula Auditing group, select Trace Dependents.
3. To remove all the arrows: a. Select the desired cell. Master your with e b. From Fromsemester the the Formulas Formulas tab,Scribd in the the Formula Formula Auditing group, selectRemo a Read Free Foron 30this Days Sign up to vote title l u Arrows. & The New York Times Useful Not useful m r o Special F offer for students: Only $4.99/month. s
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 4: Finding the Cell with the Highest Value in a Range
At times you may be working with data where you need to nd the mum value. To do this, you can sort or use the MAX function. You m however, however, might not want to sort the column and are looking for the value, but would like to know the cell address that contains the ma mum value.
Example:
MATCH: MATCH (lookup_value,lookup_array,match_type) MATCH: MATCH Theyour MATCH MATCH function will return the row number of the highest value. Master semester with Scribd e a l u m r o Special F offer for students: Only $4.99/month. s
& TheLookup_value: New York Times
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Is the value you use to nd the value you want in a table. Lookup_value
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
ADDRESS: ADDRESS (row_num,column_num,abs ADDRESS: ADDRESS (row_num,column_num,abs_num,a1,sheet_text) _num,a1,sheet_text) The ADDRESS ADDRESS function will return the cell address of the highest value. Row_num: Is the row number to use in the cell reference. Column_num: Is the column number to use in the cell reference.
Abs_num: Species the type of reference to return. 1- Absolute, 2- Absolute row; re column, 3- Relative row; absolute column, 4- Relative MAX: MAX (number1,number2,...) Number1, number2: Are 1 to 255 numbers for which which you want to nd the maximum maximum value. Applies To: MS Excel 2003, 2007 and 2010
1. Open Microsoft Excel®. 2. Select the desired result cell. 3. Select the Insert Function Function button button on the Formula Formula bar. bar. 4. In the Row_num box, enter in the below: 5. MATCH(MAX MATCH(MAX (“Column (“Column to calculate max”),”Column max”),”Column to nd the max, Master your semester with Scribd e the rst value that is exactly equal). a Read Free Foron 30this Days Sign up to vote title l u & r The New York Times Useful Not useful m o Special students: $4.99/month. 6.for In the Only Column_num F offer s
box, enter the below. below.
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Master your semester with Scribd e a l u & r The New York Times m o Special F offer for students: Only $4.99/month. s
Untitled
CA11_Sol_1108
Search document
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 5: Combining Text From Multiple Cel into One
Let’s say you have imported Main Account and Sub Account numbe into separate columns you can actually combine the two to have th New Account. All this can be done by way of a simple formula.
Using the &(ampersand) sign is the same as using the Concatenate function, but much simpler. Below is an example in column C, of where Main Account Account and Sub Account numbers need to be joined into one cell a /(forward slash) to separate the Accounts. Applies To: MS Excel 2003, 2007 and 2010
1. Select the desired cell (C2). 2. Enter =. 3. Select the rst cell to join (A2). 4. Enter in &.
5. If necessary, necessary, add any additional data that my not be found in a cell (“/” 6. Enter in &.
Master your semester with Scribd e a l u SelectYork any additional & r The7.New Times cells to join (B2). m o Special F offer for students: Only $4.99/month. s
8. Press Enter.
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 6: Use MATCH and INDEX as an Alter to The Vertical Vertical Lookup
Vertical Lookup is one of the commonly used MS Excel functions. Bu limitations in that the main search criterion needs to be in the rst However by using a combination of MATCH MATCH and INDEX, you can retu values from an array regardless of what information is in the rst col the array. Follow our example below as we explain how you can use M and INDEX as an alternative to the Vertical Lookup.
MATCH: Returns the relative position of an item in an array that matches specied value in a specied order. order.
INDEX: Returns a value or reference of the cell at the intersection of a p row and column, in a given range. Applies To: To: Excel 2003, 2007 and 2010
1. Reference will be made to the screen shot below. below. We are going to retrieve the Commission Rate for P7.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
4. Select as below.
• In the rst option the data array is only based on one data range • In the second option the data array is based on multiple data ranges
e Master your semester with Scribd a l u m EnterYork as below. & r The5.New Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
6. Select OK. 7. The answer will be 22% as given below. below.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 7: Adding Criteria/Conditions to Your Sum Function
By using the DSUM function, you can specify criteria and condition regarding which cells should be added together. An alternative to u DSUM is using SUMIF, SUMIF, but SUMIF is not suitable for complex criteri
The list below shows monthly and daily product sales. In cell G5 we calculated a running total using the DSUM function, which takes int count a number of criteria that has been set up in the range A1:D2
We will use the DSUM function to calculate the total sales that mee following criteria: Monthly sales for February that are greater than $ where the weekday is Tuesday and the product is Ipoh Coffee.
Example:
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Field: Indicates which column is used in the function. Field can be given as tex the column label enclosed between double quotation marks, such as “Ag “Yield,” or as a number that represents the position of the column within list: 1 for the rst column, 2 for the second column, and so on.
Criteria: Is the range of cells that contains the conditions you specify. specify. You You can us range for the criteria argument, as long as it includes at least one colum and at least one cell below the column label for specifying a condition fo column. Applies To: To: MS Excel 2003,2007,2010 and 2013
1. Select the cell G5. 2. Enter the formula below: =DSUM (A5:D19,4,A1:D2) (A5:D19) is the database, (4) is the eld eld number number for sales, (A1:D2) is criteria range. 3. Press Enter. 4. The answer will be $2,400.00. e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 8: Sumif Between Workbooks
When using the SUMIF function between workbooks, you may get a VALUE error if the source workbook is not open. To work around th a combination of the SUM and IF functions together in an array form
Example The source workbook, lename Data:
e Master your semester with Scribd a l u m & r TheThe New York Times workbook with the formula: o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
This behaviour occurs when the formula that contains the SUMIF, COUN COUNTBLANK function refers to cells in a closed workbook. If you open the referenced workbook, the formula works correctly. correctly. • Instead of using a formula that is similar to the following =SUMIF([Source]Sheet1!$A$1:$A$8,”a”,[Source]Sheet1!$B$1:$B$8)
• use the following formula: =SUM(IF([Source]Sheet1!$A$1:$A$8=”a”,[Source]Sheet1!$B$1:$B$8,0)
An array formula is a formula formula that can perform multiple calculations calculations on o more of the items in an array. array. Array formulas act on two or more sets of v known as array arguments.
• Each argument within an array must have the same amount of rows columns • You You must enter an array by pushing Ctrl + Shift + Enter • You You cannot add the {} (braces) that surround an array yourself, pushing Shift + Enter will do this for you. SUMIF: SUMIF(range,criteria,sum_ SUMIF(range,criteria,sum_range) range)
Range : Is the range of cells that you want evaluated by criteria. Cells in each ran must be numbers or names, arrays, or references that contain numbers. and text values are ignored.
e Master your semester with Scribd a l u m & r TheCriteria New :York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Is the criteria in the form of a number, expression, or text that denes wh cells will be added. Forexample, criteria can be expressed as 32, “32”, “>
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Applies To: MS Excel 2003, 2007 and 2010
1. Open the workbook that contains the source (Data). 2. Open the workbook that will contain the formulae.
3. Select the desired cell in the workbook that will contain the formulae ( 4. Using the FX button on the Formula Bar, locate the Sum Function.
5. To nest in the IF Function, Functio n, from the Formula bar, in the Name Box, fro e the drop down arrow, select IF. If the IF function does not appear, sele Master your semester with Scribd a l Read Free Foron 30this Days Sign up to vote title u More Functions and locate the IF Function. m
& r The New York Times o F offer for students: Only $4.99/month. Special s n
6. Enter in the arguments argument s in the Logical Test. Test.
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 9: Positive and Negative Ne gative Numbers
Have you ever had a column with positive and negative numbers, b would like to sum the positive and negative numbers separately? T can be done by using the SUMIF function.
Example: SUMIF SUMIF(range,criteria,sum SUMIF(range,criteria,sum_range) _range)
Range: Is the range of cells that you want evaluated by criteria. Cells in each ran must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
Criteria: Is the criteria in the form of a number, expression, or text that denes wh cells will be added. For example, criteria can be expressed as 32, “32”, “>32”, or “apples”.
Sum_range : Are the actual actual cells to add add if their correspon corresponding ding cells in range range match match crite If sum_range is omitted, the cells in range are both evaluated by criteria a e Master your semester with Scribd added if they match criteria. a l u m r o F offer for students: Only $4.99/month. Special s n
& The New York Times
Applies To: To: MS Excel 2003, 2007 and 2010
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Untitled
CA11_Sol_1108
Search document
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 10: Formula to Find Duplicate Values Values i Data Range
Are you tired of manually sorting and checking each cell in order to duplicates? Well by using the COUNTIF function you can nd dupli cates in a data range.
Applies To: MS Excel 2003, 2007 and 2010
Here is how you can do it: =COUNTIF($A:$A,$A3)>1
=COUNTIF(column with duplicates, rst cell in column)>1 The CountIF will return a Yes Yes or a No to a question: the question in this instance is whether the number of times the value in cell A3 is counted is greater than 1. If it is, then it’ll return Yes, Yes, otherwise False.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 11: Generating of Random Numbers fo Testin esting g of Formulas F ormulas
The RANDBETWEEN function function can be used to create some random bers for testing of formulas. Implying that there is no need of crack one’s head to generate the random numbers. Applies To: To: MS Excel 2003, 2007 and 2010
The RANDBETWEEN function allows you to generate a random whole inside of a range. The syntax is: =RANDBETWEEN(bottom,top)
The bottom parameter is the lowest number in the range you want to use top is the highest number in the range. 1. Select cell A2 and type =RANDBETWEEN(1,100) and press Enter.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
When you press Enter, a random whole number between 1 and 100 is (in this instance, the number 32 is returned).
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
You’ll note that the moment you release the AutoFill Handle, all cells gen random number, number, including cell A2 – it has changed from 32 to 78. RANDBETWEEN will generate a new number each time you press Ente even when you use the UNDO or REDO functions (as long as you’re no any of the RANDBETWEEN cells).
e Master your semester with Scribd a l Read Free Foron 30this Days Sign up to vote title u m & r The3.New Times Useful Not useful SelectYork cell B2. Type in =A2*100 and press Enter. o F offer for students: Only $4.99/month. Special s n
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 12: Convert Function
The Convert function can be used to ensure that all values are in on standard unit. If you have people entering travel distances onto a spread sheet, but some put in miles values and others kilometre va The Convert function can be used to ensure that all values are in ki metres. Applies To: MS Excel 2003, 2007 and 2010
Firstly, Firstly, an advisement: you will never cover every possible way that som might enter the value “20 km”. It could be entered as “20 kilos”, “20km”, km”, “20 kilometres” and every possible spelling mistake of those. The b option in this instance would be to have the number entered in one colum say, say, Column A, and then using a Data Validation list in Column B to sele either “mi” or “km”, limiting how users enter the information and giving yo something to work with (hoping they don’t type in “twenty” instead of “20
If you do manage to have people entering in information inconsistently e “20 mi” or “20 km”, here’s how you could do it. We’re going to use a table six sample distances:
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
2. Select cell C1. Type Type in =LEFT(A1,LEN(A1)-3) and press Enter. Enter.
The LEN() function returns the length of cell A1. By subtracting 3 from th amount (i.e. the letters and the space), you are left with the total number characters for the value. This will mean you’ll capture all numbers, regar of how big and how many decimal places there may be. 3. Select cell D1. Type in =CONVERT(C1,”mi”,”km”) and press Enter. Enter.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
The CONVERT function changes the value from a miles value to a kilom value – the rst option after the target cell (C1) is the value to convert fro the second is the value to convert to. There are other conversion options that include weight and mass, distances, liquid measures and temperatu Search in Excel Help for the CONVERT function function to see a full list. 4. Select cell E1. Type Type in =IF(B1=”mi”,D1,C1) and press Enter. Enter.
The formula will check to see whether this is a “mi” value and if so, retur kilometre value in D1, otherwise it will pull the original value from C1. 5. Highlight cells B1:E1.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
This will ll all of the formulas down to row 6.
For those who like to conserve space, the above could be accomplished one, nested formula: =IF(RIGHT(A1,2)=”mi”,CONVERT(LEFT(A1,L =IF(RIGHT(A1,2)=”mi”,CO NVERT(LEFT(A1,LEN(A1)-3),”mi”,”km”),LEFT EN(A1)-3),”mi”,”km”),LEFT EN(A1)-3)) e Master your semester withfunction Scribd a l NB: to use the CONVERT in Excel 2003, you will need Read Free For 30this Days Sign up to vote on title to have u m & r The New York Times Useful Not useful Analysis ToolPak T oolPak installed. This Th is can be done by using the M MS S Ofce o F offer for students: Only $4.99/month. Special installation CD, customising s n
Cancel anytime.
the MS Excel installation option and ticking box for the Analysis ToolPak. ToolPak. The function comes standard with Excel 20
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Upload
Sign In
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 13: Calculating the Per Periodic iodic Payment f Loan
If you are looking at taking out a loan from the bank and know how loan is and what the interest rate would be. The PMT function can b used to gure out what the payment would be per month. Applies To: MS Excel 2003, 2007 and 2010
When it comes to nance, personal or business, knowing what you’re pa ing or how much you’re spending is paramount. Knowing how you can s money by adjusting your payments is a very handy thing, and you can PMT function to not only work this out, but also to see what you would h pay before you enter into a loan agreement. The PMT function has the following syntax: =PMT(rate,nper,pv,[fv],[type]) Rate: Nper:
this is the interest rate to be paid per period. this is the number number of periods in in the life of the loan (i.e. the the numb payments to be made). Pv: the present value of the loan amount. [Fv]: the future value (optional). [Type]: shows whether payment is to be made on the rst day or the las of the month.
e Master your semester with Scribd a l Read Free Foron 30this Days Sign up to vote title u InNew this example, we will use the following information to see how much m & r The York Times Useful Not useful o
would need to$4.99/month. pay back F offer Special for students: Only s n
years.
anytime. in monthly instalments on Cancel a $100,000 loan over
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
1. Select cell B9, type in =C5/12 and press Enter.
In order to work out the amount per period, we need to divide the yearly interest rate (12%) by the number of periods in the year. In this case, we making payments on a monthly basis, hence dividing the rate by 12. 2. Select cell C9, type in =C4*12 and press Enter.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
We could directly reference cell C3 when we create our formula, but for consistency in the example layout we will reference it to our demonstrati line. 4. Select cell E9, type =pmt(B9,C9,D9) and press Enter.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
The formula references the interest rate for the period (rate = B9), the
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
5. Select cell F9, type in =E9*C9 and press Enter.
You can now see how much will be repaid over the life of the loan. Howe you can start playing with the numbers to see what effect it will have on overall amount. 6. Select cell C4 (the loan years amount), type in 5 and press Enter. Enter.
e Master your semester with Scribd a l Read Free Foron 30this Days Sign up to vote title u m & r The New York Times Useful Not useful number of total periods Changing the number of years to 5 changes the o F offer for students: Only $4.99/month. Special s n
Cancel anytime.
84 to 60 (seen in cell C9). This means an increase in the monthly payme
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 14: Using the PMT Function to Reach a Target
Are you planning on going on holiday and would like to save, say $10,000 to cover all expenses. By utilising the PMT function you ca actually gure out how much you need to pay into your savings eac month to reach the target. Applies To: To: MS Excel 2003, 2007 and 2010
When it comes to nance, personal or business, knowing what you’re paying or how much you’re spending is paramount. Knowing how you ca save money by adjusting your payments is a very handy thing, and we c use the PMT function to not only work this out, but see what we would h pay before we enter into a loan agreement. The PMT function has the following syntax: =PMT(rate,nper,pv,[fv],[type]) Rate: Nper:
this is the interest rate to be paid per period. this is the number number of periods in the life of the loan loan (i.e. the numb payments to be made). Pv: the present value of the loan amount. [Fv]: the future value (optional). [Type]: shows whether payment payment is to be made on the rst rst day or the las e Master yourofsemester a the month. with Scribd l u m r o F offer for students: Only $4.99/month. Special s n
& The New York Times
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime. In this example, we’re going planning to go on an overseas holiday in years time. In order to do this, we’ll need to save $10,000 to cover costs
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
1. Select cell B10, type in =C5/12 and press Enter.
In order to work out the amount per period, we need to divide the yearl interest rate (12%) by the number of periods in the year. year. In this case, making deposits on a monthly basis, hence dividing the rate by 12. 2. Select cell C10, type in =C4*12 and press Enter.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Upload
Sign In
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
There isn’t a present value amount – we have no savings to start with. H PV = 0. 4. Select cell E10, type =pmt(B9,C9,D9,C3) and press Enter. Enter.
The formula references the interest rate for the period (rate = B10), the number of periods (nper = C9) and the present value of the loan (pv e Master your semester with Scribd a we l Since we’re trying to determine payments against a future amount, Read Free Foron 30this Days Sign up to vote title u m now useYork the [fv] option – choose the future value we’d like to work to (i & r The New Times Useful Not useful o Cancel anytime. F offer case, cell Only C3). Special for students: $4.99/month. s n
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
The amount is represented as a negative gure because this is money t you will be paying out. If you would like to see it as a positive number, number, si multiply the result by -1. However, what if we actually began our savings with $2,500 in the bank?
5. Select cell C6 (the currently saved eld), type in -2500 and press Ente
The currently saved amount needs to be entered as a negative: this mon being paid out by you into your savings fund, so it’s a negative amount. 6. Select cell D9, type in =C6 and press Enter. e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 15: The SUMIF Function
The SUMIF function is very useful, allowing you to total a number lines together based on one common trait. This could be a produc name, a region or category, category, or a particular person’s name. Applies To: To: MS Excel 2003, 2007 and 2010
The syntax of the SUMIF function is as follows:
=SUMIF(range,criteria,[sum_range]) In the syntax, the range is the column that you’d like to search through fo given value (the criteria), and when it nds it, add the appropriate value the [sum_range] column. This is based on the row that the criteria is in term, chocolate biscuits” is in column A, rows 6, 9 and 12, with the produ sale values listed in column C, then the SUMIF formula: =SUMIF(A:A,”chocolate biscuits”,C:C) would add cells C6, C9 and C12 together. together.
In this example, we’re going to use the following table of information. Co H has a unique list of all sales people in column A, and we will use the S function to add the total product sales together for each sales person. e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
This will search through column A for all references of the content of cell (in this instance “Anderson P”), and then add the appropriate value in th in column F for that row.
2. Double click on the Auto-Fill handle in the bottom left hand corner of c
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Upload
Sign In
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 16: The SumIF Function
Assuming you have a sheet of sales information and would like to a together all lines for not only a specic product category, category, but for al sales of that product category on a particular region. You You can actua use the SUMIFS function to accomplish that. Applies To: To: MS Excel 2007 and 2010
The SUMIF function allows you to total a number of lines together based o one common trait, such as a product name, a region or category, category, or a part person’s name. However, However, the SUMIFS function looks at the situation wher may want to add values together based on two or more criteria, such as p name as well as a particular region. The syntax of the SUMIFS function is as follows: =SUMIFS(sum_range,criteria_range1,criteria1,…)
In the syntax, the sum_range is the column that contains the values you like to add together. Criteria_range1 is the range that you would like to in for your rst criteria value (criteria1), and this can be repeated using same syntax (i.e. criteria_range2, criteria2, criteria_rangeX,criteriaX, …)
e Master semester withdifferent Scribd a Youyour can include up to 127 range/criteria pairings. In this examp l Read Free Foron 30this Days Sign up to vote title u we’re going to Times use the following table of information. Column A contains m & r The New York Useful Not useful o
Cancel anytime. C containing product categories. We will use SUMIFS function to add the total product sales together (column G) for a
offorthe regions, with column F offer Special students: Only $4.99/month. s n
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
1. Select cell K2, type in the formula =SUMIFS(G:G,A:A,I2,C:C,J2) and Enter.
This will search through column A for all “North Coast” references. It will check whether the Category Name cell in that row (column C) contains t “Beverages” reference. If the row contains “North Coast” and “Beverage then the Product Sales eld for that row will be added to the total. You’ll note in the SUMIFS formula tip in the graphic that from criteria_ran onwards, the parameters are in square brackets (e.g. [criteria_range2, c ria2]) - this means that the extra criteria past the rst is optional. The SU function can be used then in place of SUMIF, SUMIF, which requires only one cr as everything from the second criteria onwards is not required. NB: The SUMIFS function is not available in MS Excel 2003.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 17: Making Forecasts with the Forecas Function
If you are recording monthly sales amounts and there seems to be constant upward trend, though the increase each month isn’t the sa amount. You You can use the FORECAST function to know when a certa sales amount has been hit. Applies To: MS Excel 2007 and 2010
The FORECAST function can work whenever you have a set of data pai an x-value range (say, (say, date) and a y-value range (say, value of sales for date). The function uses a trend line on the y-value target amount (e.g. a amount target), then applies the same trend to the x-value range to work the date that target will be reached. The values in the x-value range mus numbers, otherwise the function will return a #VALUE! #VALUE! Error. In this example, we’ll be using the following data table which lists sales from the end of the month, and the value of those sales for that month. W use the FORECAST function to determine on which date sales will exce $2000.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
3. The formula returns a value of 40716.54041.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
5. The number will now be converted to a date and we can see that sale estimated to exceed $2000 on 22nd June, 2011. 2011.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 18: IF Function
Ever tried to create a formula with a condition but could not. Perha you intend to have different answers depending on the status (true false) of the condition. Then the answer to your problem is the IF fu tion.
The IF function is one of Excel’s most useful and most used functions. W it does, basically, basically, is test to see whether a certain condition is true or fals the condition is true, the function will do one thing, if the condition is fals function will do something else. Syntax: =IF(logic test, value if true, value if false) Applies To: To: MS Excel 2003 ,2007 and 2010
1. To To calculate the commission gures for the following employees. Bas the following criteria. If the above target gure is above the target benchmark of $10,000 then multiply the value by 20% if the above tar gure is below $10,000 then multiply the gure by 5%.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
5. The logic test is always a comparison comparison between two values. Comparis operators are used, for example, to see if the rst value is greater tha less than the second, or equal to it.
6. While the logic test section is limited to answering answering a true or false que you have greater exibility in what you place in the last two argument
7. The IF function can perform different calculations depending on wheth function returns a true value or not.
Note: There is no comma separator used for the number in 10,000 in the above example. This is because the IF function uses the comma to sepa the three sections of the IF function contained within the round brackets. e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 19: Evaluating Multiple Conditions in a Formula with the Nested IF Statem
Are you planning on evaluating multiple conditions in a formula? in the example below where a specic trade discount percentage be used depending on the quantity purchased. If that is the case t you can use the Nested IF statement.
The Nested IF statement enables one to evaluate multiple conditions in formula. As a result more elaborate tests of data can be constructed. Applies To: To: MS Excel 2003 ,2007 and 2010
The IF statement can be used to conduct conditional tests on values and formulas. However if more elaborate testing of data will be carried out th Nested IF Statement can be used.
1. To To calculate the trade discount percentage for clients based on the tab below; TOTAL TOTAL PURCHASES PURCHA SES DISCOUNT PERCENTAGE >=$30,000 7% >=$20,000 5% >=$10,000 3% e Master your semester a <$10,000 0% with Scribd l u m r o F offer for students: Only $4.99/month. Special s n
& The New York Times
2. Enter the data given below.
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
3. Select cell D2 and type the following formula; =IF(C2>=30000,”7%”,IF 20000,”5%”,IF(C2>=10000,”3%”,”0%”))). 4. Press enter and copy the formula down. 5. The result will be as below.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 20: Using the AND function to perform numerous logical tests
Expanding the usefulness of other functions that perform logical t can be achieved with the AND function. One can actually test man ferent conditions by nesting the And function with the IF statemen Applies To: MS Excel 2003 ,2007 and 2010
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if on more arguments evaluate to FALSE. One common use for the AND func to expand the usefulness of other functions that perform logical tests.
1. To To qualify for a bonus an employee must exceed $1000 in sales and h or more years in service to the company. company. Furthermore they should als belong to the Sales Department.
2. If those initial conditions are met the deserving employees will get $10 each year served plus 5% of their total sales amounts.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Untitled
CA11_Sol_1108
Search document
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 21: Trapping Error Messages By Using The IF Error Function
If you’ve worked with formulas, you’ve probably encountered the dre formula error. error. Rather than return a numeric result, the formula cell di plays a weird message such as #VALUE! #VALUE! or #DIV/0!.
Most of the time, this means you need to track down the source of the erro and x it. But sometimes a formula error simply means that the data used the formula is not yet available. For example, say you run a small telemark company. You might have a spreadsheet set up to track your daily sales a percentage of calls made. Applies To: MS Excel 2003 ,2007 and 2010
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
1. The formulas in column D do the calculations that come up with the percentages. For example, cell D4 contains the formula =C4/B4.The a was then converted to percentages by using the percent style option.
2. The formula does its job well--as long as there is data to calculate. An empty cell (such as B9) is treated as a zero, and division by zero is no allowed. As a result, Excel displays an ugly #DIV/0! error message, w makes your entire worksheet look like it was created by a novice. 3. You You can avoid displaying formula errors by re-writing your formula to IFError function. For example: =IFERROR((C4/B4),””). 4. Displays a blank if the division operation results in an error (cell B4 is or contains 0), yet still displays valid results.
5. If you prefer, you can replace the empty string (“”) with other text of yo choice--just make sure the text is enclosed in quote marks.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 22: Aggregate Function
Are you planning on nding the sum for a range of numbers with er values within the range? You can actually use the Aggregate Func to overcome the limitation posed by some functions like Sum, MAX AVERAGE and Percentile functions.
The Aggregate Function addresses the limitation of conditional formattin Data bars, Icon Sets and Color Scales cannot display conditional forma there are errors in the range. This is because the MIN, MAX and PERCENTILE functions do not calculate when there is an error in the calculation range. The LARGE, SMALL, and STDEVP functions also aff the appropriate functionality of certain conditional formatting rules for th same reasons. By using the AGGREGATE AGGREGATE Function, you can implemen functions because the errors will be ignored. In addition, the AGGREGA Function can apply different aggregate functions to a list or database wi option to ignore hidden rows and error values. Syntax: AGGREGATE(function_num, AGGREGATE(function_num, options, ref1, [ref2], …) Applies To: Excel 2010
1. Refer to the data given below. below. e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
3. An error message will be displayed in the Total. Total. Delete the error mess cell B11. 4. To To overcome this limitation, we shall use the Aggregate function. 5. Select cell B11.
6. Select Formulas, Insert Function and search for the Aggregate functio 7. Select OK. 8. Select as below.
9. Enter as below.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
10. Select OK. 11. The answer will be as below.
The following numbers represent some function numbers than can be the formula above: 1=Average,2=Count,3=CountA,4=Max,5=Min,9=Sum For Option numbers refer to the table below. below. OPTION
BEHAVIOUR
0 or omitted
Ignore nested SUBTOTAL SUBTOTAL and AGGREGATE functions functio ns
e Master your semester with Scribd a l 1 I g n o r e h i d d e n rows, nested SRead UBup TFree O A Lon30 athis nDays dtitle AGGREG For Sign toT vote u m functions & r The New York Times Useful Not useful o F offer for students: Only $4.99/month. Special s n
2
Cancel anytime.
Ignore error values, nested SUBTOTAL and AGGREG
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 23: TRIM Function
The Trim Trim Function can be used to clean up data. Let’s say you have imported data into Excel from another application with leading or tr spaces.
The Trim Trim Function Removes all spaces space s from text except for single spac between words. Use TRIM on o n text that you have received from anothe application that may have irregular spacing. Applies To: MS Excel 2003, 2007, 2010
1. For this example the screen shot given below will be used.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
3. The result will be as follows.
As you can see from the data data above, all spaces from the text except for spaces between words have been removed. e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 24: Vlookup Approximate Value
Ever used the Vlookup with the approximate match? In this example demonstrate how, how, by using the Vlookup with the approximate match can extract the correct trade discount percentage based on the quan purchased.
If TRUE or 1 is specied in the Range_lookup argument text box, an exa approximate match is returned. If an exact match is not found, the next value that is less than lookup_value is returned. (The Range_lookup arg text box can also be left blank).
The values in the rst column of table_array must be placed in ascendin order; otherwise, VLOOKUP may not give the correct value.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Applies To: MS Excel 2003,2007,2010
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
4. Enter as below.
5. The original Table_array Table_array is G2:H8. Dollar signs have been added to th range to make it absolute-hence we have $G$2:$H$8. 6. Select OK and copy the formula down. 7. To calculate the Total Sales: a. Select cell E3 and enter the formula: =(A3*C3)-(D3*(A3*C3)). b. Copy the formula down. 8. The nal result will be as below. below. e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Tip 25: Distinct Count
There is no function that calculates the distinct count for a range values in MS Excel. However by using a combination of functions the Distinct Count can be calculated. In the example below by using combination of the SUM, IF and, FREQUENCY functions we are goi calculate the total number of clients who have placed orders for va products.
FUNCTION DETAILS
The FREQUENCY function calculates how often values occur within a ra of values, and then returns a vertical array of numbers. Because this fun returns an array, array, it must be entered as an array formula. The IF function returns one value if a condition you specify evaluates to and another value if that condition evaluates to FALSE. The SUM function adds all the numbers that you specify as arguments.
e Master your semester with Scribd a l u m & r The New York Times o F offer for students: Only $4.99/month. Special s n
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join
Search
Home
Saved
0
3 views
Sign In
Upload
Join
RELATED TITLES
0
Excel 13 and 10 Tips and Tricks Functions Formula eBook
Uploaded by Ganesh Prasad
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Excel Tips 340ch4
Save
Embed
Share
Print
Download
1
of 58
Untitled
CA11_Sol_1108
Search document
Contact Information Excel on Steroids http://www.alchemex.com/Training/Courses BI Community http://community.alchemex.com/ BI Blog www.alchemex.com/blog Subscribe to Excel on Steroids Tips & Tricks e-newsletter www.alchemex.com/blog/subscribe /#/exceltip www.alchemex.com/blog/subscribe /#/exceltipss Subscibe to Sage Intelligence Tips & Tricks e-newsletter www.alchemex.com /blog/subscr /blog/subscribe/#/sitips ibe/#/sitips Sage Alchemex www.alchemex.com
Connect with us LinkedIn Group za.linkedin.com/comp za.linkedin. com/company/sage-alchemex any/sage-alchemex
Master your semester with Scribd TwitterYork Times & The New twitter.com/ excelonsteroids excelonsteroids Special offer for students: Only $4.99/month.
Read Free Foron 30this Days Sign up to vote title
Useful
Not useful
Cancel anytime.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Master your semester with Scribd & The New York Times Special offer for students: Only $4.99/month.
Upload
Sign In
Read Free For 30 Days Cancel anytime.
Join