Download Excel Problem Solving: Formulas, Functions, and Data Manipulation and more Exams Biology in PDF only on Docsity!
Problem Solving in
Excel with verified
solutions
What does Formula -> Lookup & Reference do? - answer Shows all formulas If you type = and then click "Fx" in text box, what happens? - answer It lets you know step by step to inserting a formula What does Formulas -> Trace Precedence do? - answer Lets you look at which cells are referenced in a specific formula What does Formulas -> Trace dependence do? - answer Lets you look at which formula is referenced in another formula How do you remove arrows in an error help? - answer Formulas -> Remove Arrows
How to paste formats - answer Copy cells. Paste -> formats Does paste formats do the same thing as format painter? - answer Yes How to paste solely formulas? - answer Paste -> paste special -> formula How to paste solely values and not formulas - answer paste -> paste special -> values What is paste -> multiply? - answer If you want multiple cells to be multiplied by a single cell, you can use this How to paste multiply? - answer Paste -> Paste Special -> multiply What is paste transpose? - answer Switches rows to columns, vice versa (you have to highlight what you want to be switched) How to paste transpose - answer Paste -> Paste special -> Transpose
What happens when you first type in =subtotal? - answer It will ask what you want to subtotal: sum, average, count, etc. and then the range. What does IFERROR do? - answer Tells excel what value to say if an eror happens IFERROR function - answer =IFERROR (value, value if error). Ex: =IFERROR(VLOOKUP(B3,B4:I5 [table array],2,FALSE),"Data is not available"[what you want the cell to say if the data is not available]) Is an IFERROR similar to IF? - answer Yes, the first part says what value to put if an error does not occur. The 2nd part says the value to put if an error does occur. What do you do if you have data is seperated by a comma or an underscore? - answer You select the data and click data -> text to columns. This will allow you to delimit the file What type of delimited file is underscore? - answer Other, and type in _
When do you use fixed width delimited? - answer Generally, it's used in scanned data Why are delimited files important? - answer You dont want the data separated by underscores or commas otherwise it's hard to read and can be misconstrued Substitute function - answer =SUBSTITUTE([text: cell reference, old_text: "2013", new_text: "2015"]) Can substitute be used to change any number or text? - answer Yes Do you need quotes around numeric data in the substitute function? - answer Yes Should I keep raw data separate from edited data?
- answer Yes, always How to keep raw data separate from edited data - answer copy and paste. Make sure to label which is raw data and which is edited How to remove duplicates in data? - answer Data -
Remove duplicates -> ok
TRIM example - answer =TRIM([reference cell which you need to trim]). EX: =TRIM(H2) Why is TRIM necessary? - answer If you have extra space, it can bring back an error when youre trying to find something Can TRIM be used in other functions? If so, what other functions? - answer Yes; CLEAN, VALUE What does the SUBSTITUTE function do? - answer Allows you to take specific characters, words, and numbers, and change them with another Example of SUBSTITUTE with TRIM function - answer =TRIM(SUBSTITUTE([text: cell reference, old_text: ",", new_text: "."])). =TRIM(SUBSTITUTE(H2,",",".")) Optional SUBSTITUTE - answer =SUBSTITUTE([text: cell reference, old_text: ",", new_text: ".", instance_num: specifies with instance of old text you want to replace with new text]) When do you use the SUBSTITUTE function? - answer When you want to replace specific text in a text string
Do you use quotes around numeric data in a SUBSTITUTE function? - answer Yes Why do numbers have to be stored as numbers? - answer In order to od formulas such as sum, average, VLOOKUP, HLOOKUP What do little green arrows in top left of cell show?
- answer An error What happens if a number isnt truly stored as a number and you try to do a VLOOKUP? - answer The tables wont understand the number and itll mess up the data VALUE example - answer VALUE(text: reference cell). or =VALUE(H2) 2 ways to store numbers as numbers - answer 1. =VALUE. 2. Select data. Data -> Text to columns -> Delimited -> uncheck all delimiters -> general -> finish Can numbers not stored as numbers be in anything including dates? - answer Yes
What does manipulative data do? - answer Gives us a better analysis of underlying data What do text functions allow you to do? - answer modify, manipulate, and search text data What does the =RIGHT function allow you to do? - answer - Extract a substring
- Starting at the end of the string
- Selecting the defined number of characters What is the =RIGHT function? - answer It shows you the right most characters of a string, you just input how many characters from the right you want it to show. Do the =RIGHT and =LEFT function include spaces as characters? - answer Yes Example of =RIGHT function - answer =RIGHT(Text: cell reference, num chars: how many characters from the right end you want. It will count back that many). Ex: H4 contains HANK-101007. You say =RIGHT(H4,6). This will show you 101007
Can =RIGHT and =LEFT combine text and numbers?
- answer Yes What is the =LEFT function? - answer It shows you the left most characters of a string, you just input how many characters from the left you want it to show. =LEFT example - answer =LEFT(Text: cell reference, num chars: how many characters from the left end you want. It will count forward that many). Ex: H4 contains HANK-101007. You say =LEFT(H4,4). This will show you HANK What is =LEN? - answer Finds the length of what's in a cell Example of =LEN - answer =LEN(text: cell reference). Example =LEN(H4) What is =FIND? - answer Finds where a specific character is in a cell Example of =FIND - answer =FIND(text: cell reference, char: character). Ex: =FIND(H4, "_")
- This is the same as above but you dont find FIND first. =LEFT(RIGHT(H4,LEN(H4)-FIND(H4,"-")),3). What does data validation do? - answer - Limit potential inputs during data entry
- Ensures all data entered within the cell must meet a criteria otherwise an error message will appear What is the purpose of data validation? - answer It helps to stop errors in data and limits data cleansing prior to analysis How to get to data validation? - answer Data -> Data Validation -> the pop up box lets us choose what we can limit How to work data validation - answer - Highlight where the data will go into
- Data -> Data validation -> data validation -> settings: choose input and range. If you have a minimum/maximum number in data validation in a cell, what do you do? - answer just click on the cell
If you want to type in a month number (1-12), what would the minimum and maximum be in data validation? - answer Min: 1. Max: 12 How to type in a list in data validation - answer You can just type in and use commas to distinguish between numbers in a list. Ex: you can put it as 1, 2, 3. Excel will read this as 1, 2, 3 are 3 separate items in a list When typing in an error message, do you need a title too? - answer Yes What do you do if you have any cents for data validation (for example, either 0.50 or 100.50)? - answer Put decimal in allow When using RIGHT and LEFT, shoudl you clean the data (TRIM, CLEAN, etc.) first? - answer Yes What does FIND find first? - answer the first instance of text in the searched text string What does it say when you do a FIND and it comes back with #VALUE! - answer The text is not found
Example of LOWER - answer =LOWER(Text: cell reference). Ex: =LOWER(H3) What does CONCATENATE do? - answer Link things together in a chain or series to create a new and larger text string
- Takes multiple text strings and inputs them to return one text string CONCATENATE example - answer =CONCATENATE(Text 1: cell reference of first string,","[or any punctuation to separate the strings: you dont have to do this if no need. Make sure to make the punctuation be in quotes],Text 2: cell reference of second string [you can include however many text strings you need]. Ex: =CONCATENATE(H3,";",I3,";",J3) What does it mean when CONCATENATE brings back #VALUE! - answer This is displayed when text is not found If + OR - answer If any of the tests are true or all are false What are structured references? - answer Special references to specific columns
Example of SUMIFS? - answer =SUMIFS(Sum Range, Criteria Range, Critera). Ex: =SUMIFS(H2,I2,"Hank") or =SUMIFS(H2,I2,">200") Whats the difference between SUMIF and SUMIFS? or COUNTIF and COUNTIFS - answer The IFS mean that more than 1 criteria is there. If you try to do more than 1 criteria in an IF (like COUNTIF or SUMIF), it'll bring back an error Should you verify countifs and how would you do so? - answer Yes; by using filter Can you use COUNTIFS to see numbers that are greater than, less than, equal to, or does not equal to? - answer Yes Sign for does not equal to - answer <> How to do various COUNTIFS? - answer =COUNTIF(Range 1, Criteria 1, Range 2, Criteria 2, etc.). You can use cells as reference if your reference is written in a cell. Otherwise, always use quotation amrks, even in numeric.
If I'm doing an IF-AND, which part should I do first?
- answer AND What part of the IF is the AND in IF-AND? - answer the first part, so the part you're measuring What happens if you do an AND without an IF? - answer It'll show "true" if it's true and "false" if it's false Example of IF-AND - answer =IF(AND(H2>I2,B2>J2),"Yes","no). So if H2>I2 AND B2>J2, then it'll print "yes". If either one is wrong, it'll print "no" Can you use formulas in IF-AND? - answer Yes When/why do you use IF-AND? - answer Comparing data across multiple criteria What happens if there's an error in 1 part of IF- AND? - answer The entire function will show as an error What needs to happen for IF-AND to be true or false? - answer Every component of the AND
NEEDS to be true for it to be true. If any single component is false, it will show as "false" Difference between IF-OR and IF-AND - answer OR: Returns true if any of the logical tests are true and false if all of the logical tests are not true AND: Returns true if all of the logical tests are true and false if any of the logical tests are not true OR or AND?: Returns true if any of the logical tests are true and false if all of the logical tests are not true - answer OR OR or AND? Returns true if all of the logical tests are true and false if any of the logical tests are not true - answer AND Example of IF-OR - answer =IF(OR(logical test 1, logical test 2), Value if true, Value if false. EX: =IF(OR(H2>I2,B2<=H2),"profit"," " When/why do you use IF-OR? - answer Evaluating data that are on opposite ends of a range Can you use formulas in IF-OR functions? - answer Yes
