Needs help combining functions.

G

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
(D5:D15, "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
+-------------------------------------------------------------------
 
G

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
 
K

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

gargamel875;1610643 said:
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
(D5:D15, "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

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
G

gargamel875

Kevin@Radstock;1610672 said:
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(D1:D2, "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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top