Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula
Hi,
I have a formula that works out the average of four miles per gallon figures. =AVERAGE(G2,J2,M2,P2) Basically I am going to automate the values in those cells with a forumla also. My problem is that sometimes the formula will be returned as a #DIV/0 which in turn makes the average #DIV/0. The formula in cells G2 etc will calculate the Miles Per Gallon that an employee has claimed their private miles at so if these cells represent each week of the month there will be 0's sometimes if they have not travelled any private miles within that week hence why there may be #DIV/0 in the miles per gallon figures. I need the formula to do an average of those four cells using the value only if it isn't #DIV/0. Is this possible? The formula that will be in G2 etc will be =(F2*((T2/S2)*4.54))/H2 Translated this is =(private miles*((cost of fuel used/litres of fuel used)*4.54))/amount paid for private miles Which then gives the miles per gallon that they have calculated their private fuel costs at. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula
I must be getting better at this than I think, I have answered my own
question!! I have not changed the average formula instead I have: =IF(ISERROR((F2*((T2/S2)*4.54))/H2)),"",(F2*((T2/S2)*4.54))/H2) for the miles per gallon. Any glaring errors or more efficient possibilities?? "Pyrite" wrote: Hi, I have a formula that works out the average of four miles per gallon figures. =AVERAGE(G2,J2,M2,P2) Basically I am going to automate the values in those cells with a forumla also. My problem is that sometimes the formula will be returned as a #DIV/0 which in turn makes the average #DIV/0. The formula in cells G2 etc will calculate the Miles Per Gallon that an employee has claimed their private miles at so if these cells represent each week of the month there will be 0's sometimes if they have not travelled any private miles within that week hence why there may be #DIV/0 in the miles per gallon figures. I need the formula to do an average of those four cells using the value only if it isn't #DIV/0. Is this possible? The formula that will be in G2 etc will be =(F2*((T2/S2)*4.54))/H2 Translated this is =(private miles*((cost of fuel used/litres of fuel used)*4.54))/amount paid for private miles Which then gives the miles per gallon that they have calculated their private fuel costs at. Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula
Another way would be to check for zero in those 2 cells
=IF(OR(H2=0,S2=0),"",(F2*((T2/S2)*4.54))/H2) Mike "Pyrite" wrote: I must be getting better at this than I think, I have answered my own question!! I have not changed the average formula instead I have: =IF(ISERROR((F2*((T2/S2)*4.54))/H2)),"",(F2*((T2/S2)*4.54))/H2) for the miles per gallon. Any glaring errors or more efficient possibilities?? "Pyrite" wrote: Hi, I have a formula that works out the average of four miles per gallon figures. =AVERAGE(G2,J2,M2,P2) Basically I am going to automate the values in those cells with a forumla also. My problem is that sometimes the formula will be returned as a #DIV/0 which in turn makes the average #DIV/0. The formula in cells G2 etc will calculate the Miles Per Gallon that an employee has claimed their private miles at so if these cells represent each week of the month there will be 0's sometimes if they have not travelled any private miles within that week hence why there may be #DIV/0 in the miles per gallon figures. I need the formula to do an average of those four cells using the value only if it isn't #DIV/0. Is this possible? The formula that will be in G2 etc will be =(F2*((T2/S2)*4.54))/H2 Translated this is =(private miles*((cost of fuel used/litres of fuel used)*4.54))/amount paid for private miles Which then gives the miles per gallon that they have calculated their private fuel costs at. Thanks in advance for any help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula
That's fine, but taking your original formula:
=(F2*((T2/S2)*4.54))/H2 then if this produces the #DIV/0 error it must be because H2 is zero. So, another way of avoiding the error (and reducing the number of brackets which are not required) is: =IF(H2=0,"",F2*T2/S2*4.54/H2) Hope this helps. Pete On Sep 17, 9:26*am, Pyrite wrote: I must be getting better at this than I think, I have answered my own question!! I have not changed the average formula instead I have: =IF(ISERROR((F2*((T2/S2)*4.54))/H2)),"",(F2*((T2/S2)*4.54))/H2) for the miles per gallon. Any glaring errors or more efficient possibilities?? "Pyrite" wrote: Hi, I have a formula that works out the average of four miles per gallon figures. =AVERAGE(G2,J2,M2,P2) Basically I am going to automate the values in those cells with a forumla also. My problem is that sometimes the formula will be returned as a #DIV/0 which in turn makes the average #DIV/0. The formula in cells G2 etc will calculate the Miles Per Gallon that an employee has claimed their private miles at so if these cells represent each week of the month there will be 0's sometimes if they have not travelled any private miles within that week hence why there may be #DIV/0 in the miles per gallon figures. I need the formula to do an average of those four cells using the value only if it isn't #DIV/0. Is this possible? The formula that will be in G2 etc will be =(F2*((T2/S2)*4.54))/H2 Translated this is =(private miles*((cost of fuel used/litres of fuel used)*4.54))/amount paid for private miles Which then gives the miles per gallon that they have calculated their private fuel costs at. Thanks in advance for any help.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula
Ah yes, I missed the S2 divisor - good job you are on the case, Mike
<bg Pete On Sep 17, 9:47*am, Mike H wrote: Another way would be to check for zero in those 2 cells =IF(OR(H2=0,S2=0),"",(F2*((T2/S2)*4.54))/H2) Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with an average formula | Excel Worksheet Functions | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Average Formula | Excel Worksheet Functions | |||
Average formula | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |