Spreadsheet issue

  • Thread starter Thread starter whereisb
  • Start date Start date
W

whereisb

I have a spreadsheet which states either yes or no that a report was
filed on time and then following responses of whether the report was
complete or not, etc.

I need another worksheet in the same workbook to count the number of
'yes' responses but only if that yes was on a given date. For example,
for January 1, 2008 I had 5 reports filed. I had two yes and 3 no. On
the next page of the workbook I need to calculate the % of of yes and
no responses, but only for the specific date of 1-1-08.

How can I do this? PLEASE help!
 
I'm not sure what you want. Do you have a column with a date, then a column
with yes/no to state whether the report was filed on time and another column
with yes/no to indicate if the report is complete? If so, which yes/no
column do you want to count?

Tyro
 
Ok, here it is.

Column A Column B

Date Of Install On Time?
1 1/05/07 Y
2 1/05/07 Y
3 1/05/07 N
4 1/05/07 N
5 1/12/07 Y
6 1/12/07 N

SO...if you were to do a simple calculation to find the % that were on
time (Or not) for a certain date, you would see that for 1/05/07 there
would be 50% on time and 50% not since there are 4 completed reports
and 2 of yes and 2 of no were submitted. 2 yes divided by 4 total
reports = 50%. With me so far?

Now THAT part has worked so far (With the added detail that the
calculation is on another worksheet so I have to enter in the right
page information into the calculation. But the problem I am running
into is that the calculation is autmotically choosing a random a range
of cells as opposed to restricting itself to those of a certain date.
I need to eventually create a graph of each weeks' data. So the
calculation part saying 50% yes and 50% no is correct because I went
in and manually adjusted the range from say, A1 - A6 (Which is wrong
because A5 and A6 are using the date 1/12 and not 1/05) and I adjusted
it back to A1 - A4.

So what I am trying to do is add a second condition into a COUNTIF
statement saying count these numbers and give me a % but ONLY if it
matches a certain date, this way I dont have to go in and manually
adjust the range for each calculation.

I know I am doing a crappy job of explaining this. It's much easier
when you're looking at the workbook and I explain it face to face.
Hopefully this helps. If not, thanks anyway for trying!
 
Suppose your data is on a sheet called Data, and in your summary sheet
you have dates in column A starting at A2, and column B is used for
the %age. Put this formula in B2:

=SUMPRODUCT((Data!A$2:A$1000=A2)*(Data!B$2:B$1000="Y") / COUNTIF(Data!A
$2:A$1000,A2)

I've assumed that you have up to 1000 rows of data in the Data sheet -
adjust the ranges if you have more. Format the cell as percentage and
copy down to cover your range of dates in the summary sheet.

Hope this helps.

Pete
 
Back
Top