Percentage help 1

[Greg25]

Percentage help

I am a volunteer fireman working on figuring out the percentage of calls
a member makes divided by the number of calls the company has easy =BL7/BL$5
BL7 is the number of calls they made divided by
BL$5 the number of calls their where
The problem I am running into a fireman is either a day or night
If they Make 100% of the calls say night and make some day calls
They end up with over 100%
How to restrict the formula =BL7/BL$5 to produce 100% not 110%
Thanks for any help I am working on getting this all set up in access in
the future and right now linking tables to produce reports
Any help would be greatly appreciated
Greg

K & G Design
kgdesign@warwick.net

 

[Sypher2]

Try this in your formula:
(I'm assuming you have the cell formatted as a percentage, so the "1" below would show as 100%.)

=IF(BL7/BL$5>1,1,BL7/BL$5)

 

[Greg25]

Thanks ill give it a shot tonight Greg

K & G Design
kgdesign@warwick.net

 

[SkipVought]

Hi,
Let's look at the column where the calls are listed, assuming that there is such a column of data.

Are you saying the member A might have 5 calls and B 7 calls and C 8 calls for a total of 20 calls?
Or Might A & C have answered 3 of the same calls etc... and then there migh be only 15 calls total?

The question is, how are you determining the value in BL7 and BL5? Skip,
metzgsk@voughtaircraft.com

 

[Greg25]

Hey Thanks for the response,
Sypher2 & SkipVought

the formula =IF(BL7/BL$5>1,1,BL7/BL$5) seems to work

the way i end up with BL7 that is the total amount of calls
the fireman " A " has made for the year night & day calls

Fireman "A" is considerd a night time fireman

say my company had 100 night time calls BL5,
and also had 50 day calls BL4

Fireman "A" made all 100 night time calls 100%
+ 1 or more day calls that puts if over 100% the company would only
like to see 100% not 110% on the monthly & yearly reports

I also have more ?'s but will have to get back to the fourm
at a later date hope you can help again

Thanks Greg

K & G Design
kgdesign@warwick.net

 

[SkipVought]

Greg,
It seems to me that your data recording may need to be tuned up. Not knowing what the requirements are for the reporting that you are doing, is my problem. But lets assume that in capturing the data, you had these elements...

DATE      COMPANY  FIREMAN
1/1/02    1        A
1/5/02    1        A
1/9/02    2        A
1/13/02   1        A
1/17/02   1        A
1/21/02   1        A
1/25/02   2        A
1/29/02   1        A
1/1/02    2        B
1/6/02    2        B
1/11/02   2        B
1/16/02   2        B
1/21/02   1        B
1/26/02   2        B
1/31/02   2        B

then the distrbution of calls for the month of Jan, would look like this...

Count of Fireman    Company
Date  Fireman       1     2   Grand Total
Jan   A             6     2             8
      B             1     6             7
Jan Total           7     8            15

For this analysis, it is clear that the call percentage for each company and firemen can and should all add up to 100%
Company 1 had 7 calls. Fireman A participated in 6 of them, although his total was 8.
So the percentage results per Company could be shown like this...

Count of Fireman   Company	
Date  Fireman      1       2
Jan   A          86%     25%
      B          14%     75%
Jan Total       100%    100%

Does this make sense? Skip,
metzgsk@voughtaircraft.com

 

[euskadi]

My two cents, since I'm trying to get "IF"s out of my spreadsheets if I can help it...

=MIN(1,BL7/BL$5)