CIS300 Test 3 Review Reinhardt. © 2009 Dale McIntosh. All Rights Reserved. This Review Will Cover...

CIS300 Test 3 ReviewReinhardt

© 2009 Dale McIntosh. All Rights Reserved.

This Review Will Cover

• Brief overview of EXCEL functions,

followed by EXCEL Practice Questions• Wiley Textbook Study Guide

(Handout)• More Practice Questions (Handout)• Slides are Available at:

http://reach.louisville.edu/tutoring/computer/cistestreviews.html


Disclaimer***

• This review session consists of A LOT of EXCEL Functions

• I will move fast, but please raise your hand or ask me to repeat any concept you would like me to repeat.


The DAVERAGE function

Microsoft® Excel® Database Functions

Syntax:

=DAVERAGE(database,field,criteria)

Arguments:

•database Required The range of cells that makes up the list or database.

•field Required Indicates which column is used in the function.

•criteria Required The range of cells that contains the conditions you specify.




=DAVERAGE(A4:E10,"Yield",A1:B2)

The average yield of apple trees over 10 feet in height.




=DAVERAGE(A4:E10,"Yield",A1:B2)=12

The average yield of apple trees over 10 feet in height.




=DAVERAGE(A4:E10,3,A4:E10)

The average age of all trees in the database.




=DAVERAGE(A4:E10, 3,A4:E10)





=DAVERAGE(A4:E10, 3,A4:E10)=13



The DMAX function


=DMAX(A4:E10,"Profit",A1:A3)

The maximum profit of apple and pear trees.


The DMAX function


=DMAX(A4:E10,"Profit",A1:A3)=105

The maximum profit of apple and pear trees.


The DMIN function


=DMIN(A4:E10,"Profit",A1:B2)

The minimum profit of apple trees over 10 in height.


The DMIN function


=DMIN(A4:E10,"Profit",A1:B2)=75



The DCOUNT function


=DCOUNT(A4:E10,"Age",A1:F2)

This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers.


The DCOUNT function


=DCOUNT(A4:E10,"Age",A1:F2)=1

This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers.


The DSUM function


=DSUM(A4:E10,"Profit",A1:A2)

The total profit from apple trees.


The DSUM function





The DMIN function





The DMIN function


=DSUM(A4:E10,"Profit",A1:A2)=225



Microsoft Excel Date Functions

DATE=DATE(year,month,day)

YEAR=YEAR(serial_number)

MONTH=MONTH(serial_number)

DAY=DAY(serial_number)

TODAY=TODAY()

NOW=NOW()

YEARFRAC=YEARFRAC(start_date,end_date,[basis])

DATEDIF=DATEDIF(startdate,enddate,interval)


The DATE function

Microsoft® Excel® Date Functions

Syntax:

=DATE(year,month,day)

Arguments:

•year Required The value of the year argument can include one to four digits.

•month Required A positive or negative integer representing the month of the year from 1

to 12 (January to December).

•day Required A positive or negative integer representing the day of the month from 1

to 31.


The DATE function

Description:

• Returns the sequential serial number that represents a particular date.

Remarks:

• If the cell format was General before the function was entered, the result is formatted as a date instead of a number.

• Excel interprets the year argument according to the date system your computer is using.

• By default, Microsoft Excel for Windows uses the 1900 date system; Microsoft Excel for the Macintosh uses the 1904 date system.

• Excel stores dates as sequential serial numbers so that they can be used in calculations.

Errors:

NoneMicrosoft® Excel® Date Functions


The DATE function

=DATE(A2,B2,C2)


What is the serial date for the date in A2:C2, using the 1900 date system?


The DATE function

=DATE(A2,B2,C2)=39448


What is the serial date for the date in A2:C2, using the 1900 date system?


The YEAR function


Syntax:

=YEAR(serial_number)

Arguments:

•serial_number Required The date of the year you want to find.


The YEAR function

Description:

• Returns the year corresponding to a date.

Remarks:

• The year is returned as an integer in the range 1900-9999.• Dates should be entered by using the DATE function, or as results of other formulas or functions.

• For example, use DATE(2008,5,23) for the 23rd day of May, 2008. • Problems can occur if dates are entered as text.

Errors:

None



The YEAR function

=YEAR(A2)



The YEAR function

=YEAR(A2)=2008



The YEAR function

=YEAR(A4)



The YEAR function

=YEAR(A4)=2010



The MONTH function


Syntax:

=MONTH(serial_number)

Arguments:

•serial_number Required The date of the month you want to find.


The MONTH function

Description:

• Returns the month of a date represented by a serial number.

Remarks:

• The month is given as an integer, ranging from 1 (January) to 12 (December).

• Dates should be entered by using the DATE function, or as results of other formulas or functions.

• For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Errors:

None



The MONTH function

=MONTH(A2)



The MONTH function

=MONTH(A2)=4



The DAY function


Syntax:

=DAY(serial_value)

Arguments:

•serial_value Required The date of the day you want to find.


The DAY function

Description:

• Returns the day of a date, represented by a serial number.

Remarks:

• The day is given as an integer ranging from 1 to 31.• Dates should be entered by using the DATE function, or as results of other formulas or functions.

• For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Errors:

None



The DAY function

=DAY(A2)



The DAY function

=DAY(A2)=15



The TODAY function


Syntax:

=TODAY()

Arguments:

None


The TODAY function

Description:

• Returns the serial number of the current date.

Remarks:

• If the cell format was General before the function was entered, Excel changes the cell format to Date.

• If you want to view the serial number, you must change the cell format to General or Number.

• The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook.

• The TODAY function is dependent on your computer’s system clock being correct.

Errors:

NoneMicrosoft® Excel® Date Functions


The TODAY function

=TODAY()



The NOW function


Syntax:

=NOW()

Arguments:

None


The NOW function

Description:

• Returns the serial number of the current date and time.

Remarks:• If the cell format was General before the function was entered, Excel changes the

cell format to the same date and time format that is specified in the regional date and time settings in Control Panel.

• The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.

• Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date.

• The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.

Errors:None



The NOW function

=NOW()



The NOW function


=NOW()


The YEARFRAC function


Syntax:

=YEARFRAC(start_date,end_date,[basis])

Arguments:

•start_date Required A date that represents the start date.

•end_date Required A date that represents the end date.

•basis Optional The type of day count basis to use.



Description:

• Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date).

Remarks:

• Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.

• Dates should be entered by using the DATE function, or as results of other formulas or functions.

• All arguments are truncated to integers.

Errors:

#VALUE – If start_date or end_date are not valid dates#NUM! – If basis < 0#NUM! – If basis > 4




=YEARFRAC(A2,A3,A4)


What is the fraction of the year between the two dates?



=YEARFRAC(A2,A3,A4)=0.583333333


What is the fraction of the year between the two dates?


The DATEDIF function


Syntax:

=DATEDIF(startdate,enddate,interval)

Arguments:

•startdate Required A date that represents the start date.

•enddate Required A date that represents the end date.

•interval Required The type of day count basis to use.




Syntax:

=DATEDIF(startdate,enddate,interval)

Arguments:

•interval Required The type of day count basis to use.



Description:

• Computes the difference between two dates in a variety of different intervals.

Remarks:

• If you have the interval in another cell referenced by the formula, that cell should not have quotes around the interval string.

• When calculating date intervals, DATEDIF uses the year of startdate, not enddate when calculating the yd, ym and md intervals

Errors:

#VALUE – If start_date or end_date are not valid dates#NUM! – If start date is not less than or equal to the end date




=DATEDIF(A2,A3,”d”)


What is the difference in days between the two dates?



=DATEDIF(A2,A3,”d”)=210


What is the difference in days between the two dates?


The TIME function

Microsoft® Excel® Time Functions

Syntax:

=TIME(hour,minute,second)

Arguments:

•hour Required A number from 0 (zero) to 32767 representing the hour.

•minute Required A number from 0 to 32767 representing the minute.

•second Required A number from 0 to 32767 representing the second.


The TIME function

Description:

• Returns the decimal number for a particular time.

Remarks:

• If the cell format was General before the function was entered, the result is formatted as a date.

• Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different date systems as their default.

• Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Errors:

None



The TIME function

=TIME(A2,B2,C2)



The TIME function

=TIME(A2,B2,C2)=0.5



The HOUR function


Syntax:

=HOUR(serial_number)

Arguments:

•serial_number Required The time that contains the hour you want to find.


The HOUR function

Description:• Returns the hour of a time value.

Remarks:• The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).• Times may be entered as text strings within quotation marks.

(for example, "6:45 PM")• Times may be entered as decimal numbers.

(for example, 0.78125, which represents 6:45 PM)• Times may be entered as results of other formulas or functions.

(for example, TIMEVALUE("6:45 PM"))• Microsoft Excel for Windows and Excel for the Macintosh use different date

systems as their defaults.• Time values are a portion of a date value and represented by a decimal number.

(for example, 12:00 PM is represented as 0.5 because it is half of a day)

Errors:

None Microsoft® Excel® Time Functions


The HOUR function

=HOUR(A2)



The HOUR function

=HOUR(A2)=3



The HOUR function

=HOUR(A3)



The HOUR function

=HOUR(A3)=15



The HOUR function

=HOUR(A4)



The HOUR function

=HOUR(A4)=15



The MINUTE function


Syntax:

=MINUTE(serial_number)

Arguments:

•serial_number Required The time that contains the minute you want to find.


The MINUTE function

Description:• Returns the minutes of a time value.

Remarks:• The minute is given as an integer, ranging from 0 to 59.• Times may be entered as text strings within quotation marks.






Errors:



The MINUTE function

=MINUTE(A2)



The MINUTE function

=MINUTE(A2)=48



The SECOND function


Syntax:

=SECOND(serial_number)

Arguments:

•serial_number Required The time that contains the seconds you want to find.


The SECOND function

Description:• Returns the seconds of a time value.

Remarks:• The second is given as an integer, ranging from 0 to 59.• Times may be entered as text strings within quotation marks.






Errors:



The SECOND function

=SECOND(A2)



The SECOND function

=SECOND(A2)=18



The SECOND function

=SECOND(A3)



The SECOND function

=SECOND(A3)=0



The SUMIF function

93


The AVERAGEIF function

94

=AVERAGEIF(B2:B5,"<23000")



95

=AVERAGEIF(B2:B5,"<23000")=14000



96

=AVERAGEIF(A2:A5,"<95000")



97

=AVERAGEIF(A2:A5,"<95000")=#DIV/0



98

=AVERAGEIF(A2:A5,">250000",B2:B5)



99

=AVERAGEIF(A2:A5,">250000",B2:B5)=24500


The AVERAGEIFs function

100


The COUNTIF function

101


The COUNTIFs function

102


The LARGE function

103

Description:

• Returns the k-th largest value in a data set.

Remarks:• If n is the number of data points in a range, then LARGE(array,1) returns the largest

value.• If n is the number of data points in a range, then LARGE(array,n) returns the

smallest value.

Errors:

#NUM! – If array is empty#NUM! – If k ≤ 0#NUM! – If k is greater than the number of data points

=LARGE(array,k)

3rd largest number in the numbers in columns A and B

104

=LARGE(array,k)=LARGE(A2:B6,


105

=LARGE(array,k)=LARGE(A2:B6,3)


106

=LARGE(array,k)=LARGE(A2:B6,3)


List the numbers in descending order:

7655444332

=5107


The SMALL function

108

Description:

• Returns the k-th smallest value in a data set.

Remarks:• If n is the number of data points in a range, then SMALL(array,1) returns the

smallest value.• If n is the number of data points in a range, then SMALL(array,n) returns the largest

value.

Errors:

#NUM! – If array is empty#NUM! – If k ≤ 0#NUM! – If k is greater than the number of data points

=SMALL(array,k)

4th smallest number in first column

109

=SMALL(array,k)=SMALL(A2:A10


110

=SMALL(array,k)=SMALL(A2:A10,4)


List the numbers in ascending order:

233444567

111

=SMALL(array,k)=SMALL(A2:A10,4)


List the numbers in ascending order:

233444567

=4112


The ROUND function

113

=ROUND(-1.475,2) Rounds -1.475 to two decimal places


Quiz Questions 1-3

• Spend 5 minutes working on questions 1-3 on the handout.

• 1) What is the average menu price for items >$10?

• =AVERAGEIF(E4:E13, ">10")• 2) How many menu items have one side?• =COUNTIF(D4:D13, 1)• 3) What is the average Menu Price of all items

with one side?• =AVERAGEIF(D4:D13, 1, E4:E13)

114


Quiz Questions 4-6

• Spend 5 minutes working on questions 4-6• 4) How many menu items are there with a

menu price > $6 but also is an appetizer?• =COUNTIFS(E4:E13,">6",C4:C13,"Appetizer"

)• 5) What is the difference between the average

Approx. Cost of Entrees and Appetizers?• =AVERAGEIF(C4:C13, "Entrée", F4:F13) -

AVERAGEIF(C4:C13, "Appetizer", F4:F13)

115


Quiz Questions 4-6

• 6) What is the sum of the Profit Margin of menu items that begin with the letter "H"?

• =SUMIF(B4:B13, "H*", G4:G13)

116

CIS300 Test 3 Review Reinhardt. © 2009 Dale McIntosh. All Rights Reserved. This Review Will Cover...

Documents

Transcript of CIS300 Test 3 Review Reinhardt. © 2009 Dale McIntosh. All Rights Reserved. This Review Will Cover...