=SUM(E3:E7,E9)/COUNTIF(E3:E9,"0") works perfect, THANKS!
(works for MPG too)

However,
=SUM(E3:E9,"0")/COUNTIF(E3:E9,"0") does not work!
Isn't "greater than" a mathematical operator and isn't SUM() a
mathematical operation?

Is there a way to figure out total time?
A2 is "10/22/07 0756" start
A8 is "10/26/07 1709" end

Row 1 is names of each column
Row 2 is trip start, fillup, the amount of gas is not used for this
calculation
Row 3 thru 7 is each gas stop/fillup, during the trip, used for
calculation
Row 8 is "home" and is the trip end point, basically for time stamps
Row 9 tops off the tank the next morning, used for calculation
Row 10 totals
Column A is date and time
Column B is odometer
Column C is trip odometer
Column D is gallons
Column E is cost per gallon
Column F is total cost
Column G is MPG
Column H is location

This isn't set in stone, it's just a sample that will change as I learn
how to use the software.

Once again, thanks for your help!


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
=COUNT(C3:C9) should work, depending on whether or not there is a
number in
C8. If there's a number in C8, you could use
=(COUNT(C3:C7)+COUNT(C9))
COUNT only counts numbers, not blanks, text entries or error entries.
COUNTA() counts non-blank entries (which would include text and even
error
indications within the range being counted).

Another option would be COUNTIF() as
=COUNTIF(C3:C9,"0")
which would only count numeric entries with values greater than zero.


"Omohundro" wrote:

Thanks, it got me started! Item 3 is fine, =SUM(C3:C7,C9) is what I
need. Item 4, a typo (sorry 'bout that) should have been E not C, is
the
cost per gallon at each fuel stop, so =SUM(E3:E7, E9)/6 gives average
cost per gallon. Thanks for the bit about total cost per fuel stop.

Can you tell me about COUNT, I think this is what I need. In E
average
cost per gallon, there are 6 elements. How do I write the equation to
count each element instead of hard coding a value which is subject to
change.


Thank you!

Omo



"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
All examples assume we are in row 2.

(1) in F2 put
=C2/D2

(2) (and normal annotation is column letter first, followed by row
number).
In B10 put this formula
=B9-B2

(3) I think what you want in C10 is
=SUM(C3:C7)
That gives a total of values in C3, C4, C5, C6 and C7 which would
be
total
of trip odometer readings.

(4) (presuming column E here vs C)
I think we got a little confused here. We need number of gallons
purchased
along with the purchase cost at each stop. We will assume that
column
D
[from (1) above] has the # of gallons purchased and E has the total
purchase
price. Then in E10 you could put
=SUM(E3:E7)/SUM(D3:D7)

SUM(E3:E7) would give cost of all fuel purchased, SUM(D3:D7) would
give
total cost for those gallons, and the formula result would be the
price per
gallon.

(4a) but if column E actually has price per gallon vs total cost at
the
stop, then for E10 we need a formula like this:
=SUMPRODUCT(D3:D7,E3:E7)/SUM(D3:D7)
The sumproduct() takes each value in column D and multiplies it by
the
value
in column E and gives a running total, so that is the total of each
#Gallons
* Price per Gallon which is total cost for the fuel. The
SUM(D3:D7)
gives us
the total gallons purchased, so dividing the SUMPRODUCT() result by
the SUM()
result gives you an average cost per gallon.

Reattempting post, please pardon me if it gets posted twice, system
hiccuping badly tonight.
"Omohundro" wrote:

(1)
Column C has miles
Column D has gallons
I wish to have Colum F determine MPG or C÷D=F

(2)
Column B has Odometer reading
Row 2B has start, Row 9B has end
I wish to have Row 10B determine total miles or 9B-2B=10B

(3)
Column C has Trip Odometer reading
Row 3C-7C and 9C has mileage since last fillup
I wish to add each designated value to a running total at 10C

(4)
Column E has cost per gallon
Row 3C-7C and 9C has cost/gal at each gas stop
I wish to add each designated value to a running total and divide
by
number of gas stops to get average cost per gallon.


Thanks for your help.