Base-75 formula?

[rickeyjt]

I am trying to calculate seconds and frames for music. Each second is divided into 75 (0-74). Left of the decimal point is seconds, right of the decimal point is frame number. I want to add, subtract or multiply, e.g., 2.45 + 5.65 with a one step formula. Then also take total number of frames and convert back to seconds.frames.

2x75=150+45=195 + 5x75=375+65=440 =
635 (total frames) /75 = 8 seconds ???frames
8x75=600
635-600=35
8.35 (What formula to get this number?)

[ This Message was edited by: rickeyjt on 2002-03-15 08:20 ]

[Steve Hartman]

On 2002-03-15 08:17, rickeyjt wrote:
I am trying to calculate seconds and frames for music. Each second is divided into 75 (0-74). Left of the decimal point is seconds, right of the decimal point is frame number. I want to add, subtract or multiply, e.g., 2.45 + 5.65 with a one step formula.

To make sure I understand your example, 2.45 is 2 and 45/75 seconds and 5.65 is 5 and 65/75 seconds?

[rickeyjt]

Right. The number to the right of the decimal point can only go up to 74. Then the seconds will increment 1 and the number to the right of the decimal point will be 0.

[Mark W.]

Total frames can be calculated using the array formula...

{=SUM(TRUNC(A1:A2)*75+MOD(A1:A2,1)*100)}

...where A1:A2 contains {2.45;5.65}.

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

With the Total frames formula in C1 your 'seconds.frames' value can be calculated using...

=TRUNC(C1/75)+MOD(C1/75,1)*0.75

[ This Message was edited by: Mark W. on 2002-03-15 09:23 ]

[rickeyjt]

On 2002-03-15 09:21, Mark W. wrote:

{=SUM(TRUNC(A1:A2)*75+MOD(A1:A2,1)*100)}

...where A1:A2 contains {2.45;5.65}.

How do I get the difference A2-A1?

With the Total frames formula in C1 your 'seconds.frames' value can be calculated using...

=TRUNC(C1/75)+MOD(C1/75,1)*0.75

This part is great because now it's easy to convert total frame numbers to sec.fr.

[ This Message was edited by: Mark W. on 2002-03-15 09:23 ]

[Mark W.]

On 2002-03-15 09:59, rickeyjt wrote:
{=SUM(TRUNC(A1:A2)*75+MOD(A1:A2,1)*100)}

...where A1:A2 contains {2.45;5.65}.

How do I get the difference A2-A1?

{=SUM((TRUNC(A1:A2)*75+MOD(A1:A2,1)*100)*{-1;1})}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[rickeyjt]

On 2002-03-15 10:08, Mark W. wrote:

{=SUM((TRUNC(A1:A2)*75+MOD(A1:A2,1)*100)*{-1;1})}

The above yields a 0 answer.

[Mark W.]

On 2002-03-15 10:56, rickeyjt wrote:

The above yields a 0 answer.

Only if you fail to enter using Control+Shift+Enter as recommended in my note.

[ This Message was edited by: Mark W. on 2002-03-15 11:07 ]

[lenze]

You can also use the DOLLARDE() and DOLLARFR() functions.

If you have a cell(A1) with 8.45 (8 seconds and 45 frames), DOLLARDE(A1,75) will convert it to a decimal equivalent (i.e. 8.6), which you can use in normal math operations. If your result is in cell(C1), you can convert it back with DOLLARFR(C1,75)

[ This Message was edited by: lenze on 2002-03-15 11:11 ]

[ This Message was edited by: lenze on 2002-03-15 11:12 ]

[rickeyjt]

{=SUM((TRUNC(A1:A2)*75+MOD(A1:A2,1)*100)*{-1;1})}
The above yields a 0 answer.

Only if you fail to enter using Control+Shift+Enter as recommended in my note.

I did use ctrl-shft-ent. I copied the formula (minus {} outside braces). My cell positions are F2:G2, that's the only difference.