Thanks:  0
Likes:  0

1. 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 ]

2. 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?

3. 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.

4. 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 ]

5. 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 ]

6. 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.

7. On 2002-03-15 10:08, Mark W. wrote:
How do I get the difference A2-A1?
{=SUM((TRUNC(A1:A2)*75+MOD(A1:A2,1)*100)*{-1;1})}
The above yields a 0 answer.

8. On 2002-03-15 10:56, rickeyjt wrote:
On 2002-03-15 10:08, Mark W. wrote:
How do I get the difference A2-A1?
{=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.

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

9. 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 ]

10. {=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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•