Absolute Values in Relative Cells

[Sarah]

Greetings-
I am currently (attempting) to chart normal distribution for a series
of triathlons. I was able to manipulate all of the data to my liking,
but setting up the formulas for the z-scores of 9 different times (time
of swim, pace of swim, z score of swim, time of transition between swim
and cycle, you get the picture) is a lot of work. Seeing that I have 20
races to analyze in the same fashion, I would like to be able to utilize
my pre-existing chart as a template in which to simply copy the data of
alternate races. However, while the columns are situated consistantly,
there are a varied number of contestants in each race (anywhere from 20
to 150). The STANDARIZE, STDEVP, NORMDIST functions all require an
absolute reference to the AVERAGE &/or to the STDEVP. How can I set up
the worksheet so that when I enter the new times/pace/rate excel
automatically inserts the AVERAGE & STDEVP functions into the
appropriate row relative to the size of the range (2 rows following the
last cell in a range) and use this value to calculate that same range?
In other words, how can I place an absolute value into a relative cell
and still place it into a function?

 

[Bernie Deitrick]

Sarah,

Set up your template with however many rows you have for your first data set. Color the rows that
are used in the formula references. Then, when you want to use your new data set, before copying
the data into the template, either reduce the number of rows by deleting them, or increase the
number of row by inserting them, within the colored range, to match the number of rows of data that
you actually have. Then paste your data as values over the colored cells, and your formulas should
update properly and work well with the new data.

HTH,
Bernie
MS Excel MVP

 

[Jerry W. Lewis]

While Bernie has addressed your question, it seemed worth noting that you
should probably be using STDEV instead of STDEVP. If you are assuming a
normal distribution, then by definition, all the results from a particular
race are a sample, not the entire population.

Jerry

 

[Sarah]

While Bernie has addressed your question, it seemed worth noting tha
you
should probably be using STDEV instead of STDEVP. If you are assumin
a
normal distribution, then by definition, all the results from
particular
race are a sample, not the entire population.


Sarah
Thank You Bernie! As a new excelite, I can use all of the help that
can get. To address the second post, I suppose that I don’t understan
the difference between population and sample…I had assumed tha
“population” indicated the population under question (here, the numbe
of contestants.), and that “sample” indicates a group within th
population as a whole (i.e. an age group within the total number o
contestants). Forgive me my naitivity…I was an art majo