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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

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.



"Ken" wrote in message
...
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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

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:G1 2,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+C OUNTIF(C12:G12,MIN(C12:G12)))),SMALL(C12:G12,2)))

happy holidays

"Ken" wrote:

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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

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


"Bob Davison" wrote:

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.



"Ken" wrote in message
...
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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

Alternatively:

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

Scott

driller wrote:
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:G1 2,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+C OUNTIF(C12:G12,MIN(C12:G12)))),SMALL(C12:G12,2)))

happy holidays

"Ken" wrote:

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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

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

"Scott" wrote:

Alternatively:

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

Scott

driller wrote:
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:G1 2,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+C OUNTIF(C12:G12,MIN(C12:G12)))),SMALL(C12:G12,2)))

happy holidays

"Ken" wrote:

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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

Missed that detail. I hate when I do that.

=H12*IF(MAX(C12:G12)=MIN(C12:G12),MAX(C12:G12),SMA LL(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 wrote:
hi scott,
Ken's formula works even there are some "random" blank cells., if c12 is
blank, we got a problem..

"Scott" wrote:

Alternatively:

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

Scott

driller wrote:
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:G1 2,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+C OUNTIF(C12:G12,MIN(C12:G12)))),SMALL(C12:G12,2)))

happy holidays

"Ken" wrote:

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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

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



"Ken" wrote:

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


"Bob Davison" wrote:

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.



"Ken" wrote in message
...
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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

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.

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:G12MIN(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:G12MIN( C12:G12),0))),"Your
desired result here such as
MIN(C12:G12)",INDEX(C12:G12,MATCH(TRUE,C12:G12MIN (C12:G12),0)))}


"Bob Davison" wrote in message
...
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.



"Ken" wrote in message
...
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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

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" wrote in message
...
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:G12MIN(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:G12MIN( C12:G12),0))),"Your
desired result here such as
MIN(C12:G12)",INDEX(C12:G12,MATCH(TRUE,C12:G12MIN (C12:G12),0)))}


"Bob Davison" wrote in message
...
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.



"Ken" wrote in message
...
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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Thanks ... Kha

How about this one?

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

"Harlan Grove" wrote in message
ups.com...
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.

Good morning Board ...

Thanks "all" for your assistance ... I am trying a few of your suggestions
this am, some work & some appear to fail (Fail appears to be when I only have
1 value) ... I have indicated in your trailing replies which appear to Work &
Fail based on my application. (note: I did not post my entire formula as I
have other nested "IF" statements in the front) ... I just thought perhaps my
formula could be written simpler (& it appears it can be). In addition, I
had maxed out with "IF" statements & my formula only worked on 1-5 values.
Fortunately, my form is 1-5 values (20 rows) so this is not presently an
issue.

But for those of you still willing to plug a way ... :)

My Range ... (C12-G12)
My Frequency ... (H12)
2nd lowest value x Freq ... (I12) ... (My formula location)

My Range (C12:G12) contains 1, 2, 3, 4, or 5 random values & may be entered
in any cell within the Range ... Some values may repeat 1, 2, 3, 4, or 5
times.
In all cases (except where there is only 1 value ... or where all the values
are the same ... I need to return the 2nd lowest value ... If only 1 value or
if values all the same ... then I need to return this value) ... More
examples:

1-empty-empty-empty-empty ... Need value 1
empty-empty-1-empty-empty ... Need value 1
empty-1-empty-1-empty .......... Need value 1
1-1-2-empty-empty ................. Need value 2
1-2-3-4-5 ............................... Need value 2
2-3-3-4-empty ........................ Need value 3
3-3-2-1-empty ........................ Need value 2
3-2-3-2-4 ............................... Need value 3
3-3-empty-3-3 ........................ Need value 3
3-3-3-3-3 ............................... Need value 3
5-4-3-2-1 ............................... Need value 2
2-4-3-empty-empty ................. Need value 3
3-3-2-1-3 ............................... Need value 2

Above said ... So far ... I think this Formula from Harlan is it:

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

Hope this clarifies some of your questions ... & my "Thanks" again to "all"
for the help ... Kha

"Bob Davison" wrote:

How about this one?

=H12*LARGE(C12:G12,COUNTIF(C12:G12,""&MIN(C12:G12 ))) ... (Appears to Fail if only 1 value? ... Kha)

"Harlan Grove" wrote in message
ups.com...
Scott wrote...
...
=H12*IF(MAX(C12:G12)=MIN(C12:G12),MAX(C12:G12) ,
SMALL(C12:G12,COUNTIF(C12:G12,MIN(C12:G12))+1) ) ... (Oh Yes, this one appears to work ... Kha)
...

This could be simplified further.

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12 ))+1,COUNT(C12:G12))) ... (Yes, this one appears to work ... Kha)

or even

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12 ))+1,5)) ... (Appears to Fail if only 1 value ... Kha)

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 wrote...
How about this one?

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

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

you are always welcome..Ken,

i am just confused "you had requested a Simpler formula that will suffice
the result of your original formula"
I agree that Harlan may have the simplest formula...
please specify in english where is the simpler formula..

happy holidays...hohoho...

"Ken" wrote:

Good morning Board ...

Thanks "all" for your assistance ... I am trying a few of your suggestions
this am, some work & some appear to fail (Fail appears to be when I only have
1 value) ... I have indicated in your trailing replies which appear to Work &
Fail based on my application. (note: I did not post my entire formula as I
have other nested "IF" statements in the front) ... I just thought perhaps my
formula could be written simpler (& it appears it can be). In addition, I
had maxed out with "IF" statements & my formula only worked on 1-5 values.
Fortunately, my form is 1-5 values (20 rows) so this is not presently an
issue.

But for those of you still willing to plug a way ... :)

My Range ... (C12-G12)
My Frequency ... (H12)
2nd lowest value x Freq ... (I12) ... (My formula location)

My Range (C12:G12) contains 1, 2, 3, 4, or 5 random values & may be entered
in any cell within the Range ... Some values may repeat 1, 2, 3, 4, or 5
times.
In all cases (except where there is only 1 value ... or where all the values
are the same ... I need to return the 2nd lowest value ... If only 1 value or
if values all the same ... then I need to return this value) ... More
examples:

1-empty-empty-empty-empty ... Need value 1
empty-empty-1-empty-empty ... Need value 1
empty-1-empty-1-empty .......... Need value 1
1-1-2-empty-empty ................. Need value 2
1-2-3-4-5 ............................... Need value 2
2-3-3-4-empty ........................ Need value 3
3-3-2-1-empty ........................ Need value 2
3-2-3-2-4 ............................... Need value 3
3-3-empty-3-3 ........................ Need value 3
3-3-3-3-3 ............................... Need value 3
5-4-3-2-1 ............................... Need value 2
2-4-3-empty-empty ................. Need value 3
3-3-2-1-3 ............................... Need value 2

Above said ... So far ... I think this Formula from Harlan is it:

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

Hope this clarifies some of your questions ... & my "Thanks" again to "all"
for the help ... Kha

"Bob Davison" wrote:

How about this one?

=H12*LARGE(C12:G12,COUNTIF(C12:G12,""&MIN(C12:G12 ))) ... (Appears to Fail if only 1 value? ... Kha)

"Harlan Grove" wrote in message
ups.com...
Scott wrote...
...
=H12*IF(MAX(C12:G12)=MIN(C12:G12),MAX(C12:G12) ,
SMALL(C12:G12,COUNTIF(C12:G12,MIN(C12:G12))+1) ) ... (Oh Yes, this one appears to work ... Kha)
...

This could be simplified further.

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12 ))+1,COUNT(C12:G12))) ... (Yes, this one appears to work ... Kha)

or even

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12 ))+1,5)) ... (Appears to Fail if only 1 value ... Kha)

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.

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.

"Harlan Grove" wrote

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

i am now confused..

i hope Ken can justify his ?thread and explain the post' title in excel's
english.

"Bob Davison" wrote:


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.

"Harlan Grove" wrote

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

Driller ... (Good morning)

1: You were confused from your original post (you stated so) ... Now
imagine how I felt trying to create my original formula (but it did work) ...
:)

2: Excel's English? ... Do not be too philisophical regarding the Post
Title I used (Simpler Formula ... 2nd Lowest Value?) ... I am only an Excel
Hack & that's why I come to the highly respected Wizards of these boards for
the Solutions to my Excel shortcomings (note: I have many.).

3: Now as an Excel Hack ... My Post Tilte makes just as much sense to me as
the Formula ... =SMALL(C12-G12,2) returning the value 1 when the evaluated
values are 1-1-2-3-2 ... In my mind anyway ... the 2nd Lowest value here is 2
(not 1 as returned by this formula).

4: Bottom line ... Thanks to all of you in this thread kicking the dust
around this issue ... I did find a simpler (at least shorter) solution to the
Formula I orignally posted ... The point of these Boards exactly.

5: In simple English ... this means my 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:G1 2,4)*H12,(MAX(C12:G12)*H12)))))

Will ultimately be replaced with by the simpler (a least shorter Formula):

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12 ))+1,COUNT(C12:G12))) ...
(Compliments of Harlan Grove)

Hope this helps to clarify & resolve some of your confusion ...

My Thanks to all ... Have a Happy & Safe Holiday Season ... Kha













"driller" wrote:

you are always welcome..Ken,

i am just confused "you had requested a Simpler formula that will suffice
the result of your original formula"
I agree that Harlan may have the simplest formula...
please specify in english where is the simpler formula..

happy holidays...hohoho...

"Ken" wrote:

Good morning Board ...

Thanks "all" for your assistance ... I am trying a few of your suggestions
this am, some work & some appear to fail (Fail appears to be when I only have
1 value) ... I have indicated in your trailing replies which appear to Work &
Fail based on my application. (note: I did not post my entire formula as I
have other nested "IF" statements in the front) ... I just thought perhaps my
formula could be written simpler (& it appears it can be). In addition, I
had maxed out with "IF" statements & my formula only worked on 1-5 values.
Fortunately, my form is 1-5 values (20 rows) so this is not presently an
issue.

But for those of you still willing to plug a way ... :)

My Range ... (C12-G12)
My Frequency ... (H12)
2nd lowest value x Freq ... (I12) ... (My formula location)

My Range (C12:G12) contains 1, 2, 3, 4, or 5 random values & may be entered
in any cell within the Range ... Some values may repeat 1, 2, 3, 4, or 5
times.
In all cases (except where there is only 1 value ... or where all the values
are the same ... I need to return the 2nd lowest value ... If only 1 value or
if values all the same ... then I need to return this value) ... More
examples:

1-empty-empty-empty-empty ... Need value 1
empty-empty-1-empty-empty ... Need value 1
empty-1-empty-1-empty .......... Need value 1
1-1-2-empty-empty ................. Need value 2
1-2-3-4-5 ............................... Need value 2
2-3-3-4-empty ........................ Need value 3
3-3-2-1-empty ........................ Need value 2
3-2-3-2-4 ............................... Need value 3
3-3-empty-3-3 ........................ Need value 3
3-3-3-3-3 ............................... Need value 3
5-4-3-2-1 ............................... Need value 2
2-4-3-empty-empty ................. Need value 3
3-3-2-1-3 ............................... Need value 2

Above said ... So far ... I think this Formula from Harlan is it:

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

Hope this clarifies some of your questions ... & my "Thanks" again to "all"
for the help ... Kha

"Bob Davison" wrote:

How about this one?

=H12*LARGE(C12:G12,COUNTIF(C12:G12,""&MIN(C12:G12 ))) ... (Appears to Fail if only 1 value? ... Kha)

"Harlan Grove" wrote in message
ups.com...
Scott wrote...
...
=H12*IF(MAX(C12:G12)=MIN(C12:G12),MAX(C12:G12) ,
SMALL(C12:G12,COUNTIF(C12:G12,MIN(C12:G12))+1) ) ... (Oh Yes, this one appears to work ... Kha)
...

This could be simplified further.

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12 ))+1,COUNT(C12:G12))) ... (Yes, this one appears to work ... Kha)

or even

=H12*SMALL(C12:G12,MIN(COUNTIF(C12:G12,MIN(C12:G12 ))+1,5)) ... (Appears to Fail if only 1 value ... Kha)

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.