80.2K

Verified Solution

Link Copied!

Please tell me the correct function formula, which is used to calculate the answer.

Remember, all assignments and quizzes must be completed individually without the help of any other person in any capacity as explained in the syllabus.

Hint for E5 formula on Original worksheet:

Think about the E5 formula on the Original worksheet the following way and just let it fall through with a nested IF. Start the IF with the highest cutoff value condition and put the correct true action for that, then the false action is the nested IF and the condition for it is the next highest cutoff value with the appropriate true action and the false action is just letting the logic fall through to the last possible action.

=IF(condition, true action, (a nested IF as the false action, true action, false action))

If I had something like:

Total Points were 500 or more then "Great"

If Total Points between 400 and 499 then "Fair"

If Total Points were less than 400 then "Poor"

The IF statement would be =IF(Total Points >=500,"Great",IF(Total Points >=400,"Fair","Poor"))

To test the logic mentally: If Total Points were 525, then Excel would do the true action of "Great" and end the IF statement. If the Total Points were 500, then Excel would also do the true action of "Great" and end the IF statement. If the Total Points were 450, then Excel would do the "false action" which is the nested IF and do the "true action" of the nested IF which is "Fair" and end the IF statement. The same would happen if Total Points were 400. If the Total Points were 350, Excel would evaluate the first condition as false and skip the "Great" and go to the false action which is the nested IF and Excel would evaluate that as false and skip the true action. So by default Excel would use "Poor" which is the nested IFs false action.

Another example: If I have 3 options in X5 such as "M" for married, "S" for single, and "H" for head of household and the married percent is 10% and the single percent is 15% and the head of household percent is 20%, my IF might look like the following. Notice how there is only 1 nested IF and the last

1 Hard Work University MBA Graduate Admissions Exercise Simple Order of Operations Review: Inner to outer parentheses, then exponents, then multiplication and division from left to right, then addition and subtraction from left to right. 2018/1/31 21.14 (type your name here) GMAT Score GPA 4 Applicant 5 Brown, Susan 6 iliams, Paul 7 Caldwell, Bll 8 Davis, John 9 Miler, Sam 10 Neville, Julie 11 Rogers, Tom 12 Wlson, Sally 13 James, Daniel 14 Anderson, John 15 Smart, Regina 16 17 Assumptions: 18 Students are admited,considered, or rejected based on their Combined Score 500 550 3.90 3 80 2.50 1.50 4.00 2.80 3. 90 2.00 Data Validation: Normally this would be done before entering any data into the worksheet (such as for GMAT scores and GPAs). However, to keep you from having enter those values, you are just going to practice setting up the data validation after I have already entered the data, then you will try to change some of the data to invalid values so you can see what the data validation feature provides during actual data input. See instructions 410 780 3.70 4.00 20 the Combined Score is 850 or more the student status is Admit 21 f the Combined Score is between 700 and 849 the status is Consider 22 f the Combined Score is less than 700 the status is Reject 25 1. Setup data validation for valid GMAT scores and GPAs. Include Row 16 in the range for each one 2 GMAT scores must be between 400 and 1000 (Normally this would be set up before entering any data for GMAT scores) 27 GPAs must be between 1.0 and 4.0 (Normaly this would be set up before entering any data for GPAS) 28 Try to change the GMAT score in B5 to 1500 and see what happens. Be sure to leave as 500 when finished 29 Try to change the GPA to C5 to 4.9 and see what happens Be sure to leave as 3.90 when finished 0 Add a new person's data on Row 15 Your name (last, first), GMAT 950 and GPA 40 31 2. Write copyable formulas for the following: Cakulate the Combined Score as GMAT score (their GPA 100) Remember the order of operations and use () as needed Determine the Status using a nested IF function. Remember to start with the highest cutoff and you should have only 2 nested t's and the third action of "Reject will fai through by defaut as the second Ifs false action 3. After you finish this part of the exercise,copy the range A1:E15 to the Better Design worksheet and complete the Better Design worksheet using cell 33 A1 as the anchor cell on the Better Design worksheet 35

Answer & Explanation Solved by verified expert

See Answer