Filter under and above certain criteria

[Guest]

I thought I had cracked it but still having problems with this. Can anybody
help???

Basically it`s to do with flying hours budget between Medical and Police.
Medical contributes 30% of the running costs and the Police the
rest up to 40 hours per month and 25% over a combined 40 hours.
What I'm trying to do is to know the break down of hours between both units
and what percentage each use for the first 40 hours and then over that.
The trouble is that not all the hours used by Medical fall under 40 hours,
some will fall over that figure. You might have a situation when there is no
Medical flights under 40 hours and all of it over. So they should only pay
25% of the running costs.
The cells used are as follows:
Cells:
H3 = The reference figure (40 hours)
H9 = Total Med under 40 hours
H11 = Total Med over 40 hours
J9 = Medical percentage over 40 hours
J11 = Percentage under 40
H7 = Total Med hours
(note C18 is the grand total of both units)

Polioce are the same but cells start with "N" for the hours and "P" for the
percentages

I hope that gives you more of an idea on how the lay is.
Data is entered on a table A1160000.
Headings are:
A B C D
Date Unit Log No Time

 

[Stephen]

I thought I had cracked it but still having problems with this. Can anybody
help???

Basically it`s to do with flying hours budget between Medical and Police.
Medical contributes 30% of the running costs and the Police the
rest up to 40 hours per month and 25% over a combined 40 hours.
What I'm trying to do is to know the break down of hours between both
units
and what percentage each use for the first 40 hours and then over that.
The trouble is that not all the hours used by Medical fall under 40 hours,
some will fall over that figure. You might have a situation when there is
no
Medical flights under 40 hours and all of it over. So they should only pay
25% of the running costs.
The cells used are as follows:
Cells:
H3 = The reference figure (40 hours)
H9 = Total Med under 40 hours
H11 = Total Med over 40 hours
J9 = Medical percentage over 40 hours
J11 = Percentage under 40
H7 = Total Med hours
(note C18 is the grand total of both units)

Polioce are the same but cells start with "N" for the hours and "P" for
the
percentages

I hope that gives you more of an idea on how the lay is.
Data is entered on a table A1160000.
Headings are:
A B C D
Date Unit Log No Time

I'm not sure if it's just me, but the above description isn't clear.

I thought I understood: "Medical contributes 30% of the running costs and
the Police the rest up to 40 hours per month and 25% over a combined 40
hours", until I got to: "You might have a situation when there is no Medical
flights under 40 hours and all of it over". How can this latter situation
arise? Or do you not mean that the split is M=30%, P=70% for the first 40
hours?

 

[Guest]

Sorry about that. it was confusing for me at first!!
Yes to your question that under 40 hours the split is about 30% 70% and
25%(medical and 75% (police) above 40 hours.
What I need to show is how much in % the medical unit use under 40 hours(
normally it exceeds 30%) and over 40 hours. This 40 hours is the point at
which the rate changes from 30% to 25%. Also the 40 hours is a combined
between both unit.
I hope I'm not talking double dutch!!!!!
Any ideas.