If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autofill Charting Help
I have 581 points of data that were tested over 30 hours. I only know the
first test result (0 hours) and the last test result (28.9 hours). Is there a way to auto-fill the 498 fields in between? Also, I would like the X axis to display in 2 hour increments. Any help would be greatly appreciated. |
#2
|
|||
|
|||
Autofill Charting Help
Donald Franklin wrote:
I have 581 points of data that were tested over 30 hours. I only know the first test result (0 hours) and the last test result (28.9 hours). Is there a way to auto-fill the 498 fields in between? Also, I would like the X axis to display in 2 hour increments. Any help would be greatly appreciated. I'm confused. You have 581 data points, and you know the values of data points 1 and 581. Then you say there are 498 fields in between. That doesn't add up. I also don't understand how to fill in test results for all of the missing test results. Do you want a linear interpolation between the endpoints? Or is it possible that you have a set of 500 or so test results and you want Excel to figure out the time at which each data point was taken? Was the time for each data point evenly spaced, i.e. every 3 minutes? Dave dvt at psu dot edu |
#3
|
|||
|
|||
Autofill Charting Help
Don -
I have a simple macro that fills in a row or column with values between the first and last cell of the selection. I can fill by linear or logarithmic interpolation depending on a variable passed to the macro. '''------------------------------------------------------------ Sub LinFill() FillUp False End Sub '''------------------------------------------------------------ Sub LogFill() FillUp True End Sub '''------------------------------------------------------------ Sub FillUp(blFillType As Boolean) ''' given a selected range, horizontal or vertical, this routine ''' fills in interpolated values between the first and last cells ''' blFillType = False: on a LINEAR basis ''' blFillType = True: on a LOGARITHMIC basis Dim ActvSht As Worksheet, Slecshun As Range Dim TopRow As Integer, BottomRow As Integer, RowCt As Integer Dim LeftCol As Integer, RiteCol As Integer, ColCt As Integer Dim Counter As Integer Dim first As Double, Last As Double Set ActvSht = ActiveSheet Set Slecshun = Selection TopRow = Slecshun.Row RowCt = Slecshun.Rows.count BottomRow = RowCt + TopRow - 1 LeftCol = Slecshun.Column ColCt = Slecshun.Columns.count RiteCol = ColCt + LeftCol - 1 If Selection.Areas.count 1 Then MsgBox "Does not work on multiple selected areas." GoTo ExitRoutine End If If ColCt 1 And RowCt 1 Then MsgBox "Select a one-dimensional array of cells." GoTo ExitRoutine End If Select Case VarType(ActvSht.Cells(TopRow, LeftCol).Value) Case vbDouble first = ActvSht.Cells(TopRow, LeftCol) Case vbEmpty, vbString MsgBox "Invalid starting cell." GoTo ExitRoutine End Select Select Case VarType(ActvSht.Cells(BottomRow, RiteCol).Value) Case vbDouble Last = ActvSht.Cells(BottomRow, RiteCol) Case vbEmpty, vbString MsgBox "Invalid ending cell." GoTo ExitRoutine End Select If blFillType Then If first = 0 Or Last = 0 Then MsgBox "Logarithmic fill requires positive arguments." GoTo ExitRoutine End If End If If TopRow = BottomRow Then ''' horizontal If ColCt = 2 Then MsgBox "There are no cells to fill in." GoTo ExitRoutine Else For Counter = LeftCol + 1 To RiteCol - 1 Step 1 If blFillType Then ActvSht.Cells(TopRow, Counter) = _ Exp(Log(first) + (Log(Last) - Log(first)) * _ (Counter - LeftCol) / (RiteCol - LeftCol)) Else ActvSht.Cells(TopRow, Counter) = _ first + (Last - first) * _ (Counter - LeftCol) / (RiteCol - LeftCol) End If Next Counter End If ElseIf LeftCol = RiteCol Then ''' vertical If RowCt = 2 Then MsgBox "There are no cells to fill in." GoTo ExitRoutine Else For Counter = TopRow + 1 To BottomRow - 1 Step 1 If blFillType Then ActvSht.Cells(Counter, LeftCol) = _ Exp(Log(first) + (Log(Last) - Log(first)) * _ (Counter - TopRow) / (BottomRow - TopRow)) Else ActvSht.Cells(Counter, LeftCol) = _ first + (Last - first) * _ (Counter - TopRow) / (BottomRow - TopRow) End If Next Counter End If Else ''' Obsolete Else GoTo ExitRoutine End If ExitRoutine: Set ActvSht = Nothing Set Slecshun = Nothing End Sub '''------------------------------------------------------------ - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Donald Franklin wrote: I have 581 points of data that were tested over 30 hours. I only know the first test result (0 hours) and the last test result (28.9 hours). Is there a way to auto-fill the 498 fields in between? Also, I would like the X axis to display in 2 hour increments. Any help would be greatly appreciated. |
#4
|
|||
|
|||
Autofill Charting Help
Don -
Re your second question. Make sure you have made an XY chart, not a line chart. Enter your axis scale parameters as times (e.g., type 2:00 for maximum scale); Excel converts them to decimal equivalents so you don't have to. Then change the number format of the axis to something like h:mm. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Donald Franklin wrote: I have 581 points of data that were tested over 30 hours. I only know the first test result (0 hours) and the last test result (28.9 hours). Is there a way to auto-fill the 498 fields in between? Also, I would like the X axis to display in 2 hour increments. Any help would be greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|