AverageIfs function only returns first matching value

L

LambChoptheKid

New Member
Joined
Dec 10, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
I have a problem with an excel averageifs formula in that it only returns the first matching value, and does not average all values which correspond to the right criteria.

The goal is to average every value which corresponds to a particular hour of the day. I've attached a screenshot below.

For example, for the hour of 1:00 AM I would like to take an average of every value in row C which corresponds to a value of 1:00 AM in row B.

The formula in in G2 is as follows: =AVERAGEIFS(C:C, B:B, F2)

I have had success with using this formula before for similar projects and I cannot figure out why this is not working.

Any assistance will be appreciated.

Thank you
 

Attachments

  • AverageIfs screenshot.png
    AverageIfs screenshot.png
    87.3 KB · Views: 16

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Cannot manipulate data in a picture. Please reload your sample using XL2BB so that we don't have to retype your data for testing.
 
Upvote 0
alansidman said:
Cannot manipulate data in a picture. Please reload your sample using XL2BB so that we don't have to retype your data for testing.
Click to expand...
Unfortunately I am not able to install addons for my work situation. Is there any other way to work around this limitation?
 
Upvote 0
you can also just try to post the data as a table. But it will lose all formulas. If you have formulas you can change the view of the worksheet, Press CNTL-TILDE to show formulas, then copy and paste that into table (being sure the two table are of the same column and row dimensions.
 
Upvote 0
you are using averageifs instead of averageif.
You can use either but the arguments are in different order.

mr excel questions 22.xlsm
ABCDEFGH
1HourAHourBValuesAverageAverageifAverageifs
21:00 AM1:00 AM279.09261:00 AM355.9034058355.903406
32:00 AM2:00 AM258.1162:00 AM268.9476397268.94764
43:00 AM3:00 AM870.23243:00 AM429.7751646429.775165
54:00 AM4:00 AM968.44744:00 AM675.2147484675.214748
65:00 AM5:00 AM0.2540735:00 AM423.8519763423.851976
76:00 AM6:00 AM284.35656:00 AM660.9765416660.976542
87:00 AM7:00 AM747.29917:00 AM348.3112045348.311204
98:00 AM8:00 AM613.77198:00 AM549.7401055549.740106
109:00 AM9:00 AM121.52249:00 AM507.6484373507.648437
1110:00 AM10:00 AM284.982310:00 AM442.8300841442.830084
1211:00 AM11:00 AM814.318811:00 AM462.897234462.897234
1312:00 PM12:00 PM49.6621712:00 PM433.6531808433.653181
141:00 PM1:00 PM905.83251:00 PM599.7650409599.765041
152:00 PM2:00 PM260.90292:00 PM624.9440861624.944086
163:00 PM3:00 PM192.25323:00 PM466.112036466.112036
174:00 PM4:00 PM464.61884:00 PM436.6644691436.664469
185:00 PM5:00 PM133.1865:00 PM551.6180107551.618011
196:00 PM6:00 PM977.93786:00 PM630.7049927630.704993
207:00 PM7:00 PM957.71187:00 PM394.0408882394.040888
218:00 PM8:00 PM674.51498:00 PM604.8554001604.8554
229:00 PM9:00 PM920.37219:00 PM618.0731659618.073166
2310:00 PM10:00 PM980.173610:00 PM711.6437976711.643798
2411:00 PM11:00 PM277.621111:00 PM508.9788025508.978803
2512:00 AM12:00 AM784.080912:00 AM637.4707285637.470728
261:00 AM1:00 AM310.1373
272:00 AM2:00 AM201.037
283:00 AM3:00 AM48.83575
294:00 AM4:00 AM930.4049
305:00 AM5:00 AM379.8427
316:00 AM6:00 AM541.725
327:00 AM7:00 AM654.5368
338:00 AM8:00 AM51.09003
349:00 AM9:00 AM234.0867
3510:00 AM10:00 AM866.6321
3611:00 AM11:00 AM209.5315
3712:00 PM12:00 PM372.8764
381:00 PM1:00 PM264.6538
392:00 PM2:00 PM785.6746
403:00 PM3:00 PM50.62665
414:00 PM4:00 PM687.5147
425:00 PM5:00 PM649.5909
436:00 PM6:00 PM853.2045
447:00 PM7:00 PM407.0459
458:00 PM8:00 PM888.1376
469:00 PM9:00 PM216.9082
4710:00 PM10:00 PM244.3794
4811:00 PM11:00 PM980.9825
4912:00 AM12:00 AM739.7633
501:00 AM1:00 AM471.8878
512:00 AM2:00 AM72.21866
523:00 AM3:00 AM79.23254
534:00 AM4:00 AM715.2091
545:00 AM5:00 AM651.771
556:00 AM6:00 AM994.5026
567:00 AM7:00 AM134.5485
578:00 AM8:00 AM463.9855
589:00 AM9:00 AM690.6337
5910:00 AM10:00 AM851.6256
6011:00 AM11:00 AM246.5209
6112:00 PM12:00 PM569.1791
621:00 PM1:00 PM615.991
632:00 PM2:00 PM551.0075
643:00 PM3:00 PM740.3434
654:00 PM4:00 PM327.3201
665:00 PM5:00 PM803.7588
676:00 PM6:00 PM384.9266
687:00 PM7:00 PM166.6868
698:00 PM8:00 PM598.5542
709:00 PM9:00 PM342.5343
7110:00 PM10:00 PM955.3639
7211:00 PM11:00 PM34.48633
7312:00 AM12:00 AM263.7461
741:00 AM1:00 AM622.0685
752:00 AM2:00 AM391.427
763:00 AM3:00 AM545.391
774:00 AM4:00 AM192.3492
785:00 AM5:00 AM730.2538
796:00 AM6:00 AM883.394
807:00 AM7:00 AM114.0367
818:00 AM8:00 AM878.544
829:00 AM9:00 AM693.6208
8310:00 AM10:00 AM177.705
8411:00 AM11:00 AM920.0985
8512:00 PM12:00 PM186.9413
861:00 PM1:00 PM710.3384
872:00 PM2:00 PM673.3087
883:00 PM3:00 PM939.4098
894:00 PM4:00 PM696.7881
905:00 PM5:00 PM523.8707
916:00 PM6:00 PM924.9662
927:00 PM7:00 PM46.50205
938:00 PM8:00 PM566.5247
949:00 PM9:00 PM656.0327
9510:00 PM10:00 PM564.6423
9611:00 PM11:00 PM742.8253
9712:00 AM12:00 AM762.2926
981:00 AM1:00 AM96.33081
992:00 AM2:00 AM421.9395
1003:00 AM3:00 AM605.1841
1014:00 AM4:00 AM569.6632
1025:00 AM5:00 AM357.1383
1036:00 AM6:00 AM600.9047
1047:00 AM7:00 AM91.13495
1058:00 AM8:00 AM741.3091
1069:00 AM9:00 AM798.3786
10710:00 AM10:00 AM33.20544
10811:00 AM11:00 AM124.0164
10912:00 PM12:00 PM989.6069
1101:00 PM1:00 PM502.0095
1112:00 PM2:00 PM853.8268
1123:00 PM3:00 PM407.9271
1134:00 PM4:00 PM7.080638
1145:00 PM5:00 PM647.6837
1156:00 PM6:00 PM12.48982
1167:00 PM7:00 PM392.2579
1178:00 PM8:00 PM296.5456
1189:00 PM9:00 PM954.5186
11910:00 PM10:00 PM813.6597
Lambchopthekid
Cell Formulas
RangeFormula
G2:G25G2=AVERAGEIF($B$2:$B$119,F2,$C$2:$C$119)
H2:H25H2=AVERAGEIFS($C$2:$C$119,$B$2:$B$119,F2)
 
Upvote 0
Solution
awoohaw said:
you are using averageifs instead of averageif.
You can use either but the arguments are in different order.

mr excel questions 22.xlsm
ABCDEFGH
1HourAHourBValuesAverageAverageifAverageifs
21:00 AM1:00 AM279.09261:00 AM355.9034058355.903406
32:00 AM2:00 AM258.1162:00 AM268.9476397268.94764
43:00 AM3:00 AM870.23243:00 AM429.7751646429.775165
54:00 AM4:00 AM968.44744:00 AM675.2147484675.214748
65:00 AM5:00 AM0.2540735:00 AM423.8519763423.851976
76:00 AM6:00 AM284.35656:00 AM660.9765416660.976542
87:00 AM7:00 AM747.29917:00 AM348.3112045348.311204
98:00 AM8:00 AM613.77198:00 AM549.7401055549.740106
109:00 AM9:00 AM121.52249:00 AM507.6484373507.648437
1110:00 AM10:00 AM284.982310:00 AM442.8300841442.830084
1211:00 AM11:00 AM814.318811:00 AM462.897234462.897234
1312:00 PM12:00 PM49.6621712:00 PM433.6531808433.653181
141:00 PM1:00 PM905.83251:00 PM599.7650409599.765041
152:00 PM2:00 PM260.90292:00 PM624.9440861624.944086
163:00 PM3:00 PM192.25323:00 PM466.112036466.112036
174:00 PM4:00 PM464.61884:00 PM436.6644691436.664469
185:00 PM5:00 PM133.1865:00 PM551.6180107551.618011
196:00 PM6:00 PM977.93786:00 PM630.7049927630.704993
207:00 PM7:00 PM957.71187:00 PM394.0408882394.040888
218:00 PM8:00 PM674.51498:00 PM604.8554001604.8554
229:00 PM9:00 PM920.37219:00 PM618.0731659618.073166
2310:00 PM10:00 PM980.173610:00 PM711.6437976711.643798
2411:00 PM11:00 PM277.621111:00 PM508.9788025508.978803
2512:00 AM12:00 AM784.080912:00 AM637.4707285637.470728
261:00 AM1:00 AM310.1373
272:00 AM2:00 AM201.037
283:00 AM3:00 AM48.83575
294:00 AM4:00 AM930.4049
305:00 AM5:00 AM379.8427
316:00 AM6:00 AM541.725
327:00 AM7:00 AM654.5368
338:00 AM8:00 AM51.09003
349:00 AM9:00 AM234.0867
3510:00 AM10:00 AM866.6321
3611:00 AM11:00 AM209.5315
3712:00 PM12:00 PM372.8764
381:00 PM1:00 PM264.6538
392:00 PM2:00 PM785.6746
403:00 PM3:00 PM50.62665
414:00 PM4:00 PM687.5147
425:00 PM5:00 PM649.5909
436:00 PM6:00 PM853.2045
447:00 PM7:00 PM407.0459
458:00 PM8:00 PM888.1376
469:00 PM9:00 PM216.9082
4710:00 PM10:00 PM244.3794
4811:00 PM11:00 PM980.9825
4912:00 AM12:00 AM739.7633
501:00 AM1:00 AM471.8878
512:00 AM2:00 AM72.21866
523:00 AM3:00 AM79.23254
534:00 AM4:00 AM715.2091
545:00 AM5:00 AM651.771
556:00 AM6:00 AM994.5026
567:00 AM7:00 AM134.5485
578:00 AM8:00 AM463.9855
589:00 AM9:00 AM690.6337
5910:00 AM10:00 AM851.6256
6011:00 AM11:00 AM246.5209
6112:00 PM12:00 PM569.1791
621:00 PM1:00 PM615.991
632:00 PM2:00 PM551.0075
643:00 PM3:00 PM740.3434
654:00 PM4:00 PM327.3201
665:00 PM5:00 PM803.7588
676:00 PM6:00 PM384.9266
687:00 PM7:00 PM166.6868
698:00 PM8:00 PM598.5542
709:00 PM9:00 PM342.5343
7110:00 PM10:00 PM955.3639
7211:00 PM11:00 PM34.48633
7312:00 AM12:00 AM263.7461
741:00 AM1:00 AM622.0685
752:00 AM2:00 AM391.427
763:00 AM3:00 AM545.391
774:00 AM4:00 AM192.3492
785:00 AM5:00 AM730.2538
796:00 AM6:00 AM883.394
807:00 AM7:00 AM114.0367
818:00 AM8:00 AM878.544
829:00 AM9:00 AM693.6208
8310:00 AM10:00 AM177.705
8411:00 AM11:00 AM920.0985
8512:00 PM12:00 PM186.9413
861:00 PM1:00 PM710.3384
872:00 PM2:00 PM673.3087
883:00 PM3:00 PM939.4098
894:00 PM4:00 PM696.7881
905:00 PM5:00 PM523.8707
916:00 PM6:00 PM924.9662
927:00 PM7:00 PM46.50205
938:00 PM8:00 PM566.5247
949:00 PM9:00 PM656.0327
9510:00 PM10:00 PM564.6423
9611:00 PM11:00 PM742.8253
9712:00 AM12:00 AM762.2926
981:00 AM1:00 AM96.33081
992:00 AM2:00 AM421.9395
1003:00 AM3:00 AM605.1841
1014:00 AM4:00 AM569.6632
1025:00 AM5:00 AM357.1383
1036:00 AM6:00 AM600.9047
1047:00 AM7:00 AM91.13495
1058:00 AM8:00 AM741.3091
1069:00 AM9:00 AM798.3786
10710:00 AM10:00 AM33.20544
10811:00 AM11:00 AM124.0164
10912:00 PM12:00 PM989.6069
1101:00 PM1:00 PM502.0095
1112:00 PM2:00 PM853.8268
1123:00 PM3:00 PM407.9271
1134:00 PM4:00 PM7.080638
1145:00 PM5:00 PM647.6837
1156:00 PM6:00 PM12.48982
1167:00 PM7:00 PM392.2579
1178:00 PM8:00 PM296.5456
1189:00 PM9:00 PM954.5186
11910:00 PM10:00 PM813.6597
Lambchopthekid
Cell Formulas
RangeFormula
G2:G25G2=AVERAGEIF($B$2:$B$119,F2,$C$2:$C$119)
H2:H25H2=AVERAGEIFS($C$2:$C$119,$B$2:$B$119,F2)
Click to expand...
Thank you for the help. I've used both formulas that you suggested and the outputted value is still only the first matching value. I've used the AVERAGEIF function in row G, and AVERAGEIFS function in row H.

Must be something funny on my end. Unfortunately I'm not able to utilize any extra addons or third-party services that are not already installed so the best I can do is upload a PNG.

I've used these functions before for essentially the same purpose with no issue at all so I wonder why all of a sudden it has problems.

Appreciate the time, thanks!
 

Attachments

  • Excel screenshot.png
    Excel screenshot.png
    72 KB · Views: 4
Upvote 0
maybe your time values are off by a fraction of a second. Can you try some comparisons to see if the hours in the lower half of your data match the lookup value?

and again, make sure you have the arguments in the correct order. You had them wrong in your first post.
 
Upvote 0
awoohaw said:
maybe your time values are off by a fraction of a second. Can you try some comparisons to see if the hours in the lower half of your data match the lookup value?

and again, make sure you have the arguments in the correct order. You had them wrong in your first post.
Click to expand...
That was the problem. Just looked into the time values in the lower portions of the data and found that they were stored as dates.

It's always the small errors lol.

Thank you!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
AverageIfs with range criterion... can't get the right answer?
Replies
9
Views
415
Fluff
Fluff
D
  • Solved
AVERAGEIF Returning Wrong Average Value
Replies
4
Views
461
6StringJazzer
6StringJazzer
F
  • Question
AverageIF that contain a text for visible/filtered cells only
2
Replies
16
Views
584
Peter_SSs
Peter_SSs
P
  • Question
Return a value from a table that is in the top n percent of a person's trial scores
2
Replies
13
Views
420
Fluff
Fluff
L
  • Solved
Average row in array based on selected hour
Replies
3
Views
184
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,102
Messages
6,123,097
Members
449,096
Latest member
provoking

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top