How to control individual functions in excel chart?

Excel

• I am working with2007 and I have workbook that I am trying to copy the contents from a cell on one sheet to the cell of another sheet in the same workbook based on the input of multiple cells, 3 to be exact. For Example: On sheet one of my worksheet I have to input a pipe type (cell B6), pipe schedule (cell B7), & pipe OD (cell B8. Based on my input on these 3 cells, I need to pull the pipe ID from a particular cell in a chart that I made on sheet 2. I would like to know if there is a function or a series of functions that I can use to do this or would the use of a macro be better suited. Macros are not my strong suit, so any help you could lend would be most appreciated. Hi there we have a chart where we are using the FV formula. in colum B we have the rates ie b4=5% b5=5.5% b6=6%, in cell A2 we have the payment of 2000 when we do the forumla in C4 everything is great, we are suppose to copy the formula down to complete the chart for each of the different percent. When we click on the node on he bottom right of the C4 cell and move it down, the new formulas are messed up, because instead of keeping the A2 for the payment - the formula changes to A3(payment)for the formula in C5... and A4 for the formula in C6 etc. is there a way to stop them from changing and having the only one change in the formulas would be the rate cell b4 - to b5 to b6 etc??? Hi there we have a chart where we are using the FV formula. in colum B we have the rates ie b4=5% b5=5.5% b6=6%, in cell A2 we have the payment of 2000 when we do the forumla in C4 everything is great, we are suppose to copy the formula down to complete the chart for each of the different percent. When we click on the node on he bottom right of the C4 cell and move it down, the new formulas are messed up, because instead of keeping the A2 for the payment - the formula changes to A3(payment)for the formula in C5... and A4 for the formula in C6 etc. is there a way to stop them from changing and having the only one change in the formulas would be the rate cell b4 - to b5 to b6 etc??? I have a question & don't know which function I shoul use. On the infection rate sheet in column c write a formula to calculate the death rate which is defined as the number of deaths as a percentage of the total infected people. Reference the values on the HIV satatistics sheet in your formula QUESTION: Aiden- I have a column of numbers A:1 to A:1000, associated with a column of names B:1 to B:1000. I need to insert 6 blank rows between each number/name in the columns. How do I do this? ANSWER: Sorry for the delay in replying -this somehow got lost in the system (my system anyway!) - I would PROBABLY do this with a macro - though it's not clear why you need to do the insert Sub inserter() Dim mycell As Range, newcell As Range Set newcell = ActiveCell.Offset(1, 0) Dim looper For looper = 1 To 3 Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown newcell.Select Set newcell = ActiveCell.Offset(1, 0) Next End Sub should do it for you - put the cursor on the first cell you want moved down, also amend my 3 (for looper = 1 to 3) to whatever number you actually need to run it for. ---------- FOLLOW-UP ---------- QUESTION: Thanks. Not real familiar with macros but I'll give it a try. I need the inserts as the list I have are ID's and names. I need to insert spaces for start and end dates by quarters so each ID and name has a line for the 4 quarters in a year in order to input data for the quarter: ID Name Start Date End Date # Of Delinquencies 222 Smith 1/02/10 03/31/10 6 04/01/10 06/30/10 3 07/01/10 09/30/10 4 10/01/10 12/31/10 7 Hello Aidan Heritage How do I figure the number of hours worked from 8:30 AM - 3:00 PM, Minice 30 mins for lunch. I figured the Formula for total hours, but is there a way to mince the 30 mins for lunch? =TEXT(C5-B5, "h:mm")6.5 hours QUESTION: which time format should be used to record 1/100, 1/1000 of a second. Time recorded in stopwatches for any sport.(eg: Athletics, swimming...) ANSWER: right click the range to format cells, go to cusand then put hh:mm:ss.000 ---------- FOLLOW-UP ---------- QUESTION: How can we add time or subtract time in any time format in excel. is it possible to get the result in Xhrs:YMin:Zsec.Msec

• Answer:

  It can be done with a formula, but I need more info -- can you describe "pull the pipe ID from a particular cell in a chart that I made on sheet 2" in more detail? You can do a VLOOKUP(B6&B7&B8,...) or INDEX(...,MATCH(B6&B7&B8,...,0)) but I can't fill in the "..." without knowing more. Steve Excel has a great feature which is what they call relative referencing which is the default in excel. As you change formulas from one row or column to another the formula changes. As you probably know this is av ery useful feature. However, when you do not want a formula to change as you copy it Excel allows you to make any reference in the formula absolute, meaning it won't change as you move the formula around, so if you have a formula such as +a1+b1 and you copy it down one row if will change to +a2+b2 If you make the rows absolute, by putting a dollar sign in front of the row number, then when you copy it down one row it will read +a$1+b$1 as you see it didn't change because the row numbers were made absolute with the dollar sign. You can also put a dollar sign in front of the column to keep it from changing when you copy it. Using the absolute reference in your FV formula (the dollar sign) should solve your problem. Hope thsi helps Yes, there is a way to answer your question. Assuming you also want to know HOW to do this then just make the cell references that refer to B4, B5, and B6 be absolute references. So, instead of seeing or having or using B4 in the cell where your FV formula is, then use $B$4 in your FV formula, using just B4 as an example Sara I can't give you an exact formula since I don't know what your worksheet looks like, but--- In general you need to divide the number of deaths by the total infected people to get the death rate percentage. so if total infected is in cell A1 and total deaths is in cell B1 then the formula is +B1/+A1 If you do not have totals for the number infected and the number of deaths then you will need to sum those to get the totals. Again not knowing what your worksheet looks like I can't be very specific with what is needed. Thanks for that - I wonder if this is needed this way at all - presumably you have a full list of names, and elsewhere a list of delinquency dates? If so, a set of formulas could be used to produce the data you need per quarter? Happy to help further in any way with this - if it helps, my email is [email protected] I like your formula, but it's over complicated =c5-b5-(30/60/24) and have the cell formatted as [h]:mm (technically h:mm would be fine, but the square brackets forceto display times greater than 24 hours as HOURS and not days and hours. 30 divided by 60 divided by 24 converts 30 minutes into a decimal fraction, which is the way thathandles dates and times. I don't know what you quite mean by Xhrs:YMin:Zsec.Msec Working with arithmetic + times/dates incan get very complicated. You should use the infomation on this page:http://www.cpearson.com/excel/datearith.htm Because even if we work it out here, you will likely have further questions. Most of that, I definitely don't have in my head ... I also consult pages like this http://www.cpearson.com/excel/datearith.htm