G
Guest
Time formula. J8-I8. In K8(If J8-I8>=59/60 if <=60/100)
N
Nick Hodge
Rookie
I think we may need a little more help on what you are trying to do?
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
I think we may need a little more help on what you are trying to do?
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
G
Guest
Okay it is a little ambiguous. I am refining a spreadsheet that illustrates
indivdual production. It has a start time (cell I8) and a finish time (cell
J8). J8 minus I8 is total hours in cell K8. The amount of time completed
is in decimal fraction form. The time input is in some portugese postal code
form (not my brain child, I just adopted it) to ease the entries on the sheet
(i.e. 0400, 0430, 0015, etc.). The crux of the problem is in cell K8 and
it's formula as follows (J8-I8)/100. This formula works just so long as it
is 60 minutes or more (0215-0315). IF the toal time time to completion is 59
minutes or less the formula must be (J8-I8)/60. I must find how to make
these two conditional variations work in one formula. I've tried IF, SUM,
SUMIF ad infinitum. I am unsophisticated in Excel, a novice at best so just
because i tried these functions doesn't mean I did their inputs correctly. I
ask of you all, to give me
the correct function and arguements for what seems to be an easy function
that I cannot cypher.
indivdual production. It has a start time (cell I8) and a finish time (cell
J8). J8 minus I8 is total hours in cell K8. The amount of time completed
is in decimal fraction form. The time input is in some portugese postal code
form (not my brain child, I just adopted it) to ease the entries on the sheet
(i.e. 0400, 0430, 0015, etc.). The crux of the problem is in cell K8 and
it's formula as follows (J8-I8)/100. This formula works just so long as it
is 60 minutes or more (0215-0315). IF the toal time time to completion is 59
minutes or less the formula must be (J8-I8)/60. I must find how to make
these two conditional variations work in one formula. I've tried IF, SUM,
SUMIF ad infinitum. I am unsophisticated in Excel, a novice at best so just
because i tried these functions doesn't mean I did their inputs correctly. I
ask of you all, to give me
the correct function and arguements for what seems to be an easy function
that I cannot cypher.
J
joeu2004
Rookie said:
That is a m-u-c-h better explanation. I am not sure I fully understand
everything (esp. the input format), but perhaps the following satisfies
your requirements:
=(J8-I8) / if(J8-I8 >= 60, 100, 60)
----- complete previous posting -----
S
Sandy Mann
Just another option:
(J8-I8)/(100*(0.6^(J8-I8<60)))
--
HTH
Sandy
In Perth, the ancient capital of Scotland
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
(J8-I8)/(100*(0.6^(J8-I8<60)))
--
HTH
Sandy
In Perth, the ancient capital of Scotland
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
J
joeu2004
Sandy said:
Aah, I bet you were an APL programmer in another life
. Here's thesledgehammer; there's the ant ;-).
S
Sandy Mann
Hi Joe,
Not only that but my formula takes twice as long to calculate.
From various posts I have seen where it seemed to me that people were
suggesting that fewer function calls were better, I jumped to the conclusion
that calculations were faster than function calls.
However, it would seem that in this case at least that is not true. In
calculating a whole column of your formula 1,000 times, the VBA Timer
returned an average of 0.064203 & 0.068414 seconds on two runs whereas mine,
with no function calls, took 0.135188 & 0.136234 seconds
Replacing raising to a power with multiplication improved my result to
0.077914 & 0.077797 seconds, so even then it did not beat your IF() formula,
(and of course gave wring results but I was only interested in calculation
times).
mmmmmm....... There's more to spreadsheets than meets the eye....
--
Regards
Sandy
In Perth, the ancient capital of Scotland
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
Not only that but my formula takes twice as long to calculate.
From various posts I have seen where it seemed to me that people were
suggesting that fewer function calls were better, I jumped to the conclusion
that calculations were faster than function calls.
However, it would seem that in this case at least that is not true. In
calculating a whole column of your formula 1,000 times, the VBA Timer
returned an average of 0.064203 & 0.068414 seconds on two runs whereas mine,
with no function calls, took 0.135188 & 0.136234 seconds
Replacing raising to a power with multiplication improved my result to
0.077914 & 0.077797 seconds, so even then it did not beat your IF() formula,
(and of course gave wring results but I was only interested in calculation
times).
mmmmmm....... There's more to spreadsheets than meets the eye....
--
Regards
Sandy
In Perth, the ancient capital of Scotland
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
S
Sandy Mann
Actually, for the sake of the archives if for no other purpose, I think that
I was doing myself a disservice because my methodology in timing the
formulas was flawed.
I tested it again with only one formula and set of data. I used VBA to
switch cell J8 alternately between 100 & 200 with cell I8 static at 50 and
used the VBA Timer to return the time at the start and finish of a 100,000
cycle loop.
The results then were:
Joe's IF() formula: an average over 5 of the above runs of 39.17188 secs
My *sledgehammer* formula over a similar 5 runs of the above loops: 39.71875
secs
I was quite surprised at the variation in the times returned for Joe's
formula which varied from a max of 40.30469 secs to a min of 38.78125 secs
whereas my formula only varied by 0.6 seconds so it may be that trying to
time formulas the way I did it is still flawed.
--
Sandy
In Perth, the ancient capital of Scotland
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
I was doing myself a disservice because my methodology in timing the
formulas was flawed.
I tested it again with only one formula and set of data. I used VBA to
switch cell J8 alternately between 100 & 200 with cell I8 static at 50 and
used the VBA Timer to return the time at the start and finish of a 100,000
cycle loop.
The results then were:
Joe's IF() formula: an average over 5 of the above runs of 39.17188 secs
My *sledgehammer* formula over a similar 5 runs of the above loops: 39.71875
secs
I was quite surprised at the variation in the times returned for Joe's
formula which varied from a max of 40.30469 secs to a min of 38.78125 secs
whereas my formula only varied by 0.6 seconds so it may be that trying to
time formulas the way I did it is still flawed.
--
Sandy
In Perth, the ancient capital of Scotland
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
J
joeu2004
Sandy said:
I think the issue of how to time Excel spreadsheet operations is an
interesting one. I was tempted earlier to post a follow-up with some
questions; and now I have some new comments. But I decided it might be
worthy of a new thread.
Be that as it may, I will say here.... I used to worry about
arithmetic performance back in the days when a "fast" microprocessor
had a nominal instruction time of 1 microsecond (pre-RISC) and
floating-point operations were implemented in software. But today, I
lean toward readability and maintainability -- a standard which must be
tailored to the audience -- unless and until I discover or believe that
performance is truly an issue. (Certainly not in this case.) Of
course, I try to find the most concise "readable and maintainable"
solution, but only because I believe it conveys the purpose of the
formula best.
In short: "Don't sweat the small stuff".
----- previous posting -----