Needs help combining functions.

[gargamel875]

I have a worksheet I use every day at my job at a car dealership to kee
track of the previous days paperwork. I have attached an image of th
form.
On this worksheet Column B lists the advisor who was in charge of th
repair order. For each repair order, the advisor must turn in a sheet o
paper showing completed work. If they turned in that sheet with thei
repair order, I put an X in column D. If they did not, I put NO.
In a chart at the bottom of the worksheet, I calculate the percentage o
how many people turned in the sheet overall. I also calculate eac
individuals turn in percentage.
I was going to use the Countif function =countif(Cell Range in Column D
"X")/Number of Cells for the overall percentage. For example: =counti
(D515, "X")/11
But the problem with this is the number of repair orders changes ever
day. I don't want to have to change the forumla every day. Is ther
anyway to combine countif with another function that will only count ho
many cells in column D are in use?
Also, what formula can I use to calculate the individual advisor'
percentage? The amount of repair orders they are in charge of als
changes every day. Is the a way to use a formula that only counts th
cells that are next to a certain advisor?
I'm not sure if any of this is even possible. So, any help would b
appreciated

+-------------------------------------------------------------------
|Filename: Untitled.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=820
+-------------------------------------------------------------------

 

[GS]

Given that your range starts in row5, why not make this a dynamic range
and use it as follows...

=COUNTIF(MyRange,"X")/COUNTA(MyRange)

...where countif will return the number of Xs and counta will return the
number of non-empty cells in the range. Making it a dynamic range
allows its boundaries to automatically adjust to the number of entries.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Kevin@Radstock]

Hi gargamel875

In C22 & copy down
=COUNTIFS($B$5:$B$17,A22,$D$5:$D$17,"x")/COUNTIFS($B$5:$B$17,A22)
Format cells as "%"
I don't quite understand the second part. Probably me!

Kevin

I have a worksheet I use every day at my job at a car dealership to kee
track of the previous days paperwork. I have attached an image of th
form.
On this worksheet Column B lists the advisor who was in charge of th
repair order. For each repair order, the advisor must turn in a sheet o
paper showing completed work. If they turned in that sheet with thei
repair order, I put an X in column D. If they did not, I put NO.
In a chart at the bottom of the worksheet, I calculate the percentage o
how many people turned in the sheet overall. I also calculate eac
individuals turn in percentage.
I was going to use the Countif function =countif(Cell Range in Column D
"X")/Number of Cells for the overall percentage. For example: =counti
(D515, "X")/11
But the problem with this is the number of repair orders changes ever
day. I don't want to have to change the forumla every day. Is ther
anyway to combine countif with another function that will only count ho
many cells in column D are in use?
Also, what formula can I use to calculate the individual advisor'
percentage? The amount of repair orders they are in charge of als
changes every day. Is the a way to use a formula that only counts th
cells that are next to a certain advisor?
I'm not sure if any of this is even possible. So, any help would b
appreciated

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[gargamel875]

Hi gargamel875

In C22 & copy down
=COUNTIFS($B$5:$B$17,A22,$D$5:$D$17,"x")/COUNTIFS($B$5:$B$17,A22)
Format cells as "%"
I don't quite understand the second part. Probably me!

Kevin

Thanks so much for your help. What I mean by the second part is that th
repair orders per advisor will vary every day. So in the example pic,
would use =countif(D12, "x")/2 to get their percentage for that day
But if they have three repair orders the next day, I have to change th
range to include the third cell. I don't know how to avoid this. I
there formula that when only count the X's in column D only if they ar
in the same row as advisor 1

+-------------------------------------------------------------------
+-------------------------------------------------------------------