Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default EXCEL averageif with different #of values to average per calculation,depending on two criteria

I need to take the average of a large data set, for example for the hour(C) 7 of day(B) 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.

=AVERAGEIF($C$1:$C$33,7,D2:D33)
But then I have to manually change the range since it repeats (changes day at ROW34).
I Figured that by making a new column where the hour (C) exists regardless of whether or not it is in the data then I can skip on step:

Make column K1 to K24, then in any empty column:
=AVERAGEIF($C$1:$C$33,$K1,D$2:D$33)

Then by going down with the expansion cross I will get all the D averages for exactly one day, going sideways will give me the E averages as well, where no data exists I get 0#DVID! which is okay, Still I have to change the ranges and repeat once the day moves on, this works for the mean time as I only have to do one month for now, but eventually I need to do all 365 days.

If you could let me know how to include the B column so this is quicker I would appreciate it very much.

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

A= Cell number

A B C D E
01 2 07 1.01 116.01
02 2 07 0.66 119.42
03 2 08 0.62 119.49
04 2 08 0.62 120.19
05 2 08 0.57 146.63
06 2 10 0.62 18.22
07 2 11 0.66 33.57
08 2 11 0.74 28.69
09 2 11 0.88 186.97
10 2 11 1.01 131.53
11 2 11 1.05 72.37
12 2 12 1.4 153.87
13 2 12 1.01 121.94
14 2 12 1.27 156.32
15 2 12 1.62 169.6
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.9
21 2 15 2.1 155.9
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.9
34 3 07 1.32 147.47
35 3 07 0.92 147.65
36 3 08 0.53 147.75
37 3 08 0.62 147.65
38 3 08 0.57 147.54
39 3 10 2.14 96.31
40 3 10 2.14 89.78
41 3 10 2.8 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.3
60 3 15 2.28 55.4
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.4
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
Need help with Excel Formula for calculating sum of products depending on criteria Archi G Excel Discussion (Misc queries) 1 March 12th 12 08:11 PM
averageif with 3 criteria Rene Excel Discussion (Misc queries) 1 May 28th 10 02:49 AM
averageif with 2 other criteria v1rt8 Excel Discussion (Misc queries) 10 November 13th 09 09:00 PM
AverageIF with cell refenrence as criteria is not working Bart Geerling Excel Discussion (Misc queries) 4 November 22nd 07 08:36 PM
calculation a average dependated of values in seperate column vinnie123 Excel Worksheet Functions 1 February 16th 06 01:55 AM


All times are GMT +1. The time now is 11:10 PM.

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

About Us

"It's about Microsoft Excel"