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" <timothy_archer@hotmail.com> wrote in message
news:0e1a01c50935$9eb49a80$a601280a@phx.gbl...
> 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
>.
>