average with conditions

[still learning]

Hi

I’m trying to have a running average of the MPG but I don’t want to include any fill up that is 10 gallons or less

A:A is the date of the fill up
B:B is the amount of fuel in gallons used for the fill up
D:D is the millage driven for the fill up
F:F is the miles per galleon (MPG) for the fill up
G:G is the running average of MPG use since the beginning where i want to add the formula i'm having truoble with

It starts on A78 and right now it ends on A93 I don’t know what date it will end. I have the formula go down to A150 which I can extend it later
In F78, I have this formula that gives me MPG for that fill up and it’s copied down to F150

=IF(A78="","",+D78/B78)

In G78, I have this formula that gives me the running millage since the beginning and it runs down to G150

=IF(A78="","",AVERAGE($F$78:F78))

I’m trying to change it so if the amount of gallons used for the fill up (B:B) is less than 10.0, it won’t be used the average
I tried this but it puts a 0 in f and includes it in the average

=IF(AND(A78<>"",B78>10),AVERAGE($F$78:F78),””)

Or.
I need help with writing an if statement that will give me a running total but keep the cell blank if A is blank and not include a cell that's results is based on another cell that is equal or lower than 10




mike

 

[Asbestos_Jen]

Does this do what you want?

2024-09-25.xlsx
	A	B	C	D	E	F	G
1	Date	Fuel (Gal)		Miles/fill		MPG	Running Avg MPG
78	27-Jun-24	25		234		9.36	9.36
79	3-Jul-24	9		86		9.56	--
80	9-Jul-24	30		273		9.10	9.23
81	15-Jul-24	32		293		9.16	9.21
82	21-Jul-24	5		51		10.20	--
83	27-Jul-24	20		189		9.45	9.27
84	2-Aug-24	12		116		9.67	9.35
85	8-Aug-24	13		122		9.38	9.35
86	14-Aug-24	18		164		9.11	9.32
87	20-Aug-24	30		278		9.27	9.31
88	26-Aug-24	8		79		9.88	--
89	1-Sep-24	9		86		9.56	--
90	7-Sep-24	7		66		9.43	--
91	13-Sep-24	19		178		9.37	9.32
92	19-Sep-24	11		101		9.18	9.30
93	25-Sep-24	17		159		9.35	9.31
Sheet1

Cell Formulas
Range	Formula
F78:F93	F78	=D78/B78
G78	G78	=IF(B78<10,"",AVERAGEIF($B$78:$B78,">="&10,$F$78:$F78))
G79:G93	G79	=IF(B79<10,"--",AVERAGEIF($B$78:$B79,">="&10,$F$78:$F79))

 

[still learning]

hi asbestos_Jen
thank you for the response. I'll try in when i get home
With your example, it looks like exactly what i want
I wanted to track my Average MPG and not include any thing below a 10 gallon fill up
I loan my truck out to neighbors and it throws the average off using it.
Am I missing something. why can't i just copy the G78 formula down the to end instead of writing it again in G79

mike

as you can see by my name...i'm still learning and this site is wonderful

 

[Asbestos_Jen]

You can fill either one of them. I just used two different ways to show that the number wasn't being used in the average, either a blank cell or a double dash.

 

[still learning]

thank you
mike

 

[still learning]

I used the first one (formula) and copied it down to G150. I worked perfectly
thank you for your help

mike

 

[Asbestos_Jen]

Glad to have helped; thanks for the feedback.