#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Averageif in Excel

I need to take the average of a large data set, for example for the hour 7 of day 2, hour 8 of day 2 and so on. As you can see some hours have more data points than others. I have to do this for both value rows D and E.

Best I have come up with is:
=AVERAGEIF($C$1:$C$33,7,D2:D33)
But then I have to manually change the range since it repeats (changes day at C34) and also I have to change the day for each one. I have tried doing this manually and its progress.... I think I'll do it manually for now since its faster and I only need to do two months for my deadline (whole year) in five days but for future references someone tell me the trick.

I have a vast range, I am copying two days out of 365:

Cell# DATE Day D E
1 2 7 1.01 116.01
2 2 7 0.66 119.42
3 2 8 0.62 119.49
4 2 8 0.62 120.19
5 2 8 0.57 146.63
6 2 10 0.62 18.22
7 2 11 0.66 33.57
8 2 11 0.74 28.69
9 2 11 0.88 186.97
10 2 11 1.01 131.53
11 2 11 1.05 72.37
12 2 12 1.40 153.87
13 2 12 1.01 121.94
14 2 12 1.27 156.32
15 2 12 1.62 169.60
16 2 13 1.67 161.02
17 2 13 0.83 185.81
18 2 14 1.01 154.28
19 2 15 2.72 166.47
20 2 15 1.36 159.90
21 2 15 2.10 155.90
22 2 15 1.97 166.12
23 2 16 1.01 165.87
24 2 16 2.14 146.66
25 2 16 1.48 147.16
26 2 16 1.27 143.99
27 2 17 0.78 201.54
28 2 17 0.88 9.34
29 2 17 1.23 165.03
30 2 17 1.53 160.04
31 2 17 0.88 147.54
32 2 17 1.01 13.76
33 2 18 1.09 26.90
34 3 7 1.32 147.47
35 3 7 0.92 147.65
36 3 8 0.53 147.75
37 3 8 0.62 147.65
38 3 8 0.57 147.54
39 3 10 2.14 96.31
40 3 10 2.14 89.78
41 3 10 2.80 84.44
42 3 10 2.88 81.74
43 3 10 1.93 97.05
44 3 11 1.83 92.31
45 3 11 2.37 88.98
46 3 11 2.45 87.96
47 3 11 1.93 87.89
48 3 11 2.67 86.13
49 3 11 2.32 89.54
50 3 12 2.02 91.15
51 3 12 5.12 84.48
52 3 13 4.51 76.86
53 3 13 5.12 73.91
54 3 13 4.68 79.71
55 3 13 4.24 79.71
56 3 13 3.23 78.72
57 3 14 1.48 58.32
58 3 14 1.88 60.64
59 3 15 1.44 24.30
60 3 15 2.28 55.40
61 3 15 3.46 61.02
62 3 15 4.77 67.24
63 3 15 2.98 65.73
64 3 16 2.41 72.05
65 3 16 2.23 89.26
66 3 16 2.76 76.40
67 3 16 4.28 81.39
68 3 16 3.72 76.09
69 3 16 3.46 72.37
70 3 17 3.07 76.86
71 3 17 4.24 95.85
72 3 17 3.58 99.26
73 3 17 3.89 85.08
74 3 17 2.88 90.76

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there an AVERAGEIF function in excel? lisab Excel Discussion (Misc queries) 5 November 9th 09 07:46 PM
Help with Averageif Formula (don't think I should use Averageif) MUmfleet Excel Discussion (Misc queries) 5 April 9th 09 04:53 PM
Excel AverageIF issue... PLEASE HELP! [email protected] Excel Worksheet Functions 1 November 16th 07 07:15 PM
AVERAGEIF equivalent in excel 2000 flumpuk Excel Discussion (Misc queries) 4 October 16th 07 08:46 AM
EXCEL -- want to do have ''averageif'' - like sumif function - H. Glenda Excel Worksheet Functions 1 April 5th 05 08:58 PM


All times are GMT +1. The time now is 03:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"