Simpler Formula ... 2nd lowest value?

K

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

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
 
K

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
 
S

Scott

Alternatively:

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

Scott
 
D

driller

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top