# Thread: Help with Table (Excel 2002/SP3)

1. 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.

2. What does your Raw data look like?

3. 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.

4. I'm not seeing the connection between the results of the formula and the desired outcome table.

5. 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.

6. 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

7. Thanks for all the help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•