Results 1 to 8 of 8
Thread: need formula (97 SR2)

20030127, 18:02 #1
 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.

20030127, 18:59 #2
 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

20030127, 20:24 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 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

20030127, 20:32 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 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

20030127, 21:11 #5
 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.

20030127, 21:38 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 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

20030128, 20:44 #7
 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 nonzero 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 cheatminimum 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
=((cheatminimum)(properminimum))*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

20030128, 21:54 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: need formula (97 SR2)
Truth is I built mine in three steps; the nonzero Min, then the Match, then the Offset. Then I just cut & paste the nonzero 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