Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / February 2008

Tip: Looking for answers? Try searching our database.

Spreadsheet issue

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
whereisb@whereisbrian.com - 05 Feb 2008 23:02 GMT
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!
Tyro - 05 Feb 2008 23:11 GMT
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

>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
[quoted text clipped - 7 lines]
>
> How can I do this? PLEASE help!
whereisb@whereisbrian.com - 06 Feb 2008 14:46 GMT
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!
Pete_UK - 06 Feb 2008 15:41 GMT
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

On Feb 6, 2:46 pm, where...@whereisbrian.com wrote:
> Ok, here it is.
>
[quoted text clipped - 33 lines]
> when you're looking at the workbook and I explain it face to face.
> Hopefully this helps. If not, thanks anyway for trying!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.