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!