BA 254 – “Excel Project 1.2B” (Spitler)


>>THIS IS THE VIDEO
DEMONSTRATION FOR BA 254, EXCEL PROJECT 1.2B. WHEN WE OPEN UP THE
PROJECT ITSELF, PART “B,” WE’RE GOING TO
ACTUALLY GO IN AND CALCULATE DIFFERENT
PERCENTILE VALUES FROM A SET OF
GIVEN DATA. WE’RE NOT GOING TO USE THE
RANK AND PERCENTILES FUNCTION FOUND IN THE DATA
ANALYSIS TOOL. THIS CALCULATES THE RANK
AND PERCENTILE VALUE FOR EVERY SINGLE
DATA POINT. INSTEAD, WE ARE GOING TO
USE THE PERCENTILE FORMULA TO CALCULATE THE
ZERO, 25th, 50th, 75th, AND 100th
PERCENTILES. OKAY, AND THEN WE
ARE GOING TO GO ALONG AND CHECK USING THE FAMILIAR
DESCRIPTIVE STATISTICS FUNCTION UNDERNEATH OUR DATA
ANALYSIS TOOL PACK. THIS IS THE SET OF DATA
THAT YOU WILL USE– IT’S CONTAINED WITHIN
THE PROJECT FILE. THE DEMONSTRATION DATA
THAT I’M GOING TO USE IS A SERIES OF PRICES
LISTED OFF TO THE SIDE. IF WE WANT TO GO IN AND
CALCULATE THE ZERO PERCENTILE, WE WANT TO GO TO
“INSERT A FORMULA,” WE’RE GOING TO GO
INTO “STATISTICAL,” FIND THE
PERCENTILE FORMULA. WE DO NOT WANT TO USE
THE PERCENTILE RANK. WE DO NOT WANT TO USE
THE PERCENTILE EXCLUSIVE. WE WANT TO USE THE
PERCENTILE INCLUSIVE. THAT WILL FOLLOW THE SAME
CALCULATION THAT WE DID BY HAND IN THE FIRST SECTION
OF CLASS. MY ARRAY IS ALL OF MY,
IN THIS CASE, PRICING DATA. “K” IS THE PERCENTILE VALUE
THAT I’M LOOKING FOR. AND AGAIN, I WANT TO HIGHLIGHT
THAT THE PERCENTILE VALUE HERE NEEDS TO BE BETWEEN
ZERO AND ONE, AND IT’S GOING TO
INCLUDE ZERO AND ONE. WE WANT TO MAKE SURE THAT
THIS IS ENTERED AS A DECIMAL. THE PERCENTILE VALUES THAT
I HAVE IN MY MATRIX HERE ARE ALREADY ENTERED
AS A DECIMAL. SO I’M SIMPLY GOING
TO CLICK ON THAT. IT WILL THEN RETURN
THE PERCENTILE VALUE FOR THE ZERO
PERCENTILE. NOW, WE WANT TO CREATE
AN ABSOLUTE REFERENCE SO THAT OUR PRICING DATA
REMAINS IN THE SAME PLACE. I DID THAT AGAIN BY PUTTING
IN THE DOLLAR SIGNS– DIFFERENT THAN THE DOLLAR
DENOTED IN TERMS OF PRICE. BUT AGAIN, THESE WILL CREATE
JUST AN ABSOLUTE REFERENCE. I DID THAT BY HITTING
THE F4 KEY ON MY PC. NOW THAT WE’VE CREATED
THAT ABSOLUTE REFERENCE, WE CAN SIMPLY COPY AND
PASTE OR FILL DOWN. I DID THAT BY SIMPLY
WAITING UNTIL I HOVERED OVER THE BOTTOM RIGHT-HAND
CORNER OF MY CELL THAT I WANTED
A COPY, AND IT WENT FROM THE BIGGER
PLUS TO THE SMALLER PLUS. GENIUS AT WORK FROM OUR
ENGINEERS AT MICROSOFT. AND I COULD HOLD DOWN
THE LEFT MOUSE KEY AND COPY AND
PASTE THAT DOWN. AS ALWAYS, I CAN GO IN AND
REFORMAT THE LOOK OF THE DATA, THE VALUES IN THE CHART BY
CHANGING THE TEXT, THE COLOR, BACKGROUND COLORS,
ETCETERA. THE LAST THING I’D
LIKE YOU TO SEE– OR I WANT TO SEE YOU
DO IN THIS PROJECT IS TO GO IN AND
CHECK YOUR VALUES BY CLICKING ON THE
DESCRIPTIVE STATISTICS TOOL, DATA ANALYSIS,
DESCRIPTIVE STATISTICS. MY INPUT RANGE, AGAIN, IS ALL
OF MY PRICING INFORMATION. IT’S, OF COURSE, GROUPED
TOGETHER IN A COLUMN. I DID INCLUDE THE LABEL
THIS TIME, SO CLICK ON THAT. MY OUTPUT RANGE TELLS ME WHERE
I WANT TO PUT THE DATA ANALYSIS. KEEP “SUMMARY STATISTICS”
CLICKED, HIT “OKAY.” NOW, WE CAN SEE THAT OUR
ZERO PERCENTILE LINES UP TO OUR MINIMUM VALUE, OUR 100th PERCENTILE
LINES UP TO OUR MAXIMUM VALUE, AND OF COURSE, THE MEDIAN
LINES UP TO THE 50th PERCENTILE. THIS CONCLUDES THE VIDEO
DEMONSTRATION FOR BA 254, EXCEL 1.2B.

Posts Tagged with…

Write a Comment

Your email address will not be published. Required fields are marked *