Jump to content
Fantasy Football - Footballguys Forums

***Official*** Excel Help Corner


Recommended Posts

There's a constant problem in the video editing world with timecode: there are several different types and it's complicated to convert one format to another. It certainly can't be done in your head, the algorithm is insanely complicated. There are calculators online that can do it, but, they're all a bit different and sometimes do the conversion in a slightly different way into a format that's close but not exactly the one you need. 

Last week, I needed to convert some timecode and couldn't find an online calculator that matched the formats in and out that I wanted. But then I found an Excel file someone posted online that did the conversion for you: type in the timecode you want in A and the converted format appears in B. And vice-versa, type it into B in the alternate format, and it'll give you the matching code in A.

I was curious how it worked, so I looked at the formula in the cell, hoping to break it down. But it just was a reference to a hidden sheet. So I un-hid the sheet and took a look.

Was it the algorithm I expected? Did it need 8-20 helper cells and placeholders to keep it on track as it stepped through the process? Nope. Instead, column A was just numbers. But not just any numbers.... it was every possible number of 24 frames-per-second timecode. From 00:00:00.00 to 00:00:00.01 to 00:00:00.02 to 00:00:00.03, etc., down all of Column A. All 1,040,000 rows or whatever. Column B? Every corresponding number in 29.97 frames-per-second timecode, all million-plus possible answers.

The magic formula was just a LOOKUP. :lmao:

  • Laughing 1
Link to post
Share on other sites
  • Replies 971
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

You still need to spell out all of the cells with concat (or concatenate or just the & sign). That is: =CONCATENATE(A1, A2, A3... etc up to A-whatever. Textjoin lets you just enter a range: =

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

I'm an Excel nerd to the hilt and enjoy helping others when it comes to Excel problems, so I figured I'd start a thread where y'all can ask questions on how to do certain things in Excel. I'm game fo

In layman's terms, they basically wrote a "formula" that converts metric to standard units by writing out every single increment of centimeters, from 1 to 10.5 kilometers, in column A, then wrote out every equivalent in inches in column B, then just did a LOOKUP for the matching value.

A                B

1cm = 0.393701 inches

2 cm = 0.787402 inches

3 cm = 1.1811 inches

.... 1 million, 40 thousand rows later...

1,040,000 cm = 409448.8189 inches

 

:headexplode:

Edited by shades
Link to post
Share on other sites
22 hours ago, Thorpe said:

=IF(C1 = "-", "-", IF(A1 <> "-", A1-C1, B1-C1))

=IF(D1="-", "-", D1*0.00025)

Thank you! 

One other question related to this question. How would I update the existing  formula to account for a value of 'N' in A1 or B1? I didn't realize this until just now, but a value of 'N' is possible in any of these values and would make the calculation impossible. 

In addition to the existing formula, I need to to also output a '-' if/when A1 or B1 has a value of 'N'.

=IF(C1 = "-", "-", IF(A1 <> "-", A1-C1, B1-C1))

=IF(D1="-", "-", D1*0.00025)

Edited by cubd8
Link to post
Share on other sites

Thank you again!

One other question!

When Column A has the same value and Column B contains "Grass", aggregate the values in Column C in the Expected Value. 

How do I write this ?

Column A:                    Column B:              Column c                 Expected Value:

111                                Grass                        100                             250

111                                Weed                          50                             250

222                                WeedGrass                25                              25

222                                "              "                    0                                25

111                                Grass                        100                             250

333                                Grass                        100                              100

444                                Weed                         50                                 0

555                                "          "                       0                                   0

 

Edited by cubd8
Link to post
Share on other sites
6 hours ago, cubd8 said:

Thank you again!

One other question!

When Column A has the same value and Column B contains "Grass", aggregate the values in Column C in the Expected Value. 

How do I write this ?

Column A:                    Column B:              Column c                 Expected Value:

111                                Grass                        100                             250

111                                Weed                          50                             250

222                                WeedGrass                25                              25

222                                "              "                    0                                25

111                                Grass                        100                             250

333                                Grass                        100                              100

444                                Weed                         50                                 0

555                                "          "                       0                                   0

 

I would probably use SUMIFS for this.  In Column D, row 1: 

=SUMIFS($C$1:$C$100, $A$1:$A$100, A1) and copy down.  For Row 1, this adds up all of the occurences of 111 in Column A.  I can't tell if your example is contingent on what is in Column B.  If it is, $B$1:$B$100, B1 to the end of your SUMIFS.  

Link to post
Share on other sites

Can somebody assist with this logic below?

(IF Column A = "Down" OR Column B > 0) AND Column C > 0 then subtract Column D - Column E.
- If Column E = N OR the subtraction cannot be performed, output a '-' in Column F.

Column A:                   Column B:                     Column c                 Column d                     Column E                     Expected Calculation:

Down                           -                                      1                                763,979                       90,121                          673,858

Down                          3                                      -                                 145,566                      145,566                        -

Up                               3                                     -                                    -                                 N                                   -

Up                               2                                     -                                   -                                  70,384                         -

Down                         -                                       1                                100                             N                                   -

Up                              1                                      -                                  -                                 29,228                          -

Down                        -                                       -                                  -                                 N                                   -

Down                       2                                      1                                40                               20                               20

Down                      1                                       1                               10                               4                                  6

 

Link to post
Share on other sites
  • 2 weeks later...
13 minutes ago, Chemical X said:

here's one;

wife inserts an excel chart into a powerpoint presentation, then the chart rows lose their spacing......any idea how to respace an excel chart that is in powerpoint?

 

thx

Paste Special as an image is the easiest in my opinion, assuming you aren't trying to link it/update it in Powerpoint.

  • Like 1
Link to post
Share on other sites
34 minutes ago, acarey50 said:

Paste Special as an image is the easiest in my opinion, assuming you aren't trying to link it/update it in Powerpoint.

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

Link to post
Share on other sites
16 hours ago, Chemical X said:

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

Even so, if it's a chart, you should still be able to copy/paste it as an image.

If the Powerpoint is something that will be updated regularly, you can paste as a link to the original, and the powerpoint should update when the Excel file updates, but I know in practice it is not always that simple.

Link to post
Share on other sites
1 hour ago, Thorpe said:

I haven't tested this, but when you paste into powerpoint there is an option to "Keep Source formatting and Link Data" which sounds like what you need.

True, however, if this is a file that will be emailed, shared, etc. you will get the fun popup that the source/linked file could not be found, etc., and it will not be linked to the source data. In theory it could be simple, in practice it often is not.

Link to post
Share on other sites
2 hours ago, acarey50 said:
19 hours ago, Chemical X said:

thx.  not so easy, she just mentioned the chart is created by vba code.  so i have no idea anymore.

Even so, if it's a chart, you should still be able to copy/paste it as an image.

If the Powerpoint is something that will be updated regularly, you can paste as a link to the original, and the powerpoint should update when the Excel file updates, but I know in practice it is not always that simple.

agreed - how it's created shouldn't matter.

Link to post
Share on other sites
  • 2 weeks later...
1 hour ago, heckmanm said:

No question, just a pet peeve: People who put blank rows or columns in large spreadsheets that make it IMPOSSIBLE to use the "Filter" function.

That also stymies CTRL-SHIFT-8 to select the whole table. 

It's right up there with the people who people who use Merge and Center across columns.  

Link to post
Share on other sites
34 minutes ago, Brony said:

That also stymies CTRL-SHIFT-8 to select the whole table. 

It's right up there with the people who people who use Merge and Center across columns.  

I do wish they'd make Center Across Selection the default instead of merge - it is much more useful in my opinion and essentially accomplishes the same desired formatting

  • Thanks 1
  • Love 1
Link to post
Share on other sites
15 hours ago, heckmanm said:

No question, just a pet peeve: People who put blank rows or columns in large spreadsheets that make it IMPOSSIBLE to use the "Filter" function.

I know for me if I click the kind of half square looking thing to the left of the A (first column) it highlights the whole excel and lets you filter.

Link to post
Share on other sites
  • 4 weeks later...

Help

I have a number that must be formatted to two digits, i.e. 5 = 05. I can easily format a cell to display this.  ok.

Now I want to concatentate this number into a string and need it to also be "05" in the string.  

how?

 

Edit, answer was basically : ="G4-"&D16& "-" &TEXT(D12,"#00")

Edited by culdeus
Link to post
Share on other sites
2 minutes ago, culdeus said:

Help

I have a number that must be formatted to two digits, i.e. 5 = 05. I can easily format a cell to display this.  ok.

Now I want to concatentate this number into a string and need it to also be "05" in the string.  

how?

This will combine A1 and B1.

=IF(A1<10,"0"&A1&B1,A1&B1)

or you can use CONCATENATE.

=IF(A1<10,CONCATENATE("0",A1,B1),CONCATENATE(A1&B1))

Link to post
Share on other sites
1 minute ago, Ned said:

This will combine A1 and B1.

=IF(A1<10,"0"&A1&B1,A1&B1)

or you can use CONCATENATE.

=IF(A1<10,CONCATENATE("0",A1,B1),CONCATENATE(A1&B1))

I figured out another way to do it, see the edit.  

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    No registered users viewing this page.


×
×
  • Create New...