77
EXL
11
Survey Results
S
urveys are a great way for businesses to collect data on customer
satisfaction.However,thedatacollectedneedstobeproperlyanalyzed.
UsingExceltocountsurveyresultsanddisplaythoseresultsvisuallycanturna
routinecustomersurveyintoaninformativetoolinapowerfulway.
1. Althoughsurveyquestionshavetextualresponses,itisadvisabletoinputthe
data as numeric. This will ensure consistent input.
2. TheCOUNTIFfunctionrequiresarangeandcriteriainthefollowingformat:
=COUNTIF(RANGE,CRITERIA).
3. Readthroughallinstructionspriortocompletingtheproject.
1. UsingMicrosoftExcel,createanewblankworkbook.
2. SavetheworkbookasProjectEXL-11SurveyResultsinyour"Excel"folder
under "The Hub" folder.
3. KeythedataasitappearsinFigureEXL-11.1andFigureEXL-11.2intothe
spreadsheet.Unlessotherwisenoted,thefontshouldbesettoArial10point.
4. FormatthewidthofcolumnAto41.
5. Format the width of columns B through K to 10.
6. Formatthealignmentofthetextinrow1toasimilarcellorientationasshown
inFigureEXL-11.1,thenbold.
7. BeginningincellB24,entertheCOUNTIFfunctionforeachquestionincolumnA.
Hint:
=COUNTIF(B2:B21,1).
8. IncellB25,entertheCOUNTIFfunctionforthequestionincolumnA.
Hint:
=COUNTIF(B2:B21,2).
9. IncellB26,entertheCOUNTIFfunctionforthequestionincolumnA.
Hint:
=COUNTIF(B2:B21,3).
10. IncellB27,entertheCOUNTIFfunctionforthequestionincolumnA.
Hint:
=COUNTIF(B2:B21,4).
11. UsetheCOUNTIFfunctionfortheremainingquestionsincolumnA.
12. Insertapiechartforeachquestion.
· Giveeachchartanappropriatetitle,suchasthequestion
· Show value labels for each pie piece
· Do not include a legend
· Include the charts on a new Sheet and rename the Sheet "Charts"
· Resizethechartssothattheyfitproportionallyonthreepages
13. Display formulas in your spreadsheet by using Ctrl +` to check for accuracy.
14. Carefully proofread your work for accuracy and format.
your TASK
Your task is to
input the answers
from The Hub's last
customer survey into
a spreadsheet. The
COUNTIF function will
be used to tally the
results of 20 customers
surveyed. You will use
this function to count
the number of cells
within a range that
meet the given criteria.
In order to provide a
visual aid, pie charts
will be created for each
question.
the SKILLS
· UsetheCOUNTIF
function
· Insert pie charts
For Evaluation Purposes Only