Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Base-75 formula?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Rick Trankle
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    New Member
    Join Date
    Mar 2002
    Location
    Rick Trankle
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    New Member
    Join Date
    Mar 2002
    Location
    Rick Trankle
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    New Member
    Join Date
    Mar 2002
    Location
    Rick Trankle
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #10
    New Member
    Join Date
    Mar 2002
    Location
    Rick Trankle
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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