Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Simpler Formula ... 2nd lowest value?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Simpler Formula ... 2nd lowest value?
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 26
Default Simpler Formula ... 2nd lowest value?
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Ken is offline
external usenet poster
  
Posts: 590
Default Simpler Formula ... 2nd lowest value?
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 740
Default Simpler Formula ... 2nd lowest value?
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 26
Default Simpler Formula ... 2nd lowest value?
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




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 26
Default Simpler Formula ... 2nd lowest value?
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






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 740
Default Simpler Formula ... 2nd lowest value?
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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 87
Default Simpler Formula ... 2nd lowest value?
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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 740
Default Simpler Formula ... 2nd lowest value?
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


Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 87
Default Simpler Formula ... 2nd lowest value?
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



Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 733
Default Simpler Formula ... 2nd lowest value?
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.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Reusing formula Tony29 Excel Discussion (Misc queries) 7 September 7th 06 03:34 AM
Complex formula vs. simpler GerryK Excel Worksheet Functions 3 December 6th 05 03:33 PM
A SIMPLER FORMULA TO GET A REFERENCE OF WHICH COLUMN IS POPULATED Ravi Excel Worksheet Functions 7 July 15th 05 10:34 AM
Formula for displaying the lowest number of a range? coal_miner Excel Worksheet Functions 1 April 25th 05 02:54 PM
Formula to sum and list highest to lowest Brian Excel Worksheet Functions 1 December 11th 04 10:26 PM


All times are GMT +1. The time now is 05:12 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement