# Simpler Formula ... 2nd lowest value?

G

#### Guest

Excel2003 ... I have from 1-5 random numbers (format 2 decimal places). In a
separate column (Col I) I wish to always have the 2 lowest value from the
random numbers ... The following formula achieves this for me, but I am
thinking there might be a simpler way of writing??? One clarifier ... In the
following Formula Col H contains a Qty which I wish to multiply the 2nd
lowest value in Range C12:G12 by.

=IF(MIN(C12:G12)=MAX(C12:G12),MAX(C12:G12)*H12,IF(SMALL(C12:G12,2)>MIN(C12:G12),SMALL(C12:G12,2)*H12,IF(SMALL(C12:G12,3)>MIN(C12:G12),SMALL(C12:G12,3)*H12,IF(SMALL(C12:G12,4)>MIN(C12:G12),SMALL(C12:G12,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

B

#### Bob Davison

Enter an array formula in any cell:

{=SMALL(C12:G12,2)} without the brackets. Press Ctrl+Shift+Enter after
typing the formula to get the array to work.

G

#### Guest

Hi kha

considering that any of your data will fit to your posted formula

=IF(MIN(C12:G12)=MAX(C12:G12),MAX(C12:G12)*H12,IF(SMALL(C12:G12,2)>MIN(C12:G12),SMALL(C12:G12,2)*H12,IF(SMALL(C12:G12,3)>MIN(C12:G12),SMALL(C12:G12,3)*H12,IF(SMALL(C12:G12,4)>MIN(C12:G12),SMALL(C12:G12,4)*H12,(MAX(C12:G12)*H12)))))

i try this a few times, whew, i am very very confused!

=H12*IF(MIN(C12:G12)=MAX(C12:G12),MAX(C12:G12),IF(COUNTIF(C12:G12,MIN(C12:G12))>1,SMALL(C12:G12,(1+COUNTIF(C12:G12,MIN(C12:G12)))),SMALL(C12:G12,2)))

happy holidays

G

#### Guest

Bob ... (Hi)

This SMALL formula appears to fail my application when there are repeating
values ... Example ... Values might be:

1-2-3-4-5 ... I need value 2 ... SMALL Formula returns value 2 ... Ok
1-1-2-3-4 ... I need value 2 ... SMALL Formula returns value 1 ... Fails
1-1-2-2-3 ... I need value 2 ... SMALL Formula returns value 1 ... Fails
1-1-4-1-1 ... I need value 4 ... SMALL Formula returns value 1 ... Fails

Note: When I say "Fail" ... I mean for my application ... I need 2nd lowest
value ... Hope this clarifies ... Do you now how to do this? ... Thanks ...
Kha

S

#### Scott

Alternatively:

=H12*IF(MAX(C12:G12)=MIN(C12:G12),C12,SMALL(C12:G12,COUNTIF(C12:G12,MIN(C12:G12))+1))

Scott

G

#### Guest

hi scott,
Ken's formula works even there are some "random" blank cells., if c12 is
blank, we got a problem..

S

#### Scott

Missed that detail. I hate when I do that.

=H12*IF(MAX(C12:G12)=MIN(C12:G12),MAX(C12:G12),SMALL(C12:G12,COUNTIF(C12:G12,MIN(C12:G12))+1))

His formula gives an #NUM error if there are less than 4 numbers, ie.
with 3 numbers, the SMALL(...,4) gives an error.

Scott

G

#### Guest

Hi ken,
please see my suggestion so i can learn also from you ..

H

#### Harlan Grove

Scott wrote...
....
=H12*IF(MAX(C12:G12)=MIN(C12:G12),MAX(C12:G12),
SMALL(C12:G12,COUNTIF(C12:G12,MIN(C12:G12))+1))
....

This could be simplified further.

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12))+1,COUNT(C12:G12)))

or even

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12))+1,5))

This begs the question whether the OP wants a numeric result or an
error if there's only one distinct numeric value in C12:G12.

B

#### Bob Davison

Here is another way to do it that takes care of the problem of equal cell
values (enter as an array):

{=INDEX(C12:G12,MATCH(TRUE,C12:G12>MIN(C12:G12),0))}

This returns an error if there is not at least one number greater than
another number within

An IF ISERROR can be used to force a result if all numbers are the same, all
cells are blank,
or there is only one number:

{=IF(ISERROR(INDEX(C12:G12,MATCH(TRUE,C12:G12>MIN(C12:G12),0))),"Your
desired result here such as
MIN(C12:G12)",INDEX(C12:G12,MATCH(TRUE,C12:G12>MIN(C12:G12),0)))}

B

#### Bob Davison

Guess I got ahead of myself.

The formulas below work great as long as the data is in ascending order.
Otherwise, the first number to be encountered greater than the lowest (MIN)
number will be returned.

3 would be returned for 2,3,5,22,57

22 would be returned for 2,22,3,5,57

My apologies...

B

#### Bob Davison

=H12*LARGE(C12:G12,COUNTIF(C12:G12,">"&MIN(C12:G12)))

H

#### Harlan Grove

Bob Davison wrote...

=H12*LARGE(C12:G12,COUNTIF(C12:G12,">"&MIN(C12:G12)))
....

If all values are equal, your COUNTIF call would return 0, in which
case your LARGE call would return an error.

B

#### Bob Davison

Very true, but could that be appropriate since there is not a second lowest
value?

I guess it would depend on what kind of output is desired in the case of all
equal values.