***Official*** Excel Help Corner

[shades 144]

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.

[shades 144]

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 November 9, 2020 by shades

[cubd8 294]

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 November 10, 2020 by cubd8

[Thorpe 218]

I didn't test this in Excel, but this should work:

 

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

 

[cubd8 294]

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 November 10, 2020 by cubd8

[bryhamm 871]

not really an easy/convenient way to do that with formulas.  might be better doing a pivot table for that.

[Brony 7,578]

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.  

[cubd8 294]

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

 

[Chemical X 5,692]

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

[acarey50 597]

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.

[Chemical X 5,692]

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.

[acarey50 597]

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.

[Thorpe 218]

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.

[acarey50 597]

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.

[Ned 10,547]

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.

[heckmanm 4,351]

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.

[Brony 7,578]

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.  

[acarey50 597]

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

[Niles Standish 354]

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.

[culdeus 7,249]

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 December 29, 2020 by culdeus

[Ned 10,547]

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))

[culdeus 7,249]

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.