Perform functions on the result of adding two columns
 

Hi there,

I have two columns of data before and after and event and
I want to find the median, min, max and quartiles of the
change that has occured (after - before). I can easily
make another column to calculate the differences and
perform the statistics very easily. I already have a lot
of columns so I wanted to find ways of saving space and
since this is a calculation that I do for a number of
variables it would certainly free up columns for me. I
have found functions such as sumx2py2 that act in the way
that I am looking for, but I do not need to find the sum
of the squared differences, I just want to find the
minimum of the differences. Are there functions in Excel
that can do this?

Many thanks

Tim Archer

Tim,

With the 'After' numbers in B1:B100, and the 'Before' numbers in A1:A100,
you can array enter (enter using Ctrl-Shift-Enter)

=MIN(B1:B100-A1:A100)
=MAX(B1:B100-A1:A100)
=MEDIAN(B1:B100-A1:A100)
=QUARTILE(B1:B100-A1:A100,1)
=QUARTILE(B1:B100-A1:A100,2)
=QUARTILE(B1:B100-A1:A100,3)
=QUARTILE(B1:B100-A1:A100,4)

Note that you must match your data range exactly - these won't work with
blanks the way Min and Max usually will.

HTH,
Bernie
MS Excel MVP

"Tim Archer" wrote in message
...
Hi there,

I have two columns of data before and after and event and
I want to find the median, min, max and quartiles of the
change that has occured (after - before). I can easily
make another column to calculate the differences and
perform the statistics very easily. I already have a lot
of columns so I wanted to find ways of saving space and
since this is a calculation that I do for a number of
variables it would certainly free up columns for me. I
have found functions such as sumx2py2 that act in the way
that I am looking for, but I do not need to find the sum
of the squared differences, I just want to find the
minimum of the differences. Are there functions in Excel
that can do this?

Many thanks

Tim Archer

You have to use an array formula. For example type the
following into a cell

=MIN(A2:A6-B2:B6)

And then instead of pressing Enter, press Shift + Enter

This will enter it as an array formula and you will see
that it now has {} around it

={MIN(A2:A6-B2:B6)}

-----Original Message-----
Hi there,

I have two columns of data before and after and event
and
I want to find the median, min, max and quartiles of the
change that has occured (after - before). I can easily
make another column to calculate the differences and
perform the statistics very easily. I already have a
lot
of columns so I wanted to find ways of saving space and
since this is a calculation that I do for a number of
variables it would certainly free up columns for me. I
have found functions such as sumx2py2 that act in the
way
that I am looking for, but I do not need to find the sum
of the squared differences, I just want to find the
minimum of the differences. Are there functions in
Excel
that can do this?

Many thanks

Tim Archer
.