tt:mm:ss interferes with defined range?

[ro]

Hi group,

This is my log <http://img3.freeimagehosting.net/uploads/
8218e756a1.png> (notice the double space before hours) and here are
the lists <http://img3.freeimagehosting.net/uploads/dad8c1362f.png>.
It appears that the form dont see the hh:mm:ss as part of the date and
sums the column (product= 39405,49).
I've found an older post by Sjoblom where he uses the INT command.
This should get ride of the time but I not sure where to put it in my
DateList OFFSET(Log!$A$2;0;0;COUNTA(Log!$A:$A);1) .

I'm defining these lists because Im on a older version of Excel and
would like to extract total files transfers for a given date range
(second worksheet "Stats").

Have a nice day,
Rasmus

 

[T. Valko]

I've found an older post by Sjoblom where he uses the INT command.

This should get ride of the time but I not sure where to put it in my
DateList OFFSET(Log!$A$2;0;0;COUNTA(Log!$A:$A);1) .

You wouldn't put it in that formula. That formula defines the range. You
want to to perform the INT function *on the range* so it would be like this:

INT(DateList)

It sounds like you want to sum column D for a specific date. If that's what
you want to do, do you also have a defined range formula for column D?

 

[ro]

It sounds like you want to sum column D for a specific date. If that's what
you want to do, do you also have a defined range formula for column D?

Yes, thats correct. The log is from a 24/7/365 filetransfer flow and I
would like to extract the number of files processed for a given date
range (last week, n-month, year, user defined range). I have had some
help from Bob Phillips and Peo Sjoblom from this group but due to my
very (very) basic Excel skills I dont know how to solve what I think
is sumproduct seeing the hh:mm:ss as numbers and not part of the date.

Furthermore Im on an older version of Excel and apparently sumproduct
only works on the same worksheet as the log data, so I have been
trying to create ranges for use in sumproduct on the second worksheet
"stats" but as you can hear things arent going as I could hope for

I've found a tutorial about named ranges and sumproduct <http://
www.contextures.on.ca/xlNames02.html>. The spreadsheet from this
tutorial outputs both numbers and a chart <http://
img3.freeimagehosting.net/uploads/c93749efaf.png>. If you could help
me create something similar from my log data I really owe you one
mate..

Kind regards,
Rasmus Olsen

 

[T. Valko]

Ok...

I'm using the U.S. English version of Excel. Our delimiter is a comma. You
may have to replace them with a semi-colon.

Assuming the named range for column D is CountList.

To sum CountList based on specific date (Nov 20 2007):

=SUMPRODUCT(--(INT(DateList)=DATE(2007,11,20)),CountList)

To sum CountList based on a date range (start date to end date inclusive):

A1 = start date
B1 = end date

=SUMPRODUCT(--(INT(DateList)>=A1),--(INT(DateList)<=B1),CountList)

To sum CountList based on a specific month (November - month 11):

=SUMPRODUCT(--(MONTH(DateList)=11),CountList)

To sum CountList based on a specific year (2007):

=SUMPRODUCT(--(YEAR(DateList)=2007),CountList)

 

[ro]

To sum CountList based on specific date (Nov 20 2007):
=SUMPRODUCT(--(INT(DateList)=DATE(2007,11,20)),CountList)

To sum CountList based on a specific month (November - month 11):
=SUMPRODUCT(--(MONTH(DateList)=11),CountList)

To sum CountList based on a specific year (2007):
=SUMPRODUCT(--(YEAR(DateList)=2007),CountList)

Eureka! Its working =) Thank you so much.

In the tutorial I mentioned (http://www.contextures.on.ca/
xlNames02.html) you can select a user defined date range via data
validation lists and a chart is automatically updated. When I insert
this data list (=DateList) I again have this high value output.
Defining the list as =INT(DateList) gives an error. Can you help me
with this Valko?

Yours sincerly,
Rasmus Olsen

 

[T. Valko]

I again have this high value output.

Does that mean you get a big number like 39407?

If so, just format the cell as DATE. That's how Excel stores dates, as the
number of days since a base date of Jan 1 1900. So, if your format 39407 as
a DATE the cell will display (depending on which format style you choose)
11/21/2007.

 

[ro]

Does that mean you get a big number like 39407?

If so, just format the cell as DATE. That's how Excel stores dates, as the
number of days since a base date of Jan 1 1900. So, if your format 39407 as
a DATE the cell will display (depending on which format style you choose)
11/21/2007.

Wuhuu! Your sir are a wizard

Maybe you can help me with this last obstacle: The two lists to select
the date range from is working now but when trying to sum the file
count and size in two cells for use in a chart, Excel returns a '0'?
This is what it looks like:

"Stats" worksheet

C8[date list #1] E8[date list #2]

B15[Count] C15[SUMPRODUCT(--(SizeList=B15);(CountList);--(DateList>=$C
$8);--(DateList<=$E$8))]
B16[Size] C16[SUMPRODUCT(--(SizeList=B15);(CountList);--(DateList>=$C
$8);--(DateList<=$E$8))]

The idea is to use the calculated count and size in a chart but both
cells return a '0'.


Yours sincerly,
Rasmus

 

[ro]

C8[date list #1] E8[date list #2]

data validation lists, not date list.

 

[T. Valko]

Does that mean you get a big number like 39407?

If so, just format the cell as DATE. That's how Excel stores dates, as
the
number of days since a base date of Jan 1 1900. So, if your format 39407
as
a DATE the cell will display (depending on which format style you choose)
11/21/2007.

Wuhuu! Your sir are a wizard

Maybe you can help me with this last obstacle: The two lists to select
the date range from is working now but when trying to sum the file
count and size in two cells for use in a chart, Excel returns a '0'?
This is what it looks like:

"Stats" worksheet

C8[date list #1] E8[date list #2]

B15[Count] C15[SUMPRODUCT(--(SizeList=B15);(CountList);--(DateList>=$C
$8);--(DateList<=$E$8))]
B16[Size] C16[SUMPRODUCT(--(SizeList=B15);(CountList);--(DateList>=$C
$8);--(DateList<=$E$8))]

The idea is to use the calculated count and size in a chart but both
cells return a '0'.


Yours sincerly,
Rasmus

Both formulas are exactly the same?

While they "should" work the way you have them I prefer to write them in a
more logical manner:

=SUMPRODUCT(--(SizeList=B15);--(DateList>=$C$8);--(DateList<=$E$8);CountList)

That being said, I can't tell why the result would be 0. The formula is
correct. You introduced a new variable: SizeList=B15. What is that?

 

[ro]

Both formulas are exactly the same?

That is what this tutorial instructed me to do.. ?

While they "should" work the way you have them I prefer to write them in a
more logical manner:

=SUMPRODUCT(--(SizeList=B15);--(DateList>=$C$8);--(DateList<=$E$8);CountList)

Unfortunately this still output a 0.

That being said, I can't tell why the result would be 0. The formula is
correct. You introduced a new variable: SizeList=B15. What is that?

The SizeList is the defined range for the column E from the Log
worksheet. This tutorial I keep referring to instructed me to define
the first range (DateList - column A) as =OFFSET(Log!$A
$2;0;0;COUNT(Log!$A:$A);1). The following file count and size range
(column D and E) is defined as =OFFSET(DateList;0;3) and
=OFFSET(DateList;0;4). It is this SizeList that I target with the
SizeList=B15.

I have uploaded the XLS document at <ftp://ftp.dxp.dk/job_log.xls.zip>
user:freecall pass:dxpfree

What Im after is an output of the file count sum and date that I can
put in a chart. If you have a smarter way please let me know

Rasmus

 

[T. Valko]

I'll be away for a few hours. I'll take a look at your file when I return
later this evening. It's 5:20 PM where I'm at.

 

[T. Valko]

OK...

I'm not sure what you're trying to do with these formulas:

=SUMPRODUCT(--(SizeList=B15),--(DateList>=$C$8),--(DateList<=$E$8),CountList)
=SUMPRODUCT(--(CountList=B15),--(DateList>=$C$8),--(DateList<=$E$8))

Both formulas reference B15 which contains the TEXT value Count. So:

SizeList=B15 is testing the named range SizeList to see if it contains the
TEXT value Count
CountList=B15 is testing the named range CountList to see if it contains the
TEXT value Count

Neither of those named ranges contain the TEXT value Count. That is why both
formulas return 0. Another problem is that the drop downs for the dates also
contain the times (although the times are not displayed because of the
format). It seems to me that you're not interested in the time. I would just
extract the unique dates from DateList and use that as the source for the
drop downs. You might even want to remove the times from DateList, also.

 

[ro]

Neither of those named ranges contain the TEXT value Count. That is why both
formulas return 0. Another problem is that the drop downs for the dates also
contain the times (although the times are not displayed because of the
format). It seems to me that you're not interested in the time. I would just
extract the unique dates from DateList and use that as the source for the
drop downs. You might even want to remove the times from DateList, also.

Yes, your right, Im not interested in the time - only the dates. To
whole purpose of this Excel exercise is to be able to extract file
count for a specific date range - and have a chart drawn if possible.
Can you give me a hint or two on how to remove the times from the
datelist - and have Excel output the selected date range and specific
file count in some cells I can create a chart from (that is what the
two erroneous sumproduct forms were intented to do)?

Your help is really appreciated Valko. Unfortunately we live in very
different timezones and Im not able to answer your replies as quickly
as I would like to.. I know nothing about Excel so the project would
not have been possible without the help from you

Yours sincerly,
Rasmus

 

[T. Valko]

See if this is what you had in mind...

Let's get rid of the times in DateList...

Select the entire range, DateList
Goto the menu Data>Text to Columns
Select Fixed width
Click Next
In the Data preview box place the cursor on the second vertical break line
then double click. This will remove that break line.
Click Next
In the Data preview box click where is says General directly above the times
In Column data format select Do not import
Click Finish
With the DateList range still selected
Format as DATE

That will get rid of the times.

Now, let's create a summary of the Log data on the Stats sheet. This will be
the data you can use for a chart.

On the Stats sheet:

Enter these headers in A1:C1

Dates, Count, Size

To extract the unique dates from DateList enter this formula in A2 and copy
down as needed. This will extract the unique dates in ascending order.

=SMALL(DateList,SUMPRODUCT(COUNTIF(DateList,A$1:A1))+1)

Format these cells as DATE

To get the count for the dates in column A enter this formula in B2 and copy
down as needed:

=SUMIF(DateList,A2,CountList)

To get the total size enter this formula in C2 and copy down as needed:

=SUMIF(DateList,A2,SizeList)

You can use this data for you chart.

 

[ro]

See if this is what you had in mind...

Hi Valko, your the best - the time is gone and everything calculates
as its supposed to do ;-)

To get the count for the dates in column A enter this formula in B2 and copy
down as needed:
=SUMIF(DateList,A2,CountList)
To get the total size enter this formula in C2 and copy down as needed:
=SUMIF(DateList,A2,SizeList)
You can use this data for you chart.

Great! Only problem is that i cant figure out how to output weekly/
monthly/annually data. Instead of pr. day I would like pr. month for a
more clear visual presentation. If I use your SUMIF form I have to
copy it to a lot of cells for a presentation of (for example) november
2007. Furthermore I will have 30 days represented along the x-axis. In
this tutorial from Jon Peltier <http://www.myezurl.org/531.htm> they
also create a dynamic chart from a date range - only difference is
that the user can select a weekly/monthly/quarter-/semi/annually
reprsentation of the dates along the x-axis. If I somehow could
implement this feature it would be possible to compare month versus
month, month versus year or any other combination. Unfortunately the
ranges defined in this tutorial are quite advanced IMO, and my past
hours of Excel struggle have not come to fruition ;-)

In case you would want to to look at the progress of my log you may
find it here:
<ftp://ftp.dxp.dk/job_log.xls.zip>
user:freecall pass:dxpfree

Have a nice day my friend.

Sincerly yours,
Rasmus