Base-75 formula?

rickeyjt

New Member
Joined
Mar 14, 2002
Messages
8
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
{=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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top