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

 

[Mark W.]

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

Then change the formula to...

{=SUM((TRUNC(F2:G2)*75+MOD(F2:G2,1)*100)*{-1,1})}

...the orientation of the array constant, {-1,1}, must match the orientation of your cell reference, F2:G2. In an array constant rows are delimited by commas (,) and columns are delimited by semicolons (.
This message was edited by Mark W. on 2002-03-15 11:33

 

[rickeyjt]

On 2002-03-15 11:30, Mark W. wrote:
. . . F2:G2. In an array constant rows are delimited by commas (,) and columns are delimited by semicolons (.

Thanks for the great tips and help.

If I can bother you for one other thing . . .
The Seconds side of my sec.fr result is turning into Minutes (never gets to Hours). So 102.45 should read 1.42.45.

 

[Mark W.]

On 2002-03-16 04:27, rickeyjt wrote:

On 2002-03-15 11:30, Mark W. wrote:
. . . F2:G2. In an array constant rows are delimited by commas (,) and columns are delimited by semicolons (.

Thanks for the great tips and help.

If I can bother you for one other thing . . .
The Seconds side of my sec.fr result is turning into Minutes (never gets to Hours). So 102.45 should read 1.42.45.

=TRUNC(C1/4500)&"."&MOD(TRUNC(C1/75),60)&"."&RIGHT(ROUND(MOD(C1/75,1)*0.75,2),2)

 

[rickeyjt]

On 2002-03-18 08:43, Mark W. wrote:
=TRUNC(C1/4500)&"."&MOD(TRUNC(C1/75),60)&"."&RIGHT(ROUND(MOD(C1/75,1)*0.75,2),2)

This yields the numbers as expected. But occasionally, not all, there is an extra dot separator before the frames number, e.g., 4.3..3 or 5.17..3 which occur whether the cell is formatted Number or General. Also, some numbers get formatted with leading zeros (I would prefer that) and some not, e.g., 12.28.06, 11.57.0 or 17.7..6.

Is there something to add to the formula to ensure a two digit format for the seconds.frames? Cell format does not help.

 

[Mark W.]

Sorry about the earlier shortcomings. Try this...

=TRUNC(C1/4500)&"."&TEXT(MOD(TRUNC(C1/75),60),"00")&TEXT(ROUND(MOD(C1/75,1)*0.75,2),".00")

 

[Chris Davison]

On 2002-03-15 11:30, Mark W. wrote:
In an array constant rows are delimited by commas (,) and columns are delimited by semicolons (.

Hey Mark,

wasn't aware of that - that's handy to know

thanks
Chris

 

[Mark W.]

On 2002-03-20 11:35, Chris Davison wrote:

On 2002-03-15 11:30, Mark W. wrote:
In an array constant rows are delimited by commas (,) and columns are delimited by semicolons (.

Hey Mark,

wasn't aware of that - that's handy to know

thanks
Chris
/board/images/smiles/icon_smile.gif

Oops! In the rush to respond I got it "bass ackward"

{1,2,3,4} is a horizonal array so commas delimit columns

{1;2;3} is a vertical array where semicolons delimit rows

Sorry, for the misinformation.

 

[rickeyjt]

=TRUNC(C1/4500)&"."&TEXT(MOD(TRUNC(C1/75),60),"00")&TEXT(ROUND(MOD(C1/75,1)*0.75,2),".00")

This worked great. Thanks. If you want to see it you can download it ftp from dviews.com/annonymous login - asd.xls. Sheet 1 lets me enter start/end points and sheet 2 I can change the frame numbers of the difference between start and end points.

I needed this app for a music slow down program for doing transcriptions (Amazing Slow Downer - http://www.ronimusic.com). I define what one measure is then I can reposition the slider to move through the song one, two, three or four measures at a time.

Rick