# Need help

S

#### siva

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

Z

#### zvkmpw

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."

D

#### Don Guillett

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=0B2TMxNyEPQ2rZmE4MGRlYmYtMGI3Ny00OGFk....

Hope that helps.

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))

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.