Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help - please !
Hi and Happy New Year to all
I need a formula (and don't know where to start) for the following In a worksheet I have a list of dates in column A and times in column B, I want a formula to count each time a certain date and time before 12noon appear in columns A and B, and likewise the amount of times a certain date and time appear after 12noon. eg, basic worksheet A B 1Jan 11:23 1Jan 13:34 2Jan 09:10 2Jan 11:34 2Jan 15:43 so the result for 01Jan before 1200 would be 1, the result for 01Jan after 1200 would also be 1, the result for 02Jan before 1200 would be 2, and so on. I think think this could be solved using 'arrays' in the =SUMPRODUCT formula but I don't know how to write the formula Appreciate any help Kind regards Anthony |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help - please !
Depending on how many dates you are dealing with, you may want to utilize a
Pivot table. Before doing so add a formula to each row that returns "Before Noon" and "After Noon", then use that calculated column as the Pivot Table's column headers. Put the dates in the row headers area, and then use a count of the dates for the data values "Anthony" wrote: Hi and Happy New Year to all I need a formula (and don't know where to start) for the following In a worksheet I have a list of dates in column A and times in column B, I want a formula to count each time a certain date and time before 12noon appear in columns A and B, and likewise the amount of times a certain date and time appear after 12noon. eg, basic worksheet A B 1Jan 11:23 1Jan 13:34 2Jan 09:10 2Jan 11:34 2Jan 15:43 so the result for 01Jan before 1200 would be 1, the result for 01Jan after 1200 would also be 1, the result for 02Jan before 1200 would be 2, and so on. I think think this could be solved using 'arrays' in the =SUMPRODUCT formula but I don't know how to write the formula Appreciate any help Kind regards Anthony |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help - please !
=SUMPRODUCT(--($A$1:$A$100=--"2006-01-01"),--($B$1:$B$100<=--"12:00"))
and =SUMPRODUCT(--($A$1:$A$100=--"2006-01-01"),--($B$1:$B$100--"12:00")) but better to put the date and time in a cell and test against that =SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100<=C2)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi and Happy New Year to all I need a formula (and don't know where to start) for the following In a worksheet I have a list of dates in column A and times in column B, I want a formula to count each time a certain date and time before 12noon appear in columns A and B, and likewise the amount of times a certain date and time appear after 12noon. eg, basic worksheet A B 1Jan 11:23 1Jan 13:34 2Jan 09:10 2Jan 11:34 2Jan 15:43 so the result for 01Jan before 1200 would be 1, the result for 01Jan after 1200 would also be 1, the result for 02Jan before 1200 would be 2, and so on. I think think this could be solved using 'arrays' in the =SUMPRODUCT formula but I don't know how to write the formula Appreciate any help Kind regards Anthony |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help - please !
On Mon, 2 Jan 2006 08:13:02 -0800, "Anthony"
wrote: Hi and Happy New Year to all I need a formula (and don't know where to start) for the following In a worksheet I have a list of dates in column A and times in column B, I want a formula to count each time a certain date and time before 12noon appear in columns A and B, and likewise the amount of times a certain date and time appear after 12noon. eg, basic worksheet A B 1Jan 11:23 1Jan 13:34 2Jan 09:10 2Jan 11:34 2Jan 15:43 so the result for 01Jan before 1200 would be 1, the result for 01Jan after 1200 would also be 1, the result for 02Jan before 1200 would be 2, and so on. I think think this could be solved using 'arrays' in the =SUMPRODUCT formula but I don't know how to write the formula Appreciate any help Kind regards Anthony Given your data in A1:B5 With the date of interest in column D: D1: 1 Jan 2006 Before noon E1: =SUMPRODUCT(($A$1:$A$5=$D1)*(MOD($B$1:$B$5,1)<0.5) ) After noon F1: =SUMPRODUCT(($A$1:$A$5=$D1)*(MOD($B$1:$B$5,1)=0.5 )) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |