Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Min & Max Averages
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 |
#2
|
|||
|
|||
It would help if you told us how you had set out your data and what formulas
you are using. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Retiredff" wrote in message ink.net... 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 |
#3
|
|||
|
|||
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 wrote: It would help if you told us how you had set out your data and what formulas you are using. "Retiredff" wrote in message ink.net... 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 |
#4
|
|||
|
|||
Great!
Replace the formula by =IF(F50,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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Retiredff" wrote in message nk.net... 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 wrote: It would help if you told us how you had set out your data and what formulas you are using. "Retiredff" wrote in message ink.net... 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 |
#5
|
|||
|
|||
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(G260,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(G260,Average(G8:G26),"") to read =IF(G270,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. Bernard Liengme wrote: Great! Replace the formula by =IF(F50,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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averages | Excel Worksheet Functions | |||
Inventory Pricing - running averages | Excel Discussion (Misc queries) | |||
calculating averages | Excel Worksheet Functions |