PDA

View Full Version : Solved: A Problem with Excel Formula Units



Cyberdude
12-10-2005, 03:19 PM
I have a formula from J. Walkenbach?s book for calculating a runner?s ?pace? in minutes per mile that works:

=(A1 * 60 * 24) / A2..........returns 12.5 minutes/mile (correct)

where ?A1? = 1.5 miles
..........?A2? = 4.8 miles/hour

Using dimensional analysis, I can?t make the units come out correct (should be minutes/mile).
Adding units with values in the equation, I get:

=(1.5 miles * 60 minutes/hour * 24 hours/day) / 4.8 miles/hour
=(1.5 miles * 1440 minutes/day) / 4.8 miles/hour
=(1.5 miles * 1440 minutes/day) * (1 / 4.8) hours/mile
= 12.5 minutes/mile

The miles seem to cancel each other out leaving (minutes/day) * hours

Can anyone explain this to me?

matthewspatrick
12-10-2005, 04:02 PM
I don't understand your question at all.

If you are trying to convert mi/hr to min/mi, all you need to do is this:

1 / (X mi/hr * 1 hr / 60 min) = 60 min / X mi = 60/X min / mi

mdmackillop
12-10-2005, 04:29 PM
Hi Sid,
Minutes per mile is dependent on cell A2 only, if you have your units right. A1 is irrelevant.
Regards
Malcolm

Bob Phillips
12-10-2005, 04:35 PM
If A1 holds the distance, A2 the time, then

=A2/A1 gives the mins per mile

E.g.

A1:5
A2:27:30
=A2/A1 := 5:30

Cyberdude
12-11-2005, 08:38 PM
Let me explain...I was looking at one of my J. Walkenbach books and that formula caught my eye for some reason. It just didn't look right, so I copied it to a worksheet and tested it. Sure 'nuff it computed the correct answer. It still didn't look right, so I decided to use dimensional analysis to see the the units made sense. I always do that when I can't decide whether to divide or multiply something. That's when I got into trouble ... the units just don't make sense, yet the one test I ran worked OK.
Sometimes I assume incorrect units for a constant. The 24 bothered me. I assumed the units are hours per day, but it just doesn't fit.
I will say that I have found numerous errors in formulas in that book, so if this is another one, I won't be surprised.
So it isn't a problem I'm trying to solve ... I'm just trying to make sense of what John wrote. From the sound of your posts, methinks John leet one slip by him. Thanks for the comments, guys.

clvestin
12-11-2005, 09:07 PM
Could it be that the correct units for A2 are miles/day? This seems a more rational pace for the average runner/jogger. then the dimensional analysis would balance out as well.
min/mi=mi(min/hr)(hr/day)(day/mi)

Cyberdude
12-12-2005, 12:31 PM
After spending 5 more hours trying to solve the units part of this problem, I am now convinced that J. Walkenbach did not make an error in his book, and I apologize for suggesting that the book is in error.

To clvestin: I think you are correct, although I think probably it's miles/hour/day or its reciprocal.

The quirk of this problem is in the fact that the runner's time is recorded on the coach's laptop as a type Date. He wrote it like,say, 0:30:00, rather than 30 minutes. That brought up the question about the units of type Date. Just what are the units?? I've come to believe that the units are "day", at least for the time fraction. If I format 0:30:00 as a number, I get 0.0208333, which is that part of a day that 0:30:00 represents. Clearly, 0:30:00 does not have "minutes" as its units. When you start putting in "day" as the units, then it starts to make sense, but I could never completely work it out for all variations of the formula.

I'm happy with using the reciprocal of miles/hour divided by 60. By writing the formula the way he did, Walkenbach was trying to illustrate something, but he never bothered to explain it. Thanx for the thoughts, guys.