Average Function
 

Hi Everyone,

I am using Excel 2003. I understand there is probably a very sophisticated
way to get what I want but I need to learn the simple way first. I have a
data sheet named "MICU". I inserted another sheet in the workbook called
"Stats". I want to break down the data by month on my new Stats sheet. The
MICU sheet has many columns of info. The 2 I think I need to use is a
column for start date "ThpyStDtTm" (Date and Time field 3/14/09 18:41) and
a column called "VentLOSDays" (Number format with 2 decimal places).

Column A Column B
Month Ave VentLOS
June-08 2.41
July-08 3.75
Aug-08 3.42

I clicked on cell B2 (The June-08 Ave VentLOS cell) and inserted the ave
function using the Insert/Function tool menu then used the little function
arguments dialog box to go to the MICU sheet and selected all the numbers in
the average LOS column for patients started on therapy in June. Is there a
way to have an argument in there to find all the records from June 08 using
the ThpyStDtTm column? This is what my function looks like so far =AVERAGE
('MICU'!K17:K52). The July-08 looks like this =AVERAGE ('MICU'!K53:K93).
This works great but I have to select by hand/eye which is time consuming
and errors occur if "I haven't had enough coffee" and I am not sure what
will happen if I decide to exclude a record later on, or perhaps sort this
list another way.

Thanks,
Linda

Average Function
 

Hi Linda,

First of all, June-08 is that supposed to be June 2008? If so make sure by
selecting a cell with this value and look in the formula bar and make sure
it doesn't say 6/8/2009, that is what my Excel does if I type June-08, Excel
thinks you mean June 8th current year. Anyway it is very easy to get average
for particular months and years,



=AVERAGE(IF((MONTH(A3:A21)=6)*(YEAR(A3:A21)=2008), B3:B21))



the above will average values in B where A = June 2008


but first make sure the dates are really June 08 values and not June 2009



--


Regards,


Peo Sjoblom


"Linda RQ" wrote in message
...
Hi Everyone,

I am using Excel 2003. I understand there is probably a very
sophisticated way to get what I want but I need to learn the simple way
first. I have a data sheet named "MICU". I inserted another sheet in the
workbook called "Stats". I want to break down the data by month on my new
Stats sheet. The MICU sheet has many columns of info. The 2 I think I
need to use is a column for start date "ThpyStDtTm" (Date and Time field
3/14/09 18:41) and a column called "VentLOSDays" (Number format with 2
decimal places).

Column A Column B
Month Ave VentLOS
June-08 2.41
July-08 3.75
Aug-08 3.42

I clicked on cell B2 (The June-08 Ave VentLOS cell) and inserted the ave
function using the Insert/Function tool menu then used the little function
arguments dialog box to go to the MICU sheet and selected all the numbers
in the average LOS column for patients started on therapy in June. Is
there a way to have an argument in there to find all the records from June
08 using the ThpyStDtTm column? This is what my function looks like so
far =AVERAGE ('MICU'!K17:K52). The July-08 looks like this =AVERAGE
('MICU'!K53:K93). This works great but I have to select by hand/eye which
is time consuming and errors occur if "I haven't had enough coffee" and I
am not sure what will happen if I decide to exclude a record later on, or
perhaps sort this list another way.

Thanks,
Linda

Average Function
 

Forgot to tell you the formula is an array formula and needs to be entered
with ctrl + shift & enter

--


Regards,


Peo Sjoblom


"Peo Sjoblom" wrote in message
...
Hi Linda,

First of all, June-08 is that supposed to be June 2008? If so make sure by
selecting a cell with this value and look in the formula bar and make sure
it doesn't say 6/8/2009, that is what my Excel does if I type June-08,
Excel thinks you mean June 8th current year. Anyway it is very easy to get
average for particular months and years,



=AVERAGE(IF((MONTH(A3:A21)=6)*(YEAR(A3:A21)=2008), B3:B21))



the above will average values in B where A = June 2008


but first make sure the dates are really June 08 values and not June 2009



--


Regards,


Peo Sjoblom


"Linda RQ" wrote in message
...
Hi Everyone,

I am using Excel 2003. I understand there is probably a very
sophisticated way to get what I want but I need to learn the simple way
first. I have a data sheet named "MICU". I inserted another sheet in the
workbook called "Stats". I want to break down the data by month on my
new Stats sheet. The MICU sheet has many columns of info. The 2 I think
I need to use is a column for start date "ThpyStDtTm" (Date and Time
field 3/14/09 18:41) and a column called "VentLOSDays" (Number format
with 2 decimal places).

Column A Column B
Month Ave VentLOS
June-08 2.41
July-08 3.75
Aug-08 3.42

I clicked on cell B2 (The June-08 Ave VentLOS cell) and inserted the ave
function using the Insert/Function tool menu then used the little
function arguments dialog box to go to the MICU sheet and selected all
the numbers in the average LOS column for patients started on therapy in
June. Is there a way to have an argument in there to find all the
records from June 08 using the ThpyStDtTm column? This is what my
function looks like so far =AVERAGE ('MICU'!K17:K52). The July-08 looks
like this =AVERAGE ('MICU'!K53:K93). This works great but I have to
select by hand/eye which is time consuming and errors occur if "I haven't
had enough coffee" and I am not sure what will happen if I decide to
exclude a record later on, or perhaps sort this list another way.

Thanks,
Linda

Average Function
 

Thanks Peo,

When I first made my sheet I did noticed that my dates were showing up as
June 8 and July 8 etc if I changed the format...I did change them at that
time. I'll try this tomorrow when I get back to work.

Thanks,
Linda


"Peo Sjoblom" wrote in message
...
Forgot to tell you the formula is an array formula and needs to be entered
with ctrl + shift & enter

--


Regards,


Peo Sjoblom


"Peo Sjoblom" wrote in message
...
Hi Linda,

First of all, June-08 is that supposed to be June 2008? If so make sure
by selecting a cell with this value and look in the formula bar and make
sure it doesn't say 6/8/2009, that is what my Excel does if I type
June-08, Excel thinks you mean June 8th current year. Anyway it is very
easy to get average for particular months and years,



=AVERAGE(IF((MONTH(A3:A21)=6)*(YEAR(A3:A21)=2008), B3:B21))



the above will average values in B where A = June 2008


but first make sure the dates are really June 08 values and not June 2009



--


Regards,


Peo Sjoblom


"Linda RQ" wrote in message
...
Hi Everyone,

I am using Excel 2003. I understand there is probably a very
sophisticated way to get what I want but I need to learn the simple way
first. I have a data sheet named "MICU". I inserted another sheet in
the workbook called "Stats". I want to break down the data by month on
my new Stats sheet. The MICU sheet has many columns of info. The 2 I
think I need to use is a column for start date "ThpyStDtTm" (Date and
Time field 3/14/09 18:41) and a column called "VentLOSDays" (Number
format with 2 decimal places).

Column A Column B
Month Ave VentLOS
June-08 2.41
July-08 3.75
Aug-08 3.42

I clicked on cell B2 (The June-08 Ave VentLOS cell) and inserted the ave
function using the Insert/Function tool menu then used the little
function arguments dialog box to go to the MICU sheet and selected all
the numbers in the average LOS column for patients started on therapy in
June. Is there a way to have an argument in there to find all the
records from June 08 using the ThpyStDtTm column? This is what my
function looks like so far =AVERAGE ('MICU'!K17:K52). The July-08 looks
like this =AVERAGE ('MICU'!K53:K93). This works great but I have to
select by hand/eye which is time consuming and errors occur if "I
haven't had enough coffee" and I am not sure what will happen if I
decide to exclude a record later on, or perhaps sort this list another
way.

Thanks,
Linda