Ask Your Question

Calculating the remaining time for each DVD

asked 2015-11-01 14:35:27 +0200

ALOmgb41238 gravatar image

updated 2020-11-18 22:39:00 +0200

LeroyG gravatar image

I have created a list of all my recordings on DVDs. As I will have more than 99 Titles, I have set the DVD № in Cx to 000. I need to show the remaining time, so that I can easily find the DVDs with space for more recordings. Each side has 6 hours of recording.

With the recordings listed in the column Ax, the DVD № are in the column Cx, starting with C(1) = "001". The number of C-cells containing "001" varies w.r.t. to number of recordings on this first DVD. Similarly, for all subsequent C-cells. Therein lies my problem in calculating the Remaining Time for each DVD. I currently have the following entries for Cx:

C(2)=001; C(3)=001; C(4)=001; C(5)=002; C(6)=002; C(7)=003;

C(8)=003; C(9)=003; C(10)=003; C(11)=003; C(12)=004.

I started writing a nested IF-statement, but soon realised this would be enormous and prone to error: =IF(C2=C3;IF(C3=C4;IF(C4=C5;D4=0;D4=C4);D3=C3);D2=C2)

I really have run out of steam, and would appreciate any assistance offered. Surely, there must be somebody having had a similar task. Is there a means of incrementing C(X) until its content changes - i.e. "001" until "002"? Thank you.

edit retag flag offensive close merge delete


Original question title: LO, EN (BR), Calc >> Ubuntu 14.04 & Win XP

LeroyG gravatar imageLeroyG ( 2020-11-18 22:38:44 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-11-18 22:33:51 +0200

LeroyG gravatar image

updated 2020-11-18 22:34:52 +0200

D2: =IF(C2=C1;D1+B2;B2)

E2: =IF(C2<>C3;0.25-D2;"")

Applied Data Bar format to columns D and E. Use menu Format - Conditional - Manage… to edit.

DVD Remaining Time

See sample file.

Made with LibreOffice (x86); OS: Windows 6.1.

Add Answer is reserved for solutions.

If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information.

Check the mark (Answer markCorrect answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-11-01 14:35:27 +0200

Seen: 96 times

Last updated: Nov 18 '20