User:LukeCochrane

Excel Notes Formulas I need to know for Excel

• =sum()
• =today()
• =int() – keeps the integer (whole number) part and discards the decimal part
• =min() – the smallest/minimum value
• =max() – the largest/maximum value
• =average() – the average value of a data set
• =count() – counts the NUMBERS in a data set
• =counta() – counts the NON-EMPTY cells in a data set (i.e. counts numbers and text)
• =countif() – counts the number of cells that meet a criteria e.g. “a” or “<100” or “>=45”
• =if() – a conditional calculation
• =IF(text, true part, false part) they have 3 parts
• Test – something that equates to true or false, e.g. B2=100 or C6>=50 or B2>B3 or B6=”Y”
• True part and false part – what you want to appear depending on the test being true or false
• Examples: B2 or 65 or D12 * 90 or D12 * B5 or “Yes” or “N” (it can just be a value or it can be a calculation).
• TEXT MUST ALWAYS BE ENCLOSED IN SPEECH MARKS “”, NUMBERS AND DATES MUST NOT!
• =vlookup(ref, table, column)
• Ref – what it is you are looking up
• Table – the table that contains the list of items being looked up. MUST BE SORTED INTO ORDER
• Column – which coumn in the table do you want to display from the table e.g. 2, 3 or 6
• =hlookup() – exactly the same as vlookup() except the data runs horizontally rather than vertically
• =lookup()- an alternative to vlookup and result vectrs. Acts only as an approximate match and the data must be sorted for this to work.
• =sumif( range, criteria, sum range)
• Range- contains the data you want to filter (e.g. male or Female)
• Criteria – what the logic is you want to filter on (e.g. “Mal”, or “>50”, or “<=B2”)
• Sum range- the cells contain the numbers you want to add/sum
• Day()- extracts the day from the date (e.g. 12 from 12/7/2011
• =month()- extracts the month from a date
• =Year()- extracts the year from a date
• =Now() – provides the date and time right now
• =today() – provides the date ONLY right now
• =Hour() – extracts the hour from a time (e.g. 14 from 14:34:56)
• =minute – extracts the minute from a time
• =second() – extracts the second from a time
• =left() – returns the left most character from some text
• =right() – returns the right most character from some text
• =mid() – returns the specified characters from the middle of some text
• =len() – returns the length/number of characters in some text
• =find() – finds the position of a specified character(or text) in a larger piece of text.
• Access
• • Alpha- numeric (or text)
• - Any combination of letters and number
• - E.g. Bob Smith, 49 Apples, 0226401235
• • Numeric
• - Integers (whole numbers) e.g. 12, 56,-12
• - Real (with decimals) e.g. 2.0, 12.45, -45.67
• • Boolean (Yes or No)
• - Two states Yes/No, True/false, On/Off
• • Date/Time
• - E.g. 2/5/98, 12th Feb 2012, 02-Mar-11, 13:45

• Tables. Data is stored in tables. Each item of data is called a record (more details to follow). We separate out data into tables about ‘things’. This is called a relational database. We can add rules to our tables to ensure data entered meets rules. This is called validation. E.g. A date of birth should not be able to be set with a day in the future.
• Queries. If we want to find out the answer to a question about our data wen can filter it. This is done using queries. A query lets us see a subset of our data that meets the rules we set. E.g. show me all the sales made last Saturday or show me all the sales made by a particular salesperson or show me the purchases made by a particular customer.

Queries are what make databases powerful. Most businesses have enormous amounts of data, too much to look at on a screen or paper, but a query lets us see the parts of the data that we are interested in. We can also produce summary queries where we total up, and summarise the data rather than just filter it.

• Reports. While queries are very powerful and provide us with the results we desire they aren’t very presentable. They look like a table of data.

Sometimes we want to print out those results. We want the printout to look neat and to include a heading, date printed, page numbers, column headings, groupings and have our data sorted into an order of our choosing. Databases have a reporting facility that lets you take a query and design it the way you want it to look on paper.

Validation If you need to validate your database don’t forget the 2 parts: 

•  The rule 
•  The error message (lots of students lose marks be forgetting the easy part!) 
• The rule is something that must equate to TRUE to be accepted. Examples: 
• >0  Data must be more than 0 
• <=12  Data must be less than or equal to 12 
• <>100  Data must be any number that is NOT 100 
• >12 and <46  Data has to be a number between, but NOT including, 12 and 46 
• >=1/8/2010 and <=31/8/2010  Data must be a date during August 2010 
• Like “P*”  Data must start with a P 
• Like “T??”  Data must be 3 letters and start with a T 
• Like “*fan*”  Data must contain the phrase “fan” in it somewhere 
• “Apples” or “Oranges”  Data must be either Apples or Oranges 
• **Note: The only wildcards allowed are * and ? (don’t get confused with input masks)** 
• **Note2: You only use speech marks when the data is text. This is wrong “>3” unless you want people to enter the phrase >3 into that field!

Queries

• For simple queries here are a few rules to follow: 
•  Only add the fields that you need, you can add more and delete them later if need be 
•  Every time you make a change run the query to see the results of your change 
•  Use criteria to filter your data 
•  If you need to use criteria and NOT include the column uncheck the ‘show’ box 
•  If you have MULTIPLE criteria on one line that is an AND search – all criteria must be true to 

   be included in the results 

•  If you place criteria on separate lines you can perform and OR search 

Profit: [SalePrice]‐[PurchasePrice]