Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    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.
    Attached Files Attached Files

  2. #2
    Super Moderator WebGenii's Avatar
    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

  3. #3
    2 Star Lounger
    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.
    Attached Files Attached Files

  4. #4
    Super Moderator WebGenii's Avatar
    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

  5. #5
    2 Star Lounger
    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.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
  •