Average versus average based on filter

Question

I need to use the result of a visual matrix to calculate a new column.&nbsp; &nbsp;The data below is a simplified version of the data I have.&nbsp;&nbsp;&nbsp;Table AFuel TypeGallonsGross CostDiesel Regular&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 126.76&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 417.04Diesel Premium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 305.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1,238.30Unleaded Regular&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 508.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,452.88Unleaded Premium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 653.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,148.37Diesel Regular&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 350.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,106.00Diesel Premium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 250.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 972.50Unleaded Regular&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 268.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 828.12Unleaded Premium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 186.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 717.96&nbsp;Table BFuel TypePreferred Fuel TypeDiesel RegularDiesel RegularDiesel PremiumDiesel RegularUnleaded RegularUnleaded RegularUnleaded PremiumUnleaded Regular&nbsp;First desired result is:Fuel TypeSum of GallonsSum of Gross CostAvg Cost/GalDiesel Premium5552210.8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.98Diesel Regular476.761523.0404&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.19Unleaded Premium8392866.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.42Unleaded Regular7762281&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.94&nbsp;Total2646.768881.1704&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.36&nbsp;I want it to take the average cost per gallon determined from this visual and add a column that shows what the cost would be if they had purchased the preferred fuel type.&nbsp;Final Desired Result&nbsp;Fuel TypeSum of GallonsSum of Gross CostAvg Cost/GalCost w/Pref FuelDiesel Premium5552210.8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.98&nbsp;&nbsp;&nbsp; 1,772.98Diesel Regular476.761523.0404&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.19&nbsp; &nbsp;1523.04Unleaded Premium8392866.33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.42&nbsp;&nbsp;&nbsp; 2,466.18Unleaded Regular7762281&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.94&nbsp; &nbsp;2281.00&nbsp;Total2646.768881.1704&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.36&nbsp;&nbsp;&nbsp; 8,043.21&nbsp;&nbsp;I have tried multiple ways and am not getting it to work.&nbsp; Basically need to show them what it would cost if they didn't purchase the premium fuel.

lbendlin · Answer

&nbsp;

hyeasterly · Answer

Thank you! This has gotten me very close. Will you look at my formula and tell me why I am getting inaccurate results?

If I remove the word SUM on line 4 it gets much closer... but still not correct.

I am sure it is something very simple that I am missing... but I have been looking at it all day and can't see what it is.

Your help is much appreciated!

lbendlin · Answer

did you rename the [Units]&nbsp; field to "Gallons"&nbsp; for the visual or are these different fields?

hyeasterly · Answer

Yes, I renamed it for the visual.

lbendlin · Answer

no idea. Would be best if you could provide more realistic sample data
&nbsp;

Forum Discussion

Average versus average based on filter

5 Replies

Related content

Average and Moving Average

Weekly Average

Accumulated average

moving average help

Average of Average Pattern Inaccuracies

Fuel Type	Gallons	Gross Cost
Diesel Regular	126.76	417.04
Diesel Premium	305.00	1,238.30
Unleaded Regular	508.00	1,452.88
Unleaded Premium	653.00	2,148.37
Diesel Regular	350.00	1,106.00
Diesel Premium	250.00	972.50
Unleaded Regular	268.00	828.12
Unleaded Premium	186.00	717.96

Fuel Type	Preferred Fuel Type
Diesel Regular	Diesel Regular
Diesel Premium	Diesel Regular
Unleaded Regular	Unleaded Regular
Unleaded Premium	Unleaded Regular

Fuel Type	Sum of Gallons	Sum of Gross Cost	Avg Cost/Gal
Diesel Premium	555	2210.8	3.98
Diesel Regular	476.76	1523.0404	3.19
Unleaded Premium	839	2866.33	3.42
Unleaded Regular	776	2281	2.94
Total	2646.76	8881.1704	3.36