| Home |
| Search |
| Today's Posts |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average data based on unique change in date, ignoring time
Hi,
Am Tue, 8 Oct 2013 17:37:25 +0100 schrieb rbrookov: Column 1 Column 2 Column 3 (Text) Date (in the following format) Data (floating point) a 9/19/2013 12:02:00 AM 6.58667 b 9/19/2013 12:03:00 AM 6.44361 c 9/19/2013 12:04:00 AM 6.32134 d 9/19/2013 12:05:00 AM 6.54321 e 9/20/2013 7:26:00 PM 10.11111 f 9/20/2013 7:27:00 PM 10.23345 g 9/20/2013 7:28:00 PM 10.54321 h 9/20/2013 7:29:00 PM 10.67890 i 9/21/2013 11:58:00 PM 3.12345 j 9/21/2013 11:59:00 AM 3.54321 k 9/21/2013 12:00:00 PM 4.11111 l 9/21/2013 12:01:00 PM 4.54321 The goal is to take an average of the Data in Column 3 for each instance when the Date in Column 2 changes, ignoring the time. in D1 try: =IF(COUNTIF($B$1:$B1,B1)=1,AVERAGE(OFFSET(B1,,2,CO UNTIF($B$2:$B$12,B2))),"") and copy down Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate Average of an Array of Data for numbers less than 22, while ignoring blanks | Excel Discussion (Misc queries) | |||
| removing/ignoring time in a date time field | Excel Discussion (Misc queries) | |||
| change date based on time | Excel Discussion (Misc queries) | |||
| Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
| Creating a unique ID number based on date and time | Excel Programming | |||
Threaded Mode
