Help with Table (Excel 2002/SP3)

Topic

New Reply

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.

Reply | Quote

Replies

What does your Raw data look like?

Reply | Quote

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, A20($D$5+$D$6), A20($D$5+$D$6+$D$7), 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.

Reply | Quote

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

Reply | Quote

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 B5:D10.

The updated spreadsheet is attached.

Reply | Quote

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

Reply | Quote

Thanks for all the help.

Reply | Quote