Average versus average based on filter

[HYEasterly]

I need to use the result of a visual matrix to calculate a new column.   The data below is a simplified version of the data I have.  

Table A

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

Table B

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

First desired result is:

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

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.

Final Desired Result 

Fuel Type	Sum of Gallons	Sum of Gross Cost	Avg Cost/Gal	Cost w/Pref Fuel
Diesel Premium	555	2210.8	3.98	1,772.98
Diesel Regular	476.76	1523.0404	3.19	1523.04
Unleaded Premium	839	2866.33	3.42	2,466.18
Unleaded Regular	776	2281	2.94	2281.00
Total	2646.76	8881.1704	3.36	8,043.21

I have tried multiple ways and am not getting it to work.  Basically need to show them what it would cost if they didn't purchase the premium fuel.

[lbendlin]

[HYEasterly]

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]

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

[HYEasterly]

Yes, I renamed it for the visual.

[lbendlin]

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