+ Reply to Thread
Results 1 to 8 of 8

IF and OFFSET: how to OFFSET only if cell has a value in it (follows previous post)

  1. #1
    Registered User
    Join Date
    09-29-2008
    Location
    australia
    Posts
    25

    IF and OFFSET: how to OFFSET only if cell has a value in it (follows previous post)

    Hi Guys,

    I have a follow on question from a previous post:

    (http://www.excelforum.com/excel-gene...drag-down.html)

    I have attached the .xls file.

    The basic setup is as follows:

    (1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)

    (2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.

    For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc

    As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.

    (3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.

    NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.

    OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.

    I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.

    HOWEVER, what i WANT is the output as shown in columns Y-AF.

    So as you will notice, there are 2 problems:

    (1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.

    How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?

    (2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.

    Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile. I am open to more succinct solutions if you guys can help!

    Thanks again.
    Attached Files Attached Files
    Last edited by bubastisbastet; 11-03-2008 at 10:10 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-26-2008
    Location
    singapore
    Posts
    626
    I think this is what you need


    Please Login or Register  to view this content.
    Copy down

    Please Login or Register  to view this content.
    Copy down
    I need your support to add reputations if my solution works.


  3. #3
    Registered User
    Join Date
    09-29-2008
    Location
    australia
    Posts
    25

    95% solved....

    thank-you 'sglife' for your suggestions! -it most certainly solved my problems....except for 1 more thing.

    sorry, I didn't make it clear on my first post, but the other requirement I have is that I only want to display the results up until the very last entry in the "input A, B, C & D" columns.

    however, as shown in the attachment, i need to drag the formulae all the way down from rows 6 to 51, despite the fact that columns (Y/Z), (AA/AB), (AC/AD) & (AE/AF) are currently only partly filled for examples A-D, because the possibility exists that the value "1" will be chosen in one of the user inputs (cells B2, B3, B4 & B5), in which case the entire column(s) would then be filled with data.

    otherwise, in all other cases (eg, the examples shown in the attachment), I would just like blank cells returned.

    the original "IF" & "OFFSET" formulae solved this, but obviously didn't fulfill the other requirements that your formulae have.

    I have tried to merge the two with some success, but it still doesn't quite work properly, so I figure it's not the best way to go.

    I have attached a modified .xls file showing the incoporation of your formulae and the final result that I'd like to achieve.

    Finally, thanks to everyone who has had enough patience to post (to this & previous questions) and help this newb understand excel. I really appreciate it!!!
    Attached Files Attached Files
    Last edited by bubastisbastet; 10-31-2008 at 09:06 PM. Reason: forgot to add something

  4. #4
    Registered User
    Join Date
    09-29-2008
    Location
    australia
    Posts
    25

    95% solved...one more nagging problem!

    Bump no response

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    From a resource perspective I think you will find it far more efficient to store the key variables in helper cells thus avoiding the need to recalculate the same thing repetitively (and make copying formulae a little easier)

    Using your example I would have the following formulae:

    Y3: =LOOKUP(2^15,E6:E51,ROW(6:51))-6
    copied to AA3, AC3, AE3

    Z3: =B2
    AB3: =B3
    AD3: = B4
    AF3: = B5

    Formula in Y6 as array:

    =INDEX($D$1:$D$51,MIN(IF(E$6:E$51,ROW($6:$51))),1)
    copied to AA6, AC6, AE6

    Formula in Y7:Y51
    =IF(OR(N(Y6)>=Y$3,ISTEXT(Y6)),"",Y6+Z$3)
    copied to AA7:51, AC7:51, AE7:51

    Formula in Z6:Z51
    =IF(ISNUMBER(Y6),INDEX(F$6:F$51,Y6+1,1),"")
    copied to AB6:51, AD6:51, AF6:51
    Last edited by DonkeyOte; 11-03-2008 at 07:37 AM.

  6. #6
    Registered User
    Join Date
    09-29-2008
    Location
    australia
    Posts
    25
    Awwwwwesome! I managed to adapt your formulae to my 'real life' working example.

    Can you (or anyone else for that matter) please explain the syntax for the Y3 entry..? -what is the purpose of the "2^15" in the formula?

    MANY THANKS again to everyone for your invaluable advice.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    2^15 = 32768

    Using the above as a criteria value in the LOOKUP and given the nature of the values in your range the LOOKUP will always return the last numeric entry in the range (Excels binary search process) as no value in the range exceeds the criteria value ... more often people use the following number as a criteria rather than 2^15:

    9.99999999999999E+307

    This is because the former can be easily be exceeded whereas the latter can not... I opted for the former given the numbers in your range were relatively small.

    The same holds true for text values... so if you wanted to find the last text entry in a range you could use:

    =LOOKUP(REPT("z",255),A1:A100)

    REPT("z",255) builds a string of z's 255 characters long -- ie you won't find too many strings that exceed that criteria... as such the last string found in the range will be returned.

    It's a bit more complex than that in truth (ie above not necessarily the standard technical explanation!) but in layman terms that should help I think ?

  8. #8
    Registered User
    Join Date
    09-29-2008
    Location
    australia
    Posts
    25
    Got it, thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1