Using IF and/or LOOKUP to find an average of values based on range of miles

[d_k]

Hi, not really sure how to go about this.

I have a data set for orders that provide the number of miles traveled, freight charge, and $/loaded mile. I'm trying to gather information to find an average freight charge in $/LM for 25 mile ranges.

Below I've included a screenshot of what my data looks like. I have 6200 lines in this data set.

I've researched the LOOKUP function, and found some good info on compiling info (i.e. what the freight charge is on order #_____) but I couldnt find anything on using the LOOKUP function to compile an average. I tried to do this with a pivot table, and also couldn't figure it out so it would give me the correct info. I could do mile brackets, but it wouldn't calculate correctly even though I indicated I wanted the average of $/LM as the value. I checked this with some of the last orders - it would give me </=$1 for some of those last orders, which made no sense to me.

I've put a snippet of the format I'm trying to get for this freight charge summary for reference.

I hope someone has some insight here, I'm lost

 

[C Moore]

I can't tell what you are asking since all your distances are so much greater than your desired ranges.

 

[d_k]

I can't tell what you are asking since all your distances are so much greater than your desired ranges.

I was giving a sample since the actual file is too large, sorry about not being more clear!

I have 6200 orders ranging from 0-2912 miles. I want to group them into 25 mile bracket segments, and find the average cost per mile for each bracket.

 

[C Moore]

Gotcha. You can go weighted average of Freight/Billed Mileage or a straight average comparing $/LM. If you can format your data table as a table it makes your formulas easier to read. Make your brackets table show only the upper limit like so

Bracket Upper Limit	Average $/LM
25​	4.103836824​
50​	6.157044247​
75​	#DIV/0!​
100​	4.224971514​
125​	4.710755229​
150​	4.653443172​
175​	4.562255118​
200​	4.1768354​
225​	4.039456716​
250​	4.996125928​

Straight average is easier and looks like:
=AVERAGEIFS(Table_Inv[$/LM],Table_Inv[billed mileage],"<="&S3,Table_Inv[billed mileage],">"&S3-25)
where S3 was where I had 25.
Weighted average:
=SUMPRODUCT((Table_Inv[billed mileage]<=S3)+0,(Table_Inv[billed mileage]>S3-25)+0,Table_Inv[billed mileage],Table_Inv[Freight $s])/SUMPRODUCT((Table_Inv[billed mileage]<=S3)+0,(Table_Inv[billed mileage]>S3-25)+0,Table_Inv[billed mileage])

You'll have to decide how to handle ranges that aren't included in the dataset (returns #DIV/0 error)

 

[d_k]

xx

 

[d_k]

Gotcha. You can go weighted average of Freight/Billed Mileage or a straight average comparing $/LM. If you can format your data table as a table it makes your formulas easier to read. Make your brackets table show only the upper limit like so

Bracket Upper Limit	Average $/LM
25​	4.103836824​
50​	6.157044247​
75​	#DIV/0!​
100​	4.224971514​
125​	4.710755229​
150​	4.653443172​
175​	4.562255118​
200​	4.1768354​
225​	4.039456716​
250​	4.996125928​

Straight average is easier and looks like:
=AVERAGEIFS(Table_Inv[$/LM],Table_Inv[billed mileage],"<="&S3,Table_Inv[billed mileage],">"&S3-25)
where S3 was where I had 25.
Weighted average:
=SUMPRODUCT((Table_Inv[billed mileage]<=S3)+0,(Table_Inv[billed mileage]>S3-25)+0,Table_Inv[billed mileage],Table_Inv[Freight $s])/SUMPRODUCT((Table_Inv[billed mileage]<=S3)+0,(Table_Inv[billed mileage]>S3-25)+0,Table_Inv[billed mileage])

You'll have to decide how to handle ranges that aren't included in the dataset (returns #DIV/0 error)

Hi,

I couldn't get this to work. My sheet is called "Grid Data", below is the formula I tried to enter but it came up as an invalid formula. Any advice here?

=AVERAGEIFS("Grid Data"!E1:I6200[$/LM],"Grid Data"!E1:I6200[Bill Miles],"<="&A2,"Grid Data"!E1:I6200,">"&A2-25)

 

[C Moore]

If you don't format as table the [ ] portion shouldn't be there.
=AVERAGEIFS("Grid Data"!$I$1:$I$6200,"Grid Data"!$G$1:$G$6200,"<="&A2,"Grid Data"!$G$1:$G$6200,">"&A2-25)