Prof. Yitz Rosenthal (click here for my schedule)
(All contents of this website are copyright © 2009)best viewed with
Microsoft Internet Explorer 7.0 or later
Microsoft Internet Explorer 7.0 or later
The following are two worksheets in a single Excel workbook:
| Worksheet "A" | Worksheet "B" |
|---|---|
 |  |
Use the Excel sheets above to compute the value of each of the following Excel functions. For each question, assume that the question is placed in cell D1 of Worksheet "A". If the function produces an error, write the word ERROR and explain why there was an error.
=3+4*5
Answer: 23
=(3+4*5)+2*3
Answer: 29
=50%*(75+75)
Answer: 75
Explination:The % in Excel simply divides the value before it by 100. This is the same as =50/100*(75+75)
=3*2+2^3*5
Answer: 46
=3*(4+6)^(4-10/5)-50
Answer: 250
=5+6/3*2
Answer: 9
Explination: Division and multiplication are done in order from left to right. Therefore 6/3 is done before 3*2. Addition is done after both division and multiplication.
=SUM(2,3)*4
Answer: 20
Explination: SUM(2,3) is a function call so it is done before the multiplication by 4. By contrast, =2+3*4, would be 14 since multiplication is done before addition.
=2*SUM(3+4*5,6)
Answer: 58
Explination: =2*SUM(3+4*5,6) ... =2*SUM(3+20,6) ... =2*SUM(23,6) ... =2*29 ... =58
=AA+1
Answer: ERROR - AA is not a valid cell reference
=1B+2
Answer: ERROR - 1B is not a valid cell reference
Explination:
=B!B2+B2
Answer: 93
Explination: B!B2 is the cell B2 from worksheet B, i.e. 90. B2 is on the current worksheet, i.e. worksheet A (see instructions) so the value of B2 is 3. =90+3 ... =93
=3*A1:B2+3
Answer: ERROR - you can't use a range in a formula except as a parameter of a function call.
=SUM(A1:B2,C2:C3)
Answer: 25
=AVERAGE(SUM(A1:A3),3*MAX(A2:C2))+2
Answer: 20
=2*3+MAX(MIN(A1:C1),MAX(A3:C3))*2+3
Answer: 27
=LOWER(LEFT(RIGHT("ABCDEFGHIJ",4),2))&"OST!"
Answer: ghOST! (notice the upper and lower case letters)
=MID("ABC" & RIGHT("DEFGHI",3), 2,3)
Answer: BCG
=(50%+1)*100
Answer: 150
Explination:
50% just means 50/100. Therefore this is the same as
=(50/100+1)*100 ... =(0.5+1)*100 ... =1.5*100 ...
= 150
=50%+1
Answer: I would accept either 150% or 1.5 as the answer.
Explination: Remember that 50% is treated exactly as 50/100. The fact that it displays with a % sign is simply a matter of "formatting". By default if you typed =50%+1 into a cell on a new worksheet and pressed enter the answer would be displayed as 150% (actually as 150.0%). However, if you right click on the cell and choose "Format Cells" and change the category (on the Number tab) from "Percentage" to "General" or "Number" you would see 1.5 in the cell and not 150%.
Write a formula that evaluates to TRUE if A1 + B1 is at least 10% more than three times C1. Otherwise the formula should evaluate to FALSE.
ANSWER: =A1+B1>=1.1*3*C1
Write a formula that evaluates to TRUE if the largest value in column A is twice as much as the smallest value in column A. (otherwise FALSE)
ANSWER: =MAX(A:A)=2*MIN(A:A)
Write a formula that evaluates to TRUE if the largest value from row 1 is different than the largest value from row 2. (otherwise FALSE)
ANSWER: =MAX(1:1)<>MAX(2:2)
Write a formula that evaluates to TRUE if the average of the sum of the values in row1 and the sum of the values in row 2 is at least as much as the product of the largest value from row 1 and the smallest value from row 2. (otherwise FALSE)
ANSWER:=AVERAGE(SUM(1:1),SUM(2:2))>=2*MAX(1:1)*MIN(2:2)
ANSWER:=MAX(STOCKS!A:C)>=2*MIN(BONDS!A:C)