Results 1 to 7 of 7
Thread: Help with Table (Excel 2002/SP3)

20100611, 11:53 #1
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
I have the following table that can be changed by the user of the spreadsheet (sorry about the format here, I'm not sure how to format a table in this forum).
The table lists the time, on the left, that a certain event occured and on the right, the number of increments or steps that need to be evaluated between the specified times.
Time  No. of increments
0  2
30  2
60  2
120  3
300  1
600
For example, between times 0 and 30, there should be two events accounted for, and they are 15 and 30. Between times 120 to 300 there should be three events accounted for, and they are 180, 240 and 300.
I want to list all the events and the corresponding times in a seperate table. For this simple table I can do this quickly using simple hand calculations. The problem I have is that the Time and Number of Increments can change from one test to another, so I need to find a quicker way to do this.
I have also attached a spreadsheet showing what I'm trying to do. Is there a way to do such a task using equations rather than VBA?
Any help would be appriciated.

20100611, 15:41 #2
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
What does your Raw data look like?
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20100611, 16:01 #3
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
The intention of the data is to interpolate between fixed numbers. I modified the attached spreadsheet to show what I intend to do. I also found a way to add the time to the new table based on the number of increments. For example, the equation in cell B17 is:
=IF(A20<=$D$5, B19+($B$6$B$5)/$D$5, IF(AND(A20>$D$5, A20<=($D$5+$D$6)), B19+($B$7$B$6)/$D$6,
IF(AND(A20>($D$5+$D$6), A20<=($D$5+$D$6+$D$7)), B19+($B$8$B$7)/$D$7,
IF(AND(A20>($D$5+$D$6+$D$7), A20<=($D$5+$D$6+$D$7+$D$8)), B19+($B$9$B$8)/$D$8,
IF(AND(A20>($D$5+$D$6+$D$7+$D$8), A20<=($D$5+$D$6+$D$7+$D$8+$D$9)), B19+($B$10$B$9)/$D$9, "ERROR")))))
As you can see it's a long equation. I was wondering if there is a better way of doing the same thing.
Thanks.

20100611, 16:10 #4
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
I'm not seeing the connection between the results of the formula and the desired outcome table.
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20100611, 16:33 #5
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
When I modified the table for a better explanation, I forgot to erase the "Desired Output" table. I appologize for the confusion.
What I'm trying to do is to create an 'expanded' table based on the table in B510.
The updated spreadsheet is attached.

20100612, 07:23 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
In C17:
=IF(ISNA(MATCH(B17,$B$5:$B$10,0)),"",VLOOKUP(B17,$ B$5:$C$10,2,0))
Copy this down the column. [Note In C24 the formula gives a null, not a zero as you have in your example. I presume that the 0 is your error since 1950 is not in the range B5:B10...]
Steve

20100614, 09:50 #7
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks for all the help.