Relative formula in Excel when inserting new rows

[Geates]

I have a table that has 4 columns: Start Time (A), End Time (B), Regular (C), and Overtime (D). I want columns C and D to be automatically populated based on what is entered in A and B. Here's the formula I have in cell C (assume row 1)

C1 = (hour(B1) + (minute(B1) / 60)) - (hour(A1) + (minute(A1) / 60))

This produces the correct result. Next I want to subtract .5 for a lunch break if C1 >= 8. This is how I do it.

C1 = if( ((hour(B1) + (minute(B1) / 60)) - (hour(A1) + (minute(A1) / 60)))
>= 8, (((hour(B1) + (minute(B1) / 60)) - (hour(A1) + (minute(A1) / 60)) - .5), = ((hour(B1) + (minute(B1) / 60)) - (hour(A1) + (minute(A1) / 60)) )

There's gotta be an easier way to write this. Perhaps assign an arbitrary cell the first forumla and use it in cell C?

cell Z1 = (hour(B1) + (minute(B1) / 60)) - (hour(A1) + (minute(A1) / 60))
cell C1 = if(Z >= 8, Z - .5, Z)

Then, I want to fill in cell D based on the results of cell C if C >= 8.

D1 = if((C1 - 8) >= 0, (C1 - 8), 0)

As I type, I'm slowing discovering solutions. Although, I'm still having an issue when inserting a new row. I thought that Excel uses relative formulas by default, meaning that as the cells change, so does the formula to accommodate. For instance, if I insert a new row, 2, I would expect the formula in the cell in row 2 to correspond to thos cell (ie. D2 = if((C2 - 8) >= 0, (C2 - 8), 0)). However, the formulas seem to be absolute as they don't change as I insert new rows; if fact, they are empty!

So I suppose my questions are:
A) How can I simplify these formulas?
B) How can I ensure newly inserted rows contain relative formulas?

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[SkipVought]

hi,

What version Excel?

If 2007+ then you can make your table a STRUCTURED TABLE, via Data > Tables > Table.

Formulas in the table propagate as you add rows.

I would NEVER use the INSERT method, especially if you reference other rows in your table. Rather add records at the bottom, and as I stated, the formula will propagate to the new row. THEN sort the data into the desired order. SOP for good spreadsheet design!

BTW, if 2003, then use the Data > List feature -- not quite as robust as Structured Tables, but formulas will propagate as new rows are added to table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

see if this works for cell C1

C1 = if ( HOUR(B1 - A1)>=8, HOUR(B1 - A1) + MINUTE(B1-A1)/60 - .5, HOUR(B1 - A1) + MINUTE(B1-A1)/60 )
[\code]

 

[SkipVought]

I am reading your post again and have questions:

1) why do you have DATA in row 1, when all proper tables should have a row of HEADINGS, followed by one or more rows of DATA?

2) you talk about inserting a row and then your formula(s), assuming that the row(s) containing forumlas are shifted downward, do NOT change to reflect the row in which they now reside. Your formula ought to start with an EQUAL sign, like
[tt]
=(hour(B1) + (minute(B1) / 60)) - (hour(A1) + (minute(A1) / 60))
[/tt]

3) however, your formula might be a Rube Goldberg solution, when a simple straitforward formula might be...
[tt]
=(B1-A1)*24
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Geates]

version is 2010.

1) Row one was just an example. There are headers and the data actually starts on row 7.

2)Yes, when I insert a row, the subsequent rows are shifted down. The new cells contain no forumlas. Also, all my formulas do start with an equal sign. Again, I included the cell name for example sake. I suppose I shouldn't; it causes confusion (especially in the VBS forum).

3)Good Point! Your formula is precision to the hour. I need it precise to the minute:
((B1-A1) * 1440) / 60. MUCH easier!

-Geates


"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[SkipVought]

1440/60 EQUALS 24!!!!!

1. do not INSERT!

2. use the Structured Tables feature!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Geates]

yes, I realized this soon after I posted .

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer