Can you explain this Excel formula?

Wig

I found this formula which does a good job of solving an excel problem I had.

=(INT(C4/100)+(C4-100*INT(C4/100))/60)/60/24

Format cell (with the formula in) to display mm:ss


The problem was to be able to enter a set of data into excel in only mm:ss time format and then get an average. But Excel requires you to either enter your figures with leading zeros hh:mm:ss eg 00:44:13 or a trailing zero mm:ss:0

It was a pain in the neck the thought of always having to enter zeros when you didn't want to. If only micrcsoft would write better software!

Anyway, this formula allows you to enter the mm:ss straight in without having to enter a colon ":" so it can be

mmss
mmmss
ss
s

Which is pretty neat as it saves time not to type the colon's !

The formula will convert an input of 2312 (in cell C4) into a display of 23:12 into your output cell

I find the formula amazing and not being very good at maths I would like to know how it works and WHY it works......

Thanks

fwor

The formula doesn't seem to work quite the same in OpenOffice, but it almost does.

What it appears to do:

INT(C4/100) converts the number of minutes into a whole number

(C4-100*INT(C4/100))/60 converts the remainder into fractions of a minute which get added to the number of whole minutes

/60/24 converts the result into fractions of a day

Then the cell formatting of mm:ss (or hh:mm:ss in OpenOffice) displays it in the desired format.

Stompa

Wig wrote: »

I found this formula which does a good job of solving an excel problem I had.

=(INT(C4/100)+(C4-100*INT(C4/100))/60)/60/24

Format cell (with the formula in) to display mm:ss

I think I would have gone for the slightly simpler:

=(60*INT(C4/100)+MOD(C4,100))/86400

or perhaps the more readable:

=TIME(0,INT(C4/100),MOD(C4,100))

esuhl

=(INT(C4/100)+(C4-100*INT(C4/100))/60)/60/24

Looking at the left of the + sign first, the Int() function returns the integer portion of C4/100. So if you had the number 1234, C4/100 = 12.34, and INT(C4/100) = 12. This is the number of whole minutes (shown in blue above).

The other side of the plus sign, works out the number of seconds. Starting with the blue part on the right, you have the number of whole minutes (12). This is multiplied by a hundred (1200) and taken off the original number (1234 - 1200 = 34). So (C4-100*INT(C4/100)) is the number of seconds. This is then divided by 60 to convert seconds to (fractions of) minutes, and added to the number of whole minutes.

So to recap, on the left of the plus sign you have the number of whole minutes (12), then this is added the number of fractional minutes (34/60 = 0.5666667 approx.) to make 12.5666667 minutes - the decimal equalent of 12:34 in mm:ss format.

The number of minutes (12.56666667) is then dived by 60 to get the number of hours, then by 24 to get the number of days (which is just what Excel uses as its base time/date format).

Hope this helps...

Wig

esuhl wrote: »

=(INT(C4/100)+(C4-100*INT(C4/100))/60)/60/24

Looking at the left of the + sign first, the Int() function returns the integer portion of C4/100. So if you had the number 1234, C4/100 = 12.34, and INT(C4/100) = 12. This is the number of whole minutes (shown in blue above).

The other side of the plus sign, works out the number of seconds. Starting with the blue part on the right, you have the number of whole minutes (12). This is multiplied by a hundred (1200) and taken off the original number (1234 - 1200 = 34). So (C4-100*INT(C4/100)) is the number of seconds. This is then divided by 60 to convert seconds to (fractions of) minutes, and added to the number of whole minutes.

So to recap, on the left of the plus sign you have the number of whole minutes (12), then this is added the number of fractional minutes (34/60 = 0.5666667 approx.) to make 12.5666667 minutes - the decimal equalent of 12:34 in mm:ss format.

The number of minutes (12.56666667) is then dived by 60 to get the number of hours, then by 24 to get the number of days (which is just what Excel uses as its base time/date format).

Hope this helps...

You are a star! Thanks, that's exactly what I wanted.


But I'm surprised that this part (C4-100*INT(C4/100)) doesn't need to be written like this (C4-(100*INT(C4/100)))

How does it know that (C4-100*INT(C4/100))
(1234-100*12)

= 1234 - 1200

and not

=1134*12

?? it must be something to do with the respective priorities of minus and multiplication symbols? Like a multiplier always take priority over a minus sign - something I didn't know....

Wig

Stompa wrote: »

I think I would have gone for the slightly simpler:

=(60*INT(C4/100)+MOD(C4,100))/86400

or perhaps the more readable:

=TIME(0,INT(C4/100),MOD(C4,100))

They certainly both do the same trick, why is the MOD command needed?

fwor

Wig wrote: »

it must be something to do with the respective priorities of minus and multiplication symbols? Like a multiplier always take priority over a minus sign - something I didn't know....

Ah yes, I see what you mean.

Excel and OpenOffice both follow the conventions used in mathematical formulae, extended to include functions. IIRC it requires that functions are calculated first, then powers/roots, then multiplication/division, then addition/subtraction - unless that order is overridden by the use of parentheses.

MOD is simply the remainder from a division after the integer part has been removed.

Stompa

Wig wrote: »

They certainly both do the same trick, why is the MOD command needed?

As fwor suggested it just gives the remainder after a division, so:

C4-100*INT(C4/100)

can be written more simply as:

MOD(C4,100)

and it's generally a good idea to keep code as simple as possible.

Personally, I'd let the TIME() function do all the hard work. You can then pretty easily tweak it a little to cope with hh's as well as mm's & ss's, e.g.

=TIME(INT(C4/10000),INT(MOD(C4,10000)/100),MOD(C4,100))

which will convert 123456 to 12:34:56, etc....

DVardysShadow

Wig wrote: »

You are a star! Thanks, that's exactly what I wanted.


But I'm surprised that this part (C4-100*INT(C4/100)) doesn't need to be written like this (C4-(100*INT(C4/100)))

How does it know that (C4-100*INT(C4/100))
(1234-100*12)

= 1234 - 1200

and not

=1134*12

?? it must be something to do with the respective priorities of minus and multiplication symbols? Like a multiplier always take priority over a minus sign - something I didn't know....

Correct, as fwor explained. But your expectation of brackets to disambiguate is very best practice. When I teach software, I tell students that there are precedence rules, BUT I do not want them to spend a microsecond learning those rules or ever to use them. I expect them to disambiguate with brackets or by breaking calculations up. Mathematics teachers seem to be very keen on teaching students the rules of precedence, for Mathematics [where it is fine, I suppose] but also for software [where it is not].

And when I review software, I will not pass code which relies on operator precedence.

cybergibbons

DVardysShadow wrote: »

Correct, as fwor explained. But your expectation of brackets to disambiguate is very best practice. When I teach software, I tell students that there are precedence rules, BUT I do not want them to spend a microsecond learning those rules or ever to use them. I expect them to disambiguate with brackets or by breaking calculations up. Mathematics teachers seem to be very keen on teaching students the rules of precedence, for Mathematics [where it is fine, I suppose] but also for software [where it is not].

And when I review software, I will not pass code which relies on operator precedence.

Can you give a reference to a coding standard that says you should rely on parenthesis instead of operator precedence?

I can give references that state the opposite, and it's certainly not what I was taught to Masters level.

I personally think that explicitly stating the order that everything needs to be done in is unreadable and can stop the compiler fully optimising the code.

Stompa

DVardysShadow wrote: »

And when I review software, I will not pass code which relies on operator precedence.

Does that mean you would pass x-(100*y), but fail x-100*y ?