Absolute Values in Relative Cells
 

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?

Absolute Values in Relative Cells
 

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


"Sarah" wrote in message
...

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?




--
Sarah

Absolute Values in Relative Cells
 

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" wrote:


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?




--
Sarah

[quote=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.


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