Hi,

I am attempting to summarize some stats for our Loss Prevention Department.
1 group of positions is trending high (position A, for example), so I want to
further breakdown the stats for this subgroup. I am having trouble finding a
function which will allow me to do this. Can anybody help me with writing the
basic formula or function that will allow me to pull info for this subgroup?

I need to pull out stats such as
% female in position A are ?
Avg $ admission position A
% <90 days employment positon A
ETC, ETC, ETC.

Each month I will have to pull new records from my access DB and put them
into the spreadsheet for updated analysis.

Any help will be greatly appreciated!

Not so easy without a copy of your data, but i made up some data for an
example.
What I used is the conditional sum wizard which you have to install the same
as you would install the analysis pac. You go to Tools/Addins and you check
the box by conditional sum wizard. If it isn't available, you may have to
contact your IT department or help desk if you are on a company network,
otherwise it may be on your Office disks, if you have them, not sure.
First I will paste the fake data and the results, then below that I will
paste a copy of what the formula looks like.
....Well I pasted it and had to delete it because the little table of data I
built wrapped and couldn't be read. Here are the formulas the wizard built,
you will quickly see that you need to get that wizard.

=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($M$2 7:$M$34<H37,IF($G$27:$G$34I37,$O$27:$O$34,0),0),0 ),0))

=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($M$2 7:$M$34<H37,IF($G$27:$G$34I37,$G$27:$G$34,0),0),0 ),0))



The results may be more cooperative when pasted, lets see:

Gender Position Days to accident less than Cost greater than
f a 60 $40
Count of accidents by position, gender, over $40: 2
Total Cost of these accidents where over $40: $110
Average:
$55
The conditional sum wizard allowed me to build a formula that read
conditions from separate cells, that is the four conditions that i set up in
the table, Gender, Position, a 'calculated days from employment to accident'
(I faked up a hire date and an accident date then added a calculated col to
reference), and I added a 'cost of accident' column. I didn't know what that
'avg $ admission position' thing meant. I can send you the sample file, but
you might just need to add in the conditonal sum wizard and then follow the
directions.
Conditional Sum Hint1: Your data table in excel must have headers.
Conditional Sum Hint2: When the first screen comes up, it needs you to
highlight the whole table.
Hint3: If you chose the option to show the criteria as i did above, you will
be able to change the criteria, such as change from 'f' to 'm' or from
position 'a' to 'b' without changing the formula.
Hint4: To count records matching multiple criteria, add an extra column to
your data table with all ones in it (1,1,1) then sum that column for each
record that meets the conditions. Use this answer to divide the other sums
for averages.

This would be easier for me to explain and you to understand if you could
see the little data table, too. Let me know if you want me to send the
example spreadsheet, we will figure something out.
Better yet, install the conditional sum wizard and figure it out, it is not
so hard, really.

Hope this helps
SongBear

"DanceNFree" wrote:

Hi,

I am attempting to summarize some stats for our Loss Prevention Department.
1 group of positions is trending high (position A, for example), so I want to
further breakdown the stats for this subgroup. I am having trouble finding a
function which will allow me to do this. Can anybody help me with writing the
basic formula or function that will allow me to pull info for this subgroup?

I need to pull out stats such as
% female in position A are ?
Avg $ admission position A
% <90 days employment positon A
ETC, ETC, ETC.

Each month I will have to pull new records from my access DB and put them
into the spreadsheet for updated analysis.

Any help will be greatly appreciated!

Dance
I just noticed that if you double click on the original message (yours) the
whole thread comes up in a wide window. I am going to try to paste that table
again, maybe it will help understand what the conditional sum wizard does.

employe position f/m $ Emp Date Had Accident Acc date Days to accident Days
employed current Each
bob a m $40 1/1/2003 y 1/5/2003 4 852 1
george b m $45 1/1/2002 n 0 1217 1
martha a f $50 1/1/2003 y 2/2/2003 32 852 1
bill b m $55 4/5/2005 n 0 27 1
marilee a f $60 7/9/2001 y 8/9/2001 31 1393 1
betty b f $65 6/6/2004 n 0 330 1
moondoggie a m $70 1/1/2000 y 1/25/2000 24 1948 1
SongBear b m $90 5/2/2005 n 0 0 1

{=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($K$ 27:$K$34<H37,IF($G$27:$G$34I37,$M$27:$M$34,0),0), 0),0))}

{=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($K$ 27:$K$34<H37,IF($G$27:$G$34I37,$G$27:$G$34,0),0), 0),0))}


Gender Position Days to accident less than Cost greater than
f a 60 $40
Count of accidents by position, gender, over $40: 2
Total Cost of these accidents where over $40 $110
Average: $55

The average is just the total dollars divided by the count of incidents.
Let's see what it looks like when I post it and then double click the message
thread to look at it in a wide window.


"SongBear" wrote:

Not so easy without a copy of your data, but i made up some data for an
example.
What I used is the conditional sum wizard which you have to install the same
as you would install the analysis pac. You go to Tools/Addins and you check
the box by conditional sum wizard. If it isn't available, you may have to
contact your IT department or help desk if you are on a company network,
otherwise it may be on your Office disks, if you have them, not sure.
First I will paste the fake data and the results, then below that I will
paste a copy of what the formula looks like.
...Well I pasted it and had to delete it because the little table of data I
built wrapped and couldn't be read. Here are the formulas the wizard built,
you will quickly see that you need to get that wizard.

=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($M$2 7:$M$34<H37,IF($G$27:$G$34I37,$O$27:$O$34,0),0),0 ),0))

=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($M$2 7:$M$34<H37,IF($G$27:$G$34I37,$G$27:$G$34,0),0),0 ),0))



The results may be more cooperative when pasted, lets see:

Gender Position Days to accident less than Cost greater than
f a 60 $40
Count of accidents by position, gender, over $40: 2
Total Cost of these accidents where over $40: $110
Average:
$55
The conditional sum wizard allowed me to build a formula that read
conditions from separate cells, that is the four conditions that i set up in
the table, Gender, Position, a 'calculated days from employment to accident'
(I faked up a hire date and an accident date then added a calculated col to
reference), and I added a 'cost of accident' column. I didn't know what that
'avg $ admission position' thing meant. I can send you the sample file, but
you might just need to add in the conditonal sum wizard and then follow the
directions.
Conditional Sum Hint1: Your data table in excel must have headers.
Conditional Sum Hint2: When the first screen comes up, it needs you to
highlight the whole table.
Hint3: If you chose the option to show the criteria as i did above, you will
be able to change the criteria, such as change from 'f' to 'm' or from
position 'a' to 'b' without changing the formula.
Hint4: To count records matching multiple criteria, add an extra column to
your data table with all ones in it (1,1,1) then sum that column for each
record that meets the conditions. Use this answer to divide the other sums
for averages.

This would be easier for me to explain and you to understand if you could
see the little data table, too. Let me know if you want me to send the
example spreadsheet, we will figure something out.
Better yet, install the conditional sum wizard and figure it out, it is not
so hard, really.

Hope this helps
SongBear

"DanceNFree" wrote:

Hi,

I am attempting to summarize some stats for our Loss Prevention Department.
1 group of positions is trending high (position A, for example), so I want to
further breakdown the stats for this subgroup. I am having trouble finding a
function which will allow me to do this. Can anybody help me with writing the
basic formula or function that will allow me to pull info for this subgroup?

I need to pull out stats such as
% female in position A are ?
Avg $ admission position A
% <90 days employment positon A
ETC, ETC, ETC.

Each month I will have to pull new records from my access DB and put them
into the spreadsheet for updated analysis.

Any help will be greatly appreciated!

Dance,
Well, that didn't work, the table came out messed up anyway. You could
highlight the table,it is the blob of text with the lines starting with names
like bob and george. Paste it into an empty excel sheet and do a
Data/text-to-columns thing on it. then you would have the table that the
sample formulas came from. Better yet, install conditional sum wizard and
follow the directions. easier. lol

SongBear

Songbear, The info you provided was helpful. Can you email me directly so
trade files?

"SongBear" wrote:

Not so easy without a copy of your data, but i made up some data for an
example.
What I used is the conditional sum wizard which you have to install the same
as you would install the analysis pac. You go to Tools/Addins and you check
the box by conditional sum wizard. If it isn't available, you may have to
contact your IT department or help desk if you are on a company network,
otherwise it may be on your Office disks, if you have them, not sure.
First I will paste the fake data and the results, then below that I will
paste a copy of what the formula looks like.
...Well I pasted it and had to delete it because the little table of data I
built wrapped and couldn't be read. Here are the formulas the wizard built,
you will quickly see that you need to get that wizard.

=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($M$2 7:$M$34<H37,IF($G$27:$G$34I37,$O$27:$O$34,0),0),0 ),0))

=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($M$2 7:$M$34<H37,IF($G$27:$G$34I37,$G$27:$G$34,0),0),0 ),0))



The results may be more cooperative when pasted, lets see:

Gender Position Days to accident less than Cost greater than
f a 60 $40
Count of accidents by position, gender, over $40: 2
Total Cost of these accidents where over $40: $110
Average:
$55
The conditional sum wizard allowed me to build a formula that read
conditions from separate cells, that is the four conditions that i set up in
the table, Gender, Position, a 'calculated days from employment to accident'
(I faked up a hire date and an accident date then added a calculated col to
reference), and I added a 'cost of accident' column. I didn't know what that
'avg $ admission position' thing meant. I can send you the sample file, but
you might just need to add in the conditonal sum wizard and then follow the
directions.
Conditional Sum Hint1: Your data table in excel must have headers.
Conditional Sum Hint2: When the first screen comes up, it needs you to
highlight the whole table.
Hint3: If you chose the option to show the criteria as i did above, you will
be able to change the criteria, such as change from 'f' to 'm' or from
position 'a' to 'b' without changing the formula.
Hint4: To count records matching multiple criteria, add an extra column to
your data table with all ones in it (1,1,1) then sum that column for each
record that meets the conditions. Use this answer to divide the other sums
for averages.

This would be easier for me to explain and you to understand if you could
see the little data table, too. Let me know if you want me to send the
example spreadsheet, we will figure something out.
Better yet, install the conditional sum wizard and figure it out, it is not
so hard, really.

Hope this helps
SongBear

"DanceNFree" wrote:

Hi,

I am attempting to summarize some stats for our Loss Prevention Department.
1 group of positions is trending high (position A, for example), so I want to
further breakdown the stats for this subgroup. I am having trouble finding a
function which will allow me to do this. Can anybody help me with writing the
basic formula or function that will allow me to pull info for this subgroup?

I need to pull out stats such as
% female in position A are ?
Avg $ admission position A
% <90 days employment positon A
ETC, ETC, ETC.

Each month I will have to pull new records from my access DB and put them
into the spreadsheet for updated analysis.

Any help will be greatly appreciated!

Dance
I emailed, but it must have been too late, I got an out of office autoreply.
SongBear

"DanceNFree" wrote:

Songbear, The info you provided was helpful. Can you email me directly so
trade files?

"SongBear" wrote:

Not so easy without a copy of your data, but i made up some data for an
example.
What I used is the conditional sum wizard which you have to install the same
as you would install the analysis pac. You go to Tools/Addins and you check
the box by conditional sum wizard. If it isn't available, you may have to
contact your IT department or help desk if you are on a company network,
otherwise it may be on your Office disks, if you have them, not sure.
First I will paste the fake data and the results, then below that I will
paste a copy of what the formula looks like.
...Well I pasted it and had to delete it because the little table of data I
built wrapped and couldn't be read. Here are the formulas the wizard built,
you will quickly see that you need to get that wizard.

=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($M$2 7:$M$34<H37,IF($G$27:$G$34I37,$O$27:$O$34,0),0),0 ),0))

=SUM(IF($F$27:$F$34=F37,IF($E$27:$E$34=G37,IF($M$2 7:$M$34<H37,IF($G$27:$G$34I37,$G$27:$G$34,0),0),0 ),0))



The results may be more cooperative when pasted, lets see:

Gender Position Days to accident less than Cost greater than
f a 60 $40
Count of accidents by position, gender, over $40: 2
Total Cost of these accidents where over $40: $110
Average:
$55
The conditional sum wizard allowed me to build a formula that read
conditions from separate cells, that is the four conditions that i set up in
the table, Gender, Position, a 'calculated days from employment to accident'
(I faked up a hire date and an accident date then added a calculated col to
reference), and I added a 'cost of accident' column. I didn't know what that
'avg $ admission position' thing meant. I can send you the sample file, but
you might just need to add in the conditonal sum wizard and then follow the
directions.
Conditional Sum Hint1: Your data table in excel must have headers.
Conditional Sum Hint2: When the first screen comes up, it needs you to
highlight the whole table.
Hint3: If you chose the option to show the criteria as i did above, you will
be able to change the criteria, such as change from 'f' to 'm' or from
position 'a' to 'b' without changing the formula.
Hint4: To count records matching multiple criteria, add an extra column to
your data table with all ones in it (1,1,1) then sum that column for each
record that meets the conditions. Use this answer to divide the other sums
for averages.

This would be easier for me to explain and you to understand if you could
see the little data table, too. Let me know if you want me to send the
example spreadsheet, we will figure something out.
Better yet, install the conditional sum wizard and figure it out, it is not
so hard, really.

Hope this helps
SongBear

"DanceNFree" wrote:

Hi,

I am attempting to summarize some stats for our Loss Prevention Department.
1 group of positions is trending high (position A, for example), so I want to
further breakdown the stats for this subgroup. I am having trouble finding a
function which will allow me to do this. Can anybody help me with writing the
basic formula or function that will allow me to pull info for this subgroup?

I need to pull out stats such as
% female in position A are ?
Avg $ admission position A
% <90 days employment positon A
ETC, ETC, ETC.

Each month I will have to pull new records from my access DB and put them
into the spreadsheet for updated analysis.

Any help will be greatly appreciated!