Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help
Hi
I have a date wise data in sheet 1 for a month. I need average of the data in sheet 2 for date wise. Can anyone plz help me with the formula? Thanks.. Below is the example of data which i have in sheet 1. A B 1 Date Response 2 4-Jul-11 0:20:00 3 4-Jul-11 0:41:00 4 5-Jul-11 7:14:00 5 5-Jul-11 1:18:00 6 5-Jul-11 0:23:00 7 5-Jul-11 0:35:00 8 6-Jul-11 0:17:00 9 7-Jul-11 2:45:00 I need it in the sheet 2 as follows A B 1 Date Response % 2 04-Jul-11 0:30:30 3 05-Jul-11 2:22:30 3 06-Jul-11 0:17:00 Sivaji |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help
One way is to put this in Sheet2!B2 and copy downward:
=IF(COUNTIF(Sheet1!A:A,A2)=0,"", SUMIF(Sheet1!A:A,A2,Sheet1!B:B)/COUNTIF(Sheet1!A:A,A2)) To see only the rows having values in column B, you could use Data Filter AutoFilter and choose "NonBlanks." |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help
Hi Siva,
I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. https://docs.google.com/leaf?id=0B2T...lZDZi&hl=en_US Hope that helps. --- Sixthsense On Jul 10, 3:44*pm, siva wrote: Hi I have a date wise data in sheet 1 for a month. I need average of the data in sheet 2 for date wise. Can anyone plz help me with the formula? Thanks.. Below is the example of data which i have in sheet 1. * * * * * * *A * * * * * * * * * * * * * *B 1 * * * * *Date * * *Response 2 * * * *4-Jul-11 * * * * * * 0:20:00 3 * * * *4-Jul-11 * * * * * * 0:41:00 4 * * * *5-Jul-11 * * * * * * 7:14:00 5 * * * *5-Jul-11 * * * * * * 1:18:00 6 * * * *5-Jul-11 * * * * * * 0:23:00 7 * * * *5-Jul-11 * * * * * * 0:35:00 8 * * * *6-Jul-11 * * * * * * 0:17:00 9 * * * *7-Jul-11 * * * * * * 2:45:00 I need it in the sheet 2 as follows * * * * * * A * * * * * * * * * * * * * *B 1 * * * *Date * * * * * * * * * * *Response % 2 * * *04-Jul-11 * * * * * * * * *0:30:30 3 * * *05-Jul-11 * * * * * * * * *2:22:30 3 * * *06-Jul-11 * * * * * * * * *0:17:00 Sivaji |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help
On Jul 11, 1:10*am, Sixthsense wrote:
Hi Siva, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. https://docs.google.com/leaf?id=0B2T...YtMGI3Ny00OGFk.... Hope that helps. --- Sixthsense On Jul 10, 3:44*pm, siva wrote: Hi I have a date wise data in sheet 1 for a month. I need average of the data in sheet 2 for date wise. Can anyone plz help me with the formula? Thanks.. Below is the example of data which i have in sheet 1. * * * * * * *A * * * * * * * * * * * * * *B 1 * * * * *Date * * *Response 2 * * * *4-Jul-11 * * * * * * 0:20:00 3 * * * *4-Jul-11 * * * * * * 0:41:00 4 * * * *5-Jul-11 * * * * * * 7:14:00 5 * * * *5-Jul-11 * * * * * * 1:18:00 6 * * * *5-Jul-11 * * * * * * 0:23:00 7 * * * *5-Jul-11 * * * * * * 0:35:00 8 * * * *6-Jul-11 * * * * * * 0:17:00 9 * * * *7-Jul-11 * * * * * * 2:45:00 I need it in the sheet 2 as follows * * * * * * A * * * * * * * * * * * * * *B 1 * * * *Date * * * * * * * * * * *Response % 2 * * *04-Jul-11 * * * * * * * * *0:30:30 3 * * *05-Jul-11 * * * * * * * * *2:22:30 3 * * *06-Jul-11 * * * * * * * * *0:17:00 Sivaji This is an ARRAY formula that must be entered using ctrl+shift+enter Do not use entire columns (named range if desired). Place in b2 and copy down =AVERAGE(IF(('Your Data'!$A$2:$A$99=A2),'Your Data'!$B$2:$B$99)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|