Intro to Excel

Question 1

Create an invalid entry so only positive integers are accepted, preventing incorrect data entries

Answer 1

data - data validation -
allow: whole number
data: greater than
Min: 0

Question 2

refine data format from 2014-09-18 to => 18- Sep-2014

Answer 2

home-num format-custom-
Type: dd-mmm-yyyy

Question 3

make significant data stand out at a glance: highlight top 100 priced houses

Answer 3

price column-home-cond formatting-top/bottom rules-top10 (switch to 100)

Question 4

Name the range “item_price” by using the Define Name feature in Excel.

Answer 4

ItemPrice-Ctrl+shft+down-formulas-define name

Question 5

get avg Order Price across entire doc

Answer 5

create column: oerderprice- formula beneath=price*quantity for whole column, check avg at bottom

Question 6

understand whether there is a significant difference between each country’s order to the delivery period

Answer 6

data-subtotal-
At each change in: CustomerCountry
Use function: Average
Add subtotal to: OrderToDelivery
Include Summary below data

Question 7

find profit margin ratio

Answer 7

total profit/total sales

Question 8

text functions

Answer 8

upper(), lower(), left(), right(), DATE(year,month,day)

Question 9

maths and trig

Answer 9

round(num, num_digits), abs(): returns abs num w/o its sign, sum()

Question 10

statistical

Answer 10

average(), count(), min(), max()

Question 11

What is the average order item cost for orders in September?

Answer 11

home-sort&filter