Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    need formula (97 SR2)

    The attached spreadsheet is a small portion of a much larger spreadsheet to analyze bids for compressed gasses.
    I need Ito create a formula that I can enter in Column AD that will find the min value greater than zero in columns S,V,Y and AB and then return the value in the cell immediately to that cells left. For example in row 10 it would return the value 91 from U10.

    I have tried nesting IF, AND, and MIN formulas but I can't get it right. Please help.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need formula (97 SR2)

    Alternately - a single formula for AD10 then filled down

    =(MIN(IF(S10=0,100,S10+R10/10000),IF(V10=0,100,V10+U10/10000),IF(AB10=0,100,AB10+AA10/10000))-MIN(IF(S10=0,100,S10),IF(V10=0,100,V10),IF(AB10=0, 100,AB10)))*10000

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: need formula (97 SR2)

    [Edit; sorry, I can't read instructions. The formula below is intended for a single cell, not to be copied down column AD.]

    This variation on DougKlippert's formula should do it, array entered:

    {=MIN(IF(S10:S15>0,S10:S15,FALSE),IF(V10:V15>0,V10 :V15,FALSE),IF(Y10:Y15>0,Y10:Y15,FALSE),IF(AB10:AB 15>0,AB10:AB15,FALSE))}
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: need formula (97 SR2)

    Hoping to atone for my error. In cell AD10, copied down:

    =MIN(IF(S10>0,S10,9^323),IF(V10>0,V10,9^323),,IF(Y 10>0,Y10,9^323),IF(AB10>0,AB10,9^323))
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need formula (97 SR2)

    OK John, almost there. Your formula had an error. a double "," in the middle of the equation I fixed that and now the equation reports the Minimum greater than zero. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    BUT, what I need is to report the value in the cell immediately to the left of the minimum.
    Here is your corrected formula: NOTE: CELL REFERENCES ARE CHANGED BECAUSE I MADE A MISTAKE IN MY ORIGINAL POST. THESE CELL REFERENCES ARE CORRECT.
    "=MIN(IF(S22>0,S22,9^323),IF(W22>0,W22,9^323),IF(A A22>0,AA22,9^323),IF(AE22>0,AE22,9^323))"
    Your formula returned the value of cell W22. I need it to report the value of the cell imediately to the left of W22 which is V22.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: need formula (97 SR2)

    Marc, I am working with your original posted spreadsheet; you'll have to correct the column references:

    =OFFSET(S10:AB10,0,MATCH(MIN(IF(S10>0,S10,9^323),I F(V10>0,V10,9^323),IF(Y10>0,Y10,9^323),IF(AB10>0,A B10,9^323)),S10:AB10,)-2,1,1)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: need formula (97 SR2)

    Marc

    I now understand that the correct columns are S, W, AA, and AE and that I wrote an unintelligible formula. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I'll try to address that now that I've had sufficient caffeine
    The problem was to find the minumum value of S,W,AA or AE and then return the cell to the left. Finding the minimum would have been easy

    = MIN(S10,W10,AA10,AE10)

    but the 'not zero' condition caused all responses to also test for that with an <font color=red>'IF(thing=0,bignumber,thing)'</font color=red> statement to make sure that 'thing' wasn't included as the minimum

    I liked John's solution here where bignumber was 9^323. So - the basic test becomes

    =MIN(IF(S10=0,9^323,S10),IF(W10=0,9^323,W10),IF(AA 10=0,9^323,AA10),IF(AE10=0,9^323,AE10))

    which will find the smallest non-zero result. At this point John's solution and mine diverged. John then 'looked' up the result and returned the cell offset one to the left. (I hadn't worked out how to do that)

    I cheated. <img src=/S/duck.gif border=0 alt=duck width=23 height=23> The cheat I used relied on the fact that your cells were values that were close in orders of magnitude. I 'carried' the value of the cell to the left of each cell by adding it on -

    e.g. for cell S10 the value I used was (S10 + R10/10000). If S10 is 0.33 and R10 is 90 then the result is 0.339
    Looking at it now - 10000 wasn't enough difference given you numbers were real numbers - lets use 10^10 so that the result becomes 0.3300000090

    The resulting cheat-minimum still returns the 'correct' minimum - but its carrying the value of the cell to the left. From there, its just maths to get the result - I subtracted the 'real' minimum and multiplied

    =((cheat-minimum)-(proper-minimum))*10^10

    sooooooo <img src=/S/compute.gif border=0 alt=compute width=40 height=20> the formula in full for row 10 is (should be all on one line in Excel)

    =(MIN(IF(S10=0,9^323,S10+R10/10^10),IF(W10=0,9^323,W10+V10/10^10),IF(AA10=0,9^323,AA10+Z10/10^10),IF(AE10=0,9^323,AE10+AD10/10^10))
    - MIN(IF(S10=0,9^323,S10),IF(W10=0,9^323,W10),IF(AA1 0=0,9^323,AA10),IF(AE10=0,9^323,AE10)))*10^10

    place that in a cell such as AF10 and copy it down to others.

    In terms of developing formulae like this - the easiest way is not to attempt to do them all at once - doing them via several columns often helps - e.g. my one above could easily be two columns
    John's could easily have been two (first returning the value, second doing the lookup). This is often a much easier way to debug what's going on. If only we could place comments in them <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Best of luck

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: need formula (97 SR2)

    Truth is I built mine in three steps; the non-zero Min, then the Match, then the Offset. Then I just cut & paste the non-zero Min into the Match, then the new Match into the Offset. I just hope Marc never gets a quote for something worth 9^323! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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