Simpler Formula ... 2nd lowest value?

[Ken]

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

 

[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.

 

[driller]

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

 

[Ken]

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

 

[Scott]

Alternatively:

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

Scott

 

[driller]

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

 

[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

 

[driller]

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

 

[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.

 

[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
your range.

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)))}

 

[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...

 

[Bob Davison]

How about this one?

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

 

[Harlan Grove]

Bob Davison wrote...

How about this one?

=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.

 

[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.