column 1 time
column 2 time interval between adjacent rows (in minutes)
column 3 value of a variable
column 4 change in that variable between adjacent rows
I need to calculate the rate of change per hour.
I assumed that the equation would be coulumn4/(column2*60)
This kinda works, except it gives me exactly 40% of the right answer.
To get the right answer, all I have to to do is multiply the result by 2.5, but why?
If the time interval is X minutes, and the variable interval is Y units, then Y/(X*60) is the wrong equation, isn’t it?
Y/X is units per minute. Thus, units per hour will be sixty times that, not a sixtieth of that. You want (Y/X)*60.
–p
Logically, of course, you are correct. But excel doesn’t seem to follow normal human based reasoning.
To give you an idea
5.32m[sup]3[/sup] / 30 minutes = 10.64 m[sup]3[/sup] in the real world
using (y/x) in excel gets 225.36 - multiplying this by 60 isn’t going to help.
using y/(x*60)*2.5 gets exactly the right answer - hence the
that would be 10.64 m[sup]3[/sup] / hr :smack: I really am a doofus :smack:
Can you post a couple of sample rows?
Actually, I just figured it out.
60/2.5 = 24
When excel sees a time, it figures that 1 = 1 day. To get an hourly rate, just divide by 24. Who knew?
(before)
Time Volume Volume Time x/y
Change Period
5:15 2.49
5:22 1.25 -1.24 0:07 -255.09
5:25 7.4
5:55 2.08 -5.32 0:30 -255.36
6:00 6.69
6:25 2.29 -4.4 0:25 -256.44
(fixed)
Time Volume Volume Time hourly
Change Period rate
5:15 2.49
5:22 1.25 -1.24 0:07 -10.63
5:25 7.4
5:55 2.08 -5.32 0:30 -10.64
6:00 6.69
6:25 2.29 -4.4 0:25 -10.56
are you using relations to do this? You must be doing something wrong
0
30 30 5.32 10.64
60 30 9 18
column a is interval time in minutes
column b is time between intervals
column c is variable
column d is (c2/b2)*60
If you use regular numbers it works, if the cell is formatted for time, it doesn’t.
Not exactly.
The cell formatting has nothing to do with it. you can format the cell as a number, a date, a time or a percentage and the actual calulation will come out the same. The real issue, as you alluded to earlier is that “time” in Excel is really a combination of day and time.
Today (Sunday 11/21/04 in the conventional calendar) is day number 38312 in Excel’s internal calendar. Midnight is stored as 38312. Noon is 38312.5, 6pm is 38312.75, and a moment before midnight when Sunday becomes Monday is 38312.9999, etc.
This internal format makes calculating the interval between any two dates, or any two times on specific dates very easy. And for typical business user who just type dates and times into cells, the cell formatting takes care of hiding the part you don’t care about, and converting 38312.5 into a display of “11/21/04” if you format the cell as a date or “12:00:00” if you format the cell as a time.
As you’ve noticed though, when you’re thinking in terms of time intervals without a date attached, this method needs 1 more conversion step, namely scaling the value 1 into 24 hours or 1440 minutes or 86400 seconds or whatever is appropriate for your particular data.
A couple of gotchas:
When you type a time like “12:00:00” into a cell, what date is that on? Excel will use a zero date, so the actual cell value is 0.5. The zero point in Excel’s calendar is 1/0/1900. Or stated more conventionally, 1/1/1900 is Day 1. Excel doesn’t handle dates prior to that real well, so you can get ugly results if your time calculations result in a negative time interval. The math is done correctly, but the date and time formatter will choke on values below zero and you’ll get a cell full of “######”
If you have a time interval longer than 24 hours and have the cell formatted to display time in the typical way, it’ll appear to have lost all multiples of 24 hours. ie 25 hours and 15 minutes will display as “1:15” and 50 hours 30 minutes will display as “2:30”.
What’s happening internally is the value for 25:01 is really 1.052… and 50:30 is really 2.104…
And you’ve said, with conventional time formatting, to ignore the whole number part and just display the fractional part converted into hours, minutes and seconds. So Excel throws away the 1 and the 2 and just displays the 0.052… and 0.104 …
To solve that, use one of the other time formats further down the picklist, or make a custom format like [h]:mm. The around the h tells Excel to display all the days’ integer parts as 24 hours. So 2.5 is converted to 2*24 + .5 = 48 hours + 12 hours = 60 hours and it’ll display as “60:00:00”
Like I said, I’m a :wally when it comes to Excel, but with everyone’s help, I got there in the end. Thanks, guys!