Test%1%Study%Guide%Chapters%125%%Chapter%1%1. What%is%the%meaning%of%each%of%the%following%error%messages?%• #######% % Insufficient%Cell%width%• #NAME?% % Unrecognized%text%in%formula%%• #N/A% % No%Answer%• #REF% % Invalid%ce ll%re fe re n c e%• #Value% % Wrong%Argument%type%%• #Num% % Invalid%n u meric%value s %in %a%f o rmula%or%fu n ct io n%• #Div/0% % Division%by%zero%%2. If%you%en te r%1 ,1 4 9 + 2 5 ,%w h a t%v alu e %would%re su lt ?%%The%cell%would%display%1,149+25%3. If%you%en te r%= 2 + 4 * 1 0 %exactly%as%shown,%what%value%would%result?% %42%4. List%the%orders%of%operations%in%order% %1) Parenthesis%()%2) Exponentiation%3) Multiplication%&%Division%4) Adding%and%subtracting%%5. When%writing%formulas,%why%is%it%preferable%to%use%cell%reference%rather%than%typing%in%values?% %Using%cell%references%is%preferable%to%directly%inputting%values%into%a%formula%because%it%allows%the%user%to%easily%update%the%spreadsheet%without%having%to%know%exactly%which%formula%or%formulas%contain%the%changed%value.%6. In%the%w o r ks h ee t %b elo w,%cell%A3%contains%the%formula%=A1+A2.%Explain%the%most%likely%reason%the%value%calculated%appears%incorrect.%%The%most%likely%reason%is%that%the%format%chosen%for%A1%and%A2%will%not%show%decimals.%Therefore%if%cell%A1%is%“1.4”%and%cell%A2%is%“2.4”,%the%result%in%cell%A3%is%actually%“3.8”%which%is%displayed%as%“4”%when%rounding.%%%7. Referring%to%the%preceding%worksheet,%if%you%wrote%the%formula%=B1*110,%what%value%would%result%(assuming%the%displayed%value%is%the%precise%value)?%11%What%formula%would%you%write%to%do%each%of%the%following?% %a.%Add%a%range%of%numbers%in%cells%A2:X2.% % % % % % =SUM(A2:X2)%b.%Find%the%largest%value%in%cells%C2:C8.% % % % % % % =MAX(C2:C8)%c.%Find%the%smallest%value%in%cells%B2%through%Z12.% % % % % =MIN(B2:Z12)%d.%Find%the%average%value%in%cells%C1%through%C10,%ignoring%blan k%c ells % % % =AVERAGE(C1:C10,%0)%e.%Find%the%total%number%of%values%in%cells%C1%:10,%excluding%any%that%contain%text.% =COUNT(C1:C10)%%8. Define%the%following%terms:%syntax,%arguments,%and%algorithm.%Syntax% % The%specific%format%of%a%function%including%the%function%name%and%the%order%of%the%arguments%Arguments% A%function%input%(when%putting%a%number%and%how%many%decimal%places)%Algorithm% A%rule%that%governs%how%a%function%works.%A%systematic%set%of%procedures%that%the%computer%always%steps%through%to%calculate%the%results%of%a%function%9. If%the%formula%=$B $ 4–SUM(C1:C5)%is%copied%from%cell%A9%to%cell%C10,%what%is%the%resulting%formula?%=$B$42SUM(E2:E6)%10. Refer%to%the%following%worksheet.%What%formula%would%you%write%in%cell%B2%that%can%be%copied%down%the%column%and%across%the%row%to%complete%the%multiplication%table?%%%=SUM($A2*B$1)%%%11. What%new%formula%results%for%each%of%the%following%if%the%formula%is%copied%from%cell%C10%to%cell%E13?%=A1+A2%% % % à% % % =C4+C5%=$A$1+A2% % % à% % % =$A$1+C5%=$A1+A2% % % à% % % =$A4+C5%=A$1+A2% % % à% % % =C$1+C5%%12. What%formula%could%you%use%to%add%up%cell%B1%from%Sheet1!,%Sheet%2!,%and%Sheet%3!%(assuming%the%worksheets%are%contiguous%and%in%the%same%workbook)?%=SUM(Sheet1!B1,Sheet2!B1,Sheet3!B1)%13. Cell%B1%has%been%given%the%range%name%“discount”.%How%would%you%write%a%formula%in%cell%C1%that%multiplies%discount%by%cell%A1?%What%new%formula%results%if%you%copy%this%formula%into%cell%C2?%=discount*A1% % % % % % =discount*A2%Chapter(2(%1. What%formula%could%you%write%to%calculate%the%mean%of%the%following%data%set:%2,5,4,3,1,2,7?%=AVERAGE(2,5,4,3,1,2,7)%2. What%is%the%median%value%of%the%set:%2,5,4,3,1,2,7% %=MEDIAN(2,5,4,3,1,2,7)%3. What%is%the%mode%of%the%data%set:%2,5,4,3,1,2,7% %=MODE(2,5,4,3,1,2,7) %4. The%data%set:%2,5,4,3,1,2,7,%has%a%standard%deviation%of%1.58%as%compared%with%another%data%set%that%has%the%same%mean%but%a%standard %deviation%of%2.5.%What%gen eral%differences%would%you %expect%to%find%between %the%two%sets%of%data?% %Since%Standard%deviation%is%the %m easu re%o f%rang e%an d%d istributio n,%the %larger%stan d ard %devia tion%w o uld %result%in%a%larger%range%of%values.%5. In%the%ch a p te r,%t h e%o r ig in al%la b o r %ra te %fo r%in s p e ct o rs %w a s%g iv en %a s %$ 35 %p e r%hour.%Ho wever,%du e %to %a %co n t ra ct %renegotiation ,%this%value %is%now %$3 7.50 .%W ha t%algeb raic%ex pre ssion %cou ld%yo u%us e%to%d eterm in e%the %pe rcen t%increase %in %lab o r%c o sts ? %(N o te %th a t%a %re su lt ing %v alu e %is %no t %re qu ire d .)% %=(37.50235)/35%6. When%using%the%increase%decimal%button%on%the%toolbar,%the%precise%value%in%the%cell%is%modified.%True%or%false?%False%7. The%formula%=Round(345.43,0)%results%in%what%precise%value?% %345%8. Write%a%formula%to%round%up%63.34%%to%the%nearest%percent% %=ROUNDUP(63.34%,2)%9. What%is%the%symbol%for%the%greater%than%or%equal%to%relational%operator%in%Excel?%>=%10. What%is%the%symbol%for%the%not%equal%to%relational%operator%in%Excel?% %<>%%%%11. Review%the%following%worksheet,%and%then%use%the%COUNTIF%function%to%write%a%formula%that%determines%the%number%of%GM%cars%on%this%list.% %%%%=COUNTIF(A2:A10,"GM")%%%%12. Using%the%worksheet%shown%in%Question%11,%write%a%formula%to%determine%the%number%of%cars%that%cost%less%than%$20,000.%=COUNTIF(B2:B10,"< 20000")%13. Using%the%worksheet%shown%in%Question%11,%write%a%formula%to%determine%the%total%value%of%all%Ford%cars.%%=SUMIF(A2:A10,"Ford",B2:B1 0) %14. Explain%the%difference%between%a%What2if%analysis%an d %G oal%Seek%by%giving%an%example%based%on%the%worksheet%for%question%11 ? % %What%if:%to%determine%the %ou tcome%of%changing%o ne %or%m ore %inpu t%valu es%an d%e valu ate%th e%reca lcula ted %results.%For%example,%you%could%determine%how%changing%the%price%of%a%Ford%would%affect%the%“total”%cell.%Goal%Seek:%tool%that%allows%you%to%work%b ack w ard %to%de term ine %the%inp u t%requ ired %that%w ill%ensure %a%spe cific%outcome.%For%example,%you%could%use%goal%seek%to%determine%what%price%a%Ford%needs%to%be%to%make%the%“total”%value%over%$200,000.%15. Using%the%worksheet%shown%in%Question%11,%write%a%formula%to%determine%the%value%of%the%third%most%expensive%car.% %=LARGE%(B2:B10,3)%16. If%each%ca r%s h o w n%in%Que stio n%11%is%marke d %u p %b et w e en %5 0 %a n d %25 0 %in c rements,%w h at %fu n ctio n %c o uld %b e %u se d %to %random ly%assign %the %am o un t%to%be %add ed %to%the %car%p rice%in%th is%form u la?% %=B2+%RANDBETWEEN(50,250 )%17.
View Full Document
Unlocking...