savagetracey
asked on
EXCEL FORMULA CHECKING AND HELP
Hello. I came on ee last year with this question and had a part work around which was good but has a few glitches. I'm attaching a small version of the file I'm working on.
I need help with the following:
Checking the formulas in the green columns. These are calculations of the Time In and Time Out columns to show the duration in FF and SS of each shotEx-Exchange-Formula-Help.xls. I think they are correct but I'd love someone to check these to ensure they are ok. I had a fair few errors last time I had to go in and manually adjust which takes hours.
I'd love it if someone is able to expand this formula to 300 lines depth. I did have a few lines sent last time but when I transfered into another file with my text and timecodes things got tricky and messy. I suspect it would be easier and less of an error magnet if I can transfer text into a file with the calculations already on it rather than the other way around!
It was suggested I have a frame value check column to pick up any errors. There were a lot of errors last time I had to go in and manually do so I'm wondering if anyone can check that columns formula too. Essentially I need an airtight (if possible!) way to calculate Time Code IN and Time Code out to get the duration in seconds and frames of each line/shot length. I have six documents each about 300 lines long hence hoping someone can send me a document of this lenth with the formulas copied down it so I can just cut and paste in my text.
I am working with 25 frames per second. hh:mm:ff:ss
If the return document is too big it could go into my hightail link which I could send...
Thank in advance, this is headache making! I'm certainly no expert.
Tracey.
I need help with the following:
Checking the formulas in the green columns. These are calculations of the Time In and Time Out columns to show the duration in FF and SS of each shotEx-Exchange-Formula-Help.xls. I think they are correct but I'd love someone to check these to ensure they are ok. I had a fair few errors last time I had to go in and manually adjust which takes hours.
I'd love it if someone is able to expand this formula to 300 lines depth. I did have a few lines sent last time but when I transfered into another file with my text and timecodes things got tricky and messy. I suspect it would be easier and less of an error magnet if I can transfer text into a file with the calculations already on it rather than the other way around!
It was suggested I have a frame value check column to pick up any errors. There were a lot of errors last time I had to go in and manually do so I'm wondering if anyone can check that columns formula too. Essentially I need an airtight (if possible!) way to calculate Time Code IN and Time Code out to get the duration in seconds and frames of each line/shot length. I have six documents each about 300 lines long hence hoping someone can send me a document of this lenth with the formulas copied down it so I can just cut and paste in my text.
I am working with 25 frames per second. hh:mm:ff:ss
If the return document is too big it could go into my hightail link which I could send...
Thank in advance, this is headache making! I'm certainly no expert.
Tracey.
ASKER
Oh great Barry. Yes those are the results I'm after so I'll try these... Trace
ASKER
Hi Barry. I've tried pasting into another sheet I'm working on and just changed the row values i.e. C3 to C5 as that one starts on row 5 rather than 3. Should that make a difference?. The FF duration column calculation is working but the SS one doesn't seem to be. Unless I've copied across incorrectly.
Can I also check, if there is one calc in one cell I can click on the corner of that cell and drag down for a correct repetition of the formula down the page or do I need more than one cell with formulas i.e. 3 to copy and drag down a formula? It should save you sending a document if I can successfully copy and drag a formula down I should think.
Thanks in advance Barry!
Tracey
Can I also check, if there is one calc in one cell I can click on the corner of that cell and drag down for a correct repetition of the formula down the page or do I need more than one cell with formulas i.e. 3 to copy and drag down a formula? It should save you sending a document if I can successfully copy and drag a formula down I should think.
Thanks in advance Barry!
Tracey
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's it! Time format did the trick. Thanks SO much legend.
Tracey
Tracey
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for savagetracey's comment #a40699467
for the following reason:
Simple solution. Quick response. Wonderful.
Accepted answer: 0 points for savagetracey's comment #a40699467
for the following reason:
Simple solution. Quick response. Wonderful.
ASKER
Simple solution. Quick response. Wonderful.
Those results don't look correct to me, I'm assuming that for the first example - row 3 - you want to see 00:00:07 and 11, and for next row it should be 0:00:04 and 20
If that's right try this formula for E3
=IF(A3="","",C3-A3-(D3<B3)
and for F3
=IF(A3="","",MOD(D3-B3,25)
Those should work consistently for any values
I can't post an example right now ( I'm on the train!) but I can attach something later.
regards, barry