Min & Max Averages

[Retiredff]

Right off the top, let me say that I know next to nothing about Excel and
how to use formulas. I have spent several hours over the last two days
looking for an answer, and if I found it, I did not recognize it.

I may be trying to re-invent the wheel, but here is my problem.

I am setting up a spreadsheet to track short and long-term fuel economy.
Single tank MPG was no problem. I have succeeded in getting it to work so
that it will give me a running average for three and fifteen tank fills.

I want to be able to track the minimum and maximum MPG in each of the three
categories (single, three, and 15 tank). I can get it to work as long as I
have entries in those categories. I can't figure out how to get it to check
(compare?) as I add additional fill-ups to my list.

For instance, if I select the entire one-tank column, and use that range in
my formula, I wind up with #DIV/0! as the answer, even though there are
averages listed. If I only select the columns that have an entry, then I get
the correct figure.

I hope this is clear enough so someone will understand what I am trying to
do.

Many thanks.

Larry

 

[Bernard Liengme]

It would help if you told us how you had set out your data and what formulas
you are using.

 

[Retiredff]

OK. Very understandable.

Using the MPG Three Tank AVG column (F) as an example:

I started with =AVERAGE(F3:F5). I dragged it down to select the column. The
cell numbers increase by one with each fill-up, giving me my running
three-tank average.

If I want to find the High Average from all the averages in the three-tank
column (G), I have set up another column (K) (High Average Three Tank), and
tried to use this: =Max(G3:G25). My test sheet only has entries cell 25. It
will give me the correct single High Average that is listed in those cells.
However, if I try to add more cells in column G to that formula, in order
for the High Average to update as I buy more gas, it gives me #DIV/0! as the
answer. It only works as long as the range of cells already have numbers in
them.

I apologize if this is still not clear, and is not the information you asked
for. When I read my answer to you, it makes sense. I mat just be having a
hard time expressing my answer in a way that others will understand. My
first thought was that you wanted the entire setup, but that did not make
sense to me, since the rest of it is working correctly.

Larry

 

[Bernard Liengme]

Great!
Replace the formula by =IF(F5>0,AVERAGE(F3:F5),"")
The item after the last comma is double quotes in a row.
This say: IF the value is F5 is greater than 0, then do the calculation,
else display nothing.

My old math teacher- an Irishman - was fond of saying "There are more ways
of killing a pig than stuffing it with butter", so here is another formula
to try
=IF(COUNT(F3:F5)<3,"",AVERAGE(F3:F5))

How do you like retirement? This is my first month!

best wishes

 

[Retiredff]

Thanks, Bernard Liengme.

Never would I have been able to figure that out!

Still not able to get it to work the way I want it to.

I made some changes to the sheet, so the cell numbers have changed. Maybe I
should try to explain another way.

I have two MPG columns. Column F shows my One Tank Avg (what any one would
get if they check their mileage after filling up. Column G has my Five Tank
Avg (=IF(G26>0,MIN(G8:G26),"").)

Next, I have two High Avg columns. J is the One Tank High Avg, and K is the
Five Tank High Avg.

On my test sheet, I have entered on rows 4-26, enough info to get my one and
five tank averages in columns F & G.

Using your first formula, I had to change Average to Min, and it will do
want I want, but only if columns F & G have figures in them. Columns F & G
have the formulas to figure out the answers as I continue to add fuel
fill-ups to the list. If I change your formula =IF(G26>0,Average(G8:G26),"")
to read =IF(G27>0,MAX(G8:G27),""), with row 27 not having any figures to
work with, but only a formula, then the answer in cells J3 & K3 (which is
where I want the single-most One & Five Tank high average from columns F & G
to show, does not give me a number. Instead, it shows #DIV/0!.

I need the formula for cells J3 & K3 to be able to adjust to the fact that I
will continue to enter info that will show up in columns F & G, and if those
averages are higher then what was previously recorded in J3 or K3, the it
will change.

If I can figure out how to get one column to update when new figures are
entered, then it will be easy enough to adjust it for the other one.

The second formula you provided doesn't work. I get an error message. As for
retirement- mine wasn't by choice, it was for medical reasons. If I had the
years in for a normal retirement, I think I would have be a little happier.