AVERAGEIF Returning Wrong Average Value

[dkrak]

Hi all,

This is truly baffling me. I keep a spreadsheet that tracks my weight and the corresponding fluctuations over time (weekly, monthly, etc). I do this using AVERAGEIFS which, until October, has never given me trouble. You can see in the screenshot and formula below that I'm just doing an AVERAGEIFS based off a month key (concat of month and year) and my recorded weight. In October, I have weighed 184. 182.8. 184.4. 182.8. 182.8 which is an average of 183.4. You can see that excel is way off, telling me I have a month avg of 180.8 using my AVERAGEIF formula.

I'm not sure how to get the Mini-sheet to work on here so I put the file out at a Drop Box if you want to download it. It's on the 'Weight' Tab just below the pivot chart, columns AB-AT:

Copy of newest-workout-tracker.xlsx

=AVERAGEIF($AB$28:$AB$1048576,AB279,$AS$28:$AS$1048576)

Thanks!

 

[6StringJazzer]

AVERAGEIF is treating your date key as a decimal number, so 2023.1 is the same as 2023.10. You probably didn't notice that the averages for 2023.1 are also now wrong.

The easiest way to fix this is to use a separator symbol in your date key that cannot be interpreted as part of a number.

=CONCAT(AD28,"-",AE28)

I would prefer to use an actual date, but it's not necessary

=DATE(AD28, AE28, 1)

Custom format to YYYY-MM

 

[dkrak]

AVERAGEIF is treating your date key as a decimal number, so 2023.1 is the same as 2023.10. You probably didn't notice that the averages for 2023.1 are also now wrong.

The easiest way to fix this is to use a separator symbol in your date key that cannot be interpreted as part of a number.

=CONCAT(AD28,"-",AE28)

I would prefer to use an actual date, but it's not necessary

=DATE(AD28, AE28, 1)

Custom format to YYYY-MM

That did it, thanks Jeff!

Just out of curiosity, why wouldn't the formula work even with the Month Key as a decimal number so long as the number is the same every time? Maybe as I dragged the 2023.10 down for each new entry it was adding figures after the 0 that I just wasn't seeing? therefore manipulating the number?

 

[dkrak]

That did it, thanks Jeff!

Just out of curiosity, why wouldn't the formula work even with the Month Key as a decimal number so long as the number is the same every time? Maybe as I dragged the 2023.10 down for each new entry it was adding figures after the 0 that I just wasn't seeing? therefore manipulating the number?

Ignore this, I understand now that it was blending 2023.1 with 2023.10. Thanks again!

 

[6StringJazzer]

Yes, exactly. If you go back to the 2023.1 numbers, you'll see the same average as for 2023.10.

I can think of other ways to fix this as well. I gave you the easiest one, but just for discussion purposes:

Use two digits for the month so 10 will be different than 01. Value is Text

=CONCAT(AD28,".",TEXT(AE28,"00"))

2023.01
2023.10

Build a number instead of text. It will have no delimiter. Value is Integer.

=AD28*100+AE28

202301
202310

As I mentioned before, use a date, and custom format as desired. Value is Date

=DATE(AD28, AE28, 1)

Format "yyyy.mm"
2023.01
2023.10
Format "yyyy mmm"
2023 Jan
2023 Oct