Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Remember Me

Are you a
Computer / IT professional?
Join Tek-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Just copy and paste the

#### Feedback

"...your web site's great! I've been using this system for almost a year now and find it really, really helpful. The people have been helpful in answering just about any question you post in the forums..."

#### Geography

Where in the world do Tek-Tips members come from?

# Relative formula in Excel when inserting new rows

 Forum Search FAQs Links Jobs Whitepapers MVPs
 Geates (Programmer) 5 Jun 12 9:20
 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 (Programmer) 5 Jun 12 9:26
 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 (TechnicalUser) 5 Jun 12 11:33
 see if this works for cell C1[code]C1 = if ( HOUR(B1 - A1)>=8, HOUR(B1 - A1) + MINUTE(B1-A1)/60 - .5, HOUR(B1 - A1) + MINUTE(B1-A1)/60 )[\code]
 SkipVought (Programmer) 5 Jun 12 11:58
 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=(hour(B1) + (minute(B1) / 60)) - (hour(A1) + (minute(A1) / 60))3) however, your formula might be a Rube Goldberg solution, when a simple straitforward formula might be...=(B1-A1)*24 Skip,Just traded in my old subtlety...for a NUANCE!
 Geates (Programmer) 5 Jun 12 13:00
 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 (Programmer) 5 Jun 12 13:12
 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 (Programmer) 5 Jun 12 14:40
 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

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!