Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pete
 
Posts: n/a
Default Calculate Minimum numbers

I have a need to calculate the 3 smallest numbers in a set of 7. I have tried
the min function but this only gives me one number. The nubers are listed in
(B3:B10) my formula is in (B12). Some cells can be blank and some or a
negitive, while a third senerio would be that you could have repeatitive #'s
(I.e -13,_,_,2,_,-2,-13).

Any thoughts
--
Pete
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=SMALL($B$3:$B$10,1)
=SMALL($B$3:$B$10,2)
=SMALL($B$3:$B$10,3)

--
HTH

Bob Phillips

"Pete" wrote in message
...
I have a need to calculate the 3 smallest numbers in a set of 7. I have

tried
the min function but this only gives me one number. The nubers are listed

in
(B3:B10) my formula is in (B12). Some cells can be blank and some or a
negitive, while a third senerio would be that you could have repeatitive

#'s
(I.e -13,_,_,2,_,-2,-13).

Any thoughts
--
Pete



  #3   Report Post  
N Harkawat
 
Posts: n/a
Default

Smallest 3 numbers if you have unique (no repeats)
= small(b3:b10,1)
= small(b3:b10,2)
= small(b3:b10,3)

However if you have numbers repeat then

Smallest
= min(b3:b10)

2nd smallest
=min(if(b3:b10small(b3:b10,2),b3:b10))
array entered (ctrl+shift+enter)

3rd smallest
=min(if(b3:b10small(b3:b10,2),b3:b10))
array entered (ctrl+shift+enter)


"Pete" wrote in message
...
I have a need to calculate the 3 smallest numbers in a set of 7. I have
tried
the min function but this only gives me one number. The nubers are listed
in
(B3:B10) my formula is in (B12). Some cells can be blank and some or a
negitive, while a third senerio would be that you could have repeatitive
#'s
(I.e -13,_,_,2,_,-2,-13).

Any thoughts
--
Pete



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Calculate In-Between Numbers Brenda Rueter Excel Worksheet Functions 7 May 10th 05 04:07 PM
How do I automatically calculate YTD numbers by changing a date? MDSistah Excel Worksheet Functions 1 April 29th 05 05:52 PM
Match Last Occurrence of two numbers and Return Date Sam via OfficeKB.com Excel Worksheet Functions 6 April 5th 05 12:40 PM
Match Last Occurrence of two numbers and Count to Previous Occurence Sam via OfficeKB.com Excel Worksheet Functions 33 April 4th 05 02:17 PM
Formula to calculate only the negative numbers Dawn Boot-Bunston Excel Worksheet Functions 5 November 24th 04 10:57 PM


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

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

About Us

"It's about Microsoft Excel"