# Thread: need formula (97 SR2)

1. ## 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.

2. ## 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. ## 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))}

4. ## 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))

5. ## 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. ## 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)

7. ## 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),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. ## 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>

#### Posting Permissions

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