I need Excel help! (Or....Math Freaks to Me!)

[Jester]

Okay, who here knows Excel really well?

I need help figuring something out so I can just plug one formula in, rather than retyping it for different cells.

Specifically, I am using it to track my progress with my bicycling, which I really need to start doing more of, damn it. (But that is another rant for another thread.) So what I am doing is using Excel to show me the date, the distance I rode, the total time, and the average time per mile. Even I could figure that part out. So, one easy (I think) Excel question and one tough (I think) math conversion question.

First, to figure out the average time per mile ridden, I am using the following formula: D1=C1/B1, where D1 is the average time per mile ridden, C1 is the total time, and B1 is the miles ridden. My question is, is there some way with Excel where I can just type in some kind of generic formula so that I don't have to keep retyping the formula in the D column, reformulating it for each successive row (D2=C2/B2, D3=C3/B3, etc.)? Because the retyping/reformulating it is a pain in the butt, to be quite honest.

Secondly, does anyone know or can anyone come up with a formula that will determine my average miles per hour using the information given above (total miles and time ridden)?

Yes, I know I can just go get a bike computer. And I plan on doing that, though I am still digging out from the financial whole my idiot Deadbeat ex-roommate left me in. But even once I get that computer, it won't track the information over time, which I really want to do, even once I get the bike computer.

So, Excel and math experts......any ideas?

[ArcticChicken]

I can tell you for certain the formula thing is doable, I just can't remember how, and I don't have Excel on this computer.

.....

OK, there is an AVERAGE function.

Click on the cell you want your average MPH to come up in, click Insert, click function, click AVERAGE. A window should show up, just type in the cells you want it to average. There probably is a way to do it so that you don't have to type in each cell individually, I'll get back to you on that.

.....

OK, instead of typing each individual cell type FirstCell:LastCell, it will average all those cells and all the cells in between.

As I understand it, if some of the cells are blank, they won't be counted towards your average, but if they have a 0 value in them, they will.

....

OK, do the formula thing

Click on the cell with the formula, hover over the bottom right corner of the cell until your pointer turns into a small black cross.

Click and drag the outline until each cell you want to have the formula in is covered. Let go.

[MaggieTheCat]

Assuming I read your OP right, this should be able to answer both of your questions.

This is assuming that the B column is distance ridden, C is total time, and D is average time per mile. Your distance column would have to be in miles, and your total time would have to be hours -- for example, a quarter hour would be entered as .25, half hour as .5, etc.

Regarding the formula, if you want the "average time per mile" to show up in the "D" column, then your formula in cell D1 should look like this:

=sum(C1/B1)

Once you have that in place, if you move your cursor to the lower right-hand corner of the D1 cell, it should turn into a solid black cross. Click and drag downward and it should copy the formula down as far as you want it to.

Regarding average miles per hour, it's probably easiest to set up another column -- say, "E" -- to get your MPH for each day. A formula for this would be:

=sum(B1/C1)

That is, miles ridden divided by total time will give you your MPH for that day. Drag this formula down like you did for the average time per mile formula, so you have your MPH for every day entered.

Once you have the MPH formula set up, you should be able to set up a single cell (Say, F1) with the average function like AC mentioned above. The quick formula for Averages, so you don't have to go into Insert, etc, is this:

=AVERAGE(FirstCell:LastCell)

Where FirstCell will probably be E1 (if you set up the MPH like described above) and LastCell will be something like E1000 (and if you enter more than 1000 days worth of data on the same spreadsheet, you'll have to change that number to extend the formula.) That should give you your MPH average which will include every day that you have data entered for.

I hope this helps...I don't actually have Excel on this computer, and it's a bit hard to convey Excel instructions via text. Let me know if this helps, and if it doesn't, we'll try again.

[Jester]

Maggie, thank you for your help....but unfortunately, while you solved the first problem, the second problem still stands.

This is assuming that the B column is distance ridden, C is total time, and D is average time per mile. Your distance column would have to be in miles, and your total time would have to be hours -- for example, a quarter hour would be entered as .25, half hour as .5, etc.

You assumed correctly for the values of B, C, and D. However, Excel is smarter than you thought, as I can enter time as time, not as fractions. In other words, I can enter 38 minutes and 27 seconds (yesterday's time) as 00:38:27, and it will use the formula in D to show the average time per mile in a time form, in this case, 3:51. Cool, huh? Yeah, I thought so too, and discovered that all on my own.

Your thoughts regarding the formula and how to make it work for several other rows at once were absolutely spot on. Thank you!

Regarding average miles per hour, it's probably easiest to set up another column -- say, "E" -- to get your MPH for each day. A formula for this would be:

=sum(B1/C1)

That is, miles ridden divided by total time will give you your MPH for that day. Drag this formula down like you did for the average time per mile formula, so you have your MPH for every day entered.

Alas, no. See, the formula above divides miles by time, not by HOUR. Because, using your formula, I get a number over 9,000, and while I may be fast, I am not that fast, nor is anyone.

Now, I don't know if the formula is figuring miles per second, miles per minute, or miles per hour (my guess is none of the above), but I do know that this formula is clearly not showing miles per hour, as that number should be (for the last two days, anyway) somewhere slightly below 15mph. I know this because, if we simplify things and say that I did 4 minute miles, with 60 minutes in an hour, that would give me an average speed of 15 mph. While I am good at math in general, I still cannot quite figure out the formula I would need to convert the data I have into mph.

For those who want to crunch numbers and figure out the correct formula, and/or how the above formula went wrong (which could help determine the correct formula, after all), for the 6th I rode 10 miles in 38:07 (38 minutes, 7 seconds). The incorrect formula above, when plugged in to E, gives me a figure of 9066:54:00. That is obviously a time form, so I really have no idea what Excel is doing.

My guess is that the formula may in fact be correct if time were given in decimal form, but I'll be damned if I can tell you what fraction of an hour 38 minutes and 7 seconds is! I guess we would probably need a formula to determine that, too. Of course, it is morning, and we all know I don't function all that well in the morning!

[Boggles]

If column b is miles and c is time then you should be able to use the formula below to work out the averge mph. This is assuming that you have applied the Time format to the cells.

Average MPH = sum(((sum(b first:b last))/(sum(c first:c last)))/24)

Excels built in time format uses days as the base unit so dividing by 24 converts the figure from days to hours.

I'm not at a pc with excel to test this at the moment but it should work!

[Rapscallion]

Moved to tech help section - seems appropriate.

Rapscallion

[MaggieTheCat]

What Boggles said. I tested it in Open Office, which isn't exactly like Excel but formulas generally work the same and it seems to be working. He just condensed it more than I could, so you don't need a separate column for MPH.

[Jester]

Okay, here is the progress I have thus far.

Column A is the date (unnecessary for our calculations).
Column B is the distance in miles.
Column C is the hour component of the time.
Column D is the minute component of the time.
Column E is the second component of the time.
Column F is average time per mile.
Column G is average mph for the whole trip.

Please ignore the words "average" above as we do not need to compute averages....since we are feeding the total time and the total distance in, by definition those two numbers are averages.

For mph (G), the formula was pretty straight forward once the time was broken down into its component parts: =B4/(C4+(D4/60)+(E4/3600))

I was struggling with the time per mile part, as I had the following formula: =SUM(C4+(D4/60)+(E4/3600))/B4

While accurate, the problem with that formula is it would give me a decimal fraction of an hour, say 0.0635, etc. As in 0.06 hours. Yeah, not the easiest to comprehend.

Then I thought about it, and I came up with this: =SUM((C4+(D4/60)+(E4/3600))/B4)*60

Multiplying it by 60 gave me a more workable number, for example 3.81, which now shows the average time per mile in minutes, in this case 3.81 minutes.

Now, I would of course prefer to have the time per mile column read in the standard time format, as in HH:MM:SS. If anyone can figure out how I can modify the above formula to make a decimal number a time number, it would be appreciated!

[Boggles]

Ok, that's fairly doable.

Use the initial formula you had for working out time per mile but modify it so it reads as follows:

=SUM((C4+(D4/60)+(E4/3600))/B4)/24

The apply a format to the cells. Select all of column F, click on Format then Cells. In the dialog box that appears you can specify what type of number it is. Choose Time and then select the format of hh:mm:ss (or enter it into the box labelled Format Code.

By dividing the initial formula by 24 you are expressing it as a decimal form of a day. Then the Time format should automatically convert it all to a standard display.

I've tried this in openOffice and it works so Excel should do the same.

[Jester]

Boggles, you are a genius! That is the one part I just could not figure out! Thank you!

And, using your idea about formatting, on my own I figured out how to round the mph figure to two decimals, again so it is more readable. Because 15.71 mph is easier to work with than 15.71159778524 mph, don't you think? I mean, it is not like I need this to be absolutely accurate, because the time and the distance are not necessarily that precise. I am timing myself using my kitchen timer (I can more afford for that to be damaged than my cell phone, AND it has seconds on it), which I tuck in one of my jersey pouches, and the distance is based on the fact that I clocked this particular loop that I ride at exactly 5 miles with my truck odometer.

Once I get my bike computer, things will be a little more accurate, but again, it's not like I am working for NASA and need things to be that exact. This is just for my own reference to track my progress over the coming weeks/months/years.

Again, Boggles, thanks! And if you are ever in Key West, come by my bar for your free beer/cocktail/non-alcoholic beverage of your choice.

[crashhelmet]

I keep a similar log for both cycling and running (if my knees allow me to run). I've got 5 columns in the spreadsheet and a bottom row that shows a total for the month with equations set to do the math as I enter info.

Row 1 = Month Name (I have a tab set for each month)
Row 2 = Headings for each column (Date, Distance, Time, Avg Mile, Avg MPH)
Top Row: (This is Row 3 in my spreadsheet)
Column A = Date
Column B = Distance in Miles
Column C = Time in Minutes
Column D = @sum(c3/b3) <this gives me my average time per mile>
Column E = @SUM(B3*60)/C3 <This gives me my average MPH>

I know these basically tell me the same thing but I do it this way so that I don't have to do any extra math when I look it over.

To make it easier with all of the equations, I type them into the first row of cells and then copy and paste all the way down to the last day of the month.

Bottom Row: (Usually Row 34 or 35, depends on the number of days in the month)
Column A = Total (The word itself)
Column B = @sum(B3:B33)
Column C = @sum(C3:C33)
Column D = @sum(c35/b35)
Column E = @SUM(B35*60)/C35 <The cell numbers will vary depending on the days in the month. I have a blank row between the last day of the month and the total row. So if it was February, the total row would be Row 32 or 33 for a leap year.

Hope this helps you out.

CH

[Jester]

Thanks for the thought of putting in monthly totals. That is a great idea, and one I am going to do.