shades 144 Posted November 9, 2020 Share Posted November 9, 2020 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. 1 Quote Link to post Share on other sites
shades 144 Posted November 9, 2020 Share Posted November 9, 2020 (edited) 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 Quote Link to post Share on other sites
cubd8 294 Posted November 10, 2020 Share Posted November 10, 2020 (edited) 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 Quote Link to post Share on other sites
Thorpe 218 Posted November 10, 2020 Share Posted November 10, 2020 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))) Quote Link to post Share on other sites
cubd8 294 Posted November 10, 2020 Share Posted November 10, 2020 (edited) 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 Quote Link to post Share on other sites
bryhamm 871 Posted November 10, 2020 Share Posted November 10, 2020 not really an easy/convenient way to do that with formulas. might be better doing a pivot table for that. Quote Link to post Share on other sites
Brony 7,578 Posted November 10, 2020 Share Posted November 10, 2020 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. Quote Link to post Share on other sites
cubd8 294 Posted November 11, 2020 Share Posted November 11, 2020 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 Quote Link to post Share on other sites
Chemical X 5,692 Posted November 19, 2020 Share Posted November 19, 2020 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 Quote Link to post Share on other sites
acarey50 597 Posted November 19, 2020 Share Posted November 19, 2020 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. 1 Quote Link to post Share on other sites
Chemical X 5,692 Posted November 19, 2020 Share Posted November 19, 2020 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. Quote Link to post Share on other sites
acarey50 597 Posted November 20, 2020 Share Posted November 20, 2020 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. Quote Link to post Share on other sites
Thorpe 218 Posted November 20, 2020 Share Posted November 20, 2020 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. 1 Quote Link to post Share on other sites
acarey50 597 Posted November 20, 2020 Share Posted November 20, 2020 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. Quote Link to post Share on other sites
Ned 10,547 Posted November 20, 2020 Author Share Posted November 20, 2020 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. Quote Link to post Share on other sites
heckmanm 4,351 Posted December 2, 2020 Share Posted December 2, 2020 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. 1 Quote Link to post Share on other sites
Brony 7,578 Posted December 2, 2020 Share Posted December 2, 2020 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. Quote Link to post Share on other sites
acarey50 597 Posted December 2, 2020 Share Posted December 2, 2020 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 1 1 Quote Link to post Share on other sites
Niles Standish 354 Posted December 3, 2020 Share Posted December 3, 2020 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. Quote Link to post Share on other sites
culdeus 7,249 Posted December 29, 2020 Share Posted December 29, 2020 (edited) 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 Quote Link to post Share on other sites
Ned 10,547 Posted December 29, 2020 Author Share Posted December 29, 2020 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)) Quote Link to post Share on other sites
culdeus 7,249 Posted December 29, 2020 Share Posted December 29, 2020 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. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.