Complicated averaging-- averaging data that matches certain intervals

[atung]

Greetings ExcelForum community,

I have tens of thousands of datapoints that I have to average and doing it by hand is out of the question.

Basically, I have a long set of "distances" and data taken from each of those distances. I need to average the data within a certain distance interval. For instance, I need to average the temperature taken between 1ft and 9ft. I've made a dummy-sheet so feel free to check that out. Any help would be so greatly appreciated! I've been working on this for weeks so I believe it's time I reach out for help. Please let me know if there are any clarifications you need.

Thanks!

[JieJenn]

In F2, enter =AVERAGE(IF($B$2:$B$10>=D2,IF($B$2:$B$10<E2,$A$2:$A$10,""))) with Ctrl + Shift + Enter keys together since it is an array formula

[martindwilson]

for from 1 to less than 10 ft
=AVERAGEIFS(B2:B10,B2:B10,">="&D2,B2:B10,"<"&E2)

[mrice]

Try this user defined function.

Function AverageSpecial(CriteriaMax As Single, CriteriaMin As Single, CriteriaRange As Range, ValueRange As Range) As Single
Dim RunningTotal As Single
Dim RunningCount As Long
Dim N As Long

For N = 1 To CriteriaRange.Count
    If CriteriaRange(N) >= CriteriaMin And CriteriaRange(N) < CriteriaMax Then
        RunningTotal = RunningTotal + ValueRange(N)
        RunningCount = RunningCount + 1
    End If
Next N
If RunningCount > 0 Then
    AverageSpecial = RunningTotal / RunningCount
End If
End Function

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a F2, enter =AverageSpecial(E2,D2,B2:B10,A2:A10) and copy down

Remember to save the workbook as a macro enabled workbook .xlsm

[atung]

JieJenn and martindwilson,

Those are great! They worked. However, they don't come out with the exact averages I'm looking for. That is, I'm looking for exact number "4" as the average. Instead, for these formulas I'm getting 4 plus some fraction. Is there a way to change these to be more accurate?

And mrice, I really appreciate the thoroughness of your suggestion! However I'm looking for a simple function that I can input and that everyone in my office will be able to backtrack (although, believe me, if this were a better solution I wish I could take it)

[atung]

JieJenn and martindwilson,

Those are great! They worked. However, they don't come out with the exact averages I'm looking for. That is, I'm looking for exact number "4" as the average. Instead, for these formulas I'm getting 4 plus some fraction. Is there a way to change these to be more accurate?

And mrice, I really appreciate the thoroughness of your suggestion! However I'm looking for a simple function that I can input and that everyone in my office will be able to backtrack (although, believe me, if this were a better solution I wish I could take it)

*exact number 4 as the average to distance between 1 and 10

[shg]

If F2 and copy down,

=AVERAGEIFS($A$2:$A$10, $B$2:$B$10, ">=" & D2, $B$2:$B$10, "<" & E2)

[atung]

If F2 and copy down,

=AVERAGEIFS($A$2:$A$10, $B$2:$B$10, ">=" & D2, $B$2:$B$10, "<" & E2)

Shg, that might've done the trick! I'll try it on my dataset now and will let you know how it goes!

[shg]

It's martin's formula; he just misread which column was to be averaged.

[atung]

It's martin's formula; he just misread which column was to be averaged.

Thanks Martin and shg for the correction

I have another question I was hoping you could answer--- do you know how I can track to see which raw data values my formula computed into the average? I have large datasets with numbers all over the place. I need to be able to compute the data by hand to prove that the formula works but it isn't practical. Any ideas?

[mrice]

You might try several of the solutions and then use the EXACT function to see if they agree if you have to prove that the formulae are working to someone else.

[shg]

You're in for a long, hard slog if you feel the need to verify that Excel formulas work as advertised.

[mrice]

There is a user defined function solution to this second challenge.

Function AverageNumbers(CriteriaMax As Single, CriteriaMin As Single, CriteriaRange As Range, ValueRange As Range) As String

Dim NumberString As String
Dim N As Long

For N = 1 To CriteriaRange.Count
    If CriteriaRange(N) >= CriteriaMin And CriteriaRange(N) < CriteriaMax Then
        NumberString = NumberString & "," & ValueRange(N)
    End If
Next N
If Len(NumberString) > 0 Then
    AverageNumbers = Right(NumberString, Len(NumberString) - 1)
End If
End Function