SUMIF(), that'll do it! Anything I click on after a search in the main
Excel help screen comes of blank. I've uninstalled, cleaned out the
registry, and reinstalled as well as Detect and Repair and it still does
it.



Had I actually looked at "General" in a Time/Date cell it would have
taken me about 1 second to realize and another minute to verify its
basically NASA time.



Date: 1/1/1900= 1, 1/1/1901= 365, 1/1/1902(leapyear)= 731,

1/1/1903= 1096, 10/28/2007= 39383, 10/29/2007= 39384



Time: 24 hours = base 10 value

The mantissa shows 5 digits to the right of the decimal point, so:

1 day = 100,000 parts

100000/24 = 4,166.6667 per hour (remainder is base 10 min)

10000/60 = 166.667 = per minute (remainder is base 10 sec)

100/60= 1.67 = per second



36384.12345 = 10/29/2007 @ 02:57:46



12345/4166.6667= 2.96279997629 hours

9628/166.667= 57.7678844642 minutes

77/1.67= 46.1 seconds





Thanks for your help!



Omo




"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Excel'ls Help feature can assist in finding out just exactly how many
of
these functions work. Usually if you will type the function followed
by the
word 'function' in the search for box it will provide that function's
explanation as one of the returns.
SUM is indeed a math function, simply adding the values of the ranges
referenced. COUNTIF() is a semi-math operation, counting a cell IF it
meets
the criteria or test.

There is a SUMIF() also, you could use it instead of the SUM() in the
2nd
formula you gave - the one that didn't work. Excel 2007 has added
some more,
including a SUMIFS() which allows multiple tests instead just one.

Time is sometimes difficult to work with. Lots of questions in these
forums
about dealing with time. But basically you can subtract the starting
time
from the end time just as you would any two numbers. But if you
subtract
time in a way that gives you a negative number, you get an error.
Often the
trick with doing math with time is setting up the format of the
result.
Using your examples, if you subtract the start from the end, you may
end up
with a couple of values:
1/5/00 9:13 because the cell is formatted as time, or
5.3840278 if the cell is formatted as General. 5.3840278 is 5 and
.340278
days.

But if you select the cell with the result and then use Format | Cells
and
go to the bottom of the list and choose [Special] then type in this
format:
[h]:mm:ss
the result is displayed as 129:13:00 or 129 hours, 13 minutes, zero
seconds,
which is probably more like what you want!

Remember that formatting only controls how a value is displayed, not
what is
hiding under the surface. In reality, the value is 5.340278 for all
cases
here.




"Omohundro" wrote:


=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.