Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a formula that finds the smallest number that occurs twice in a column?
I have a spreadsheet with a lengthy list of numbers. I need to find
the smallest number that occurs twice. I also need to find the smallest number that occurs three times, etc. Is there a way to do this with a formula? I don't mind running a macro to do this, but I would need help with that as well. Placing the answer in a msgbox is fine with me. thanks in advance. jasonk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a formula that finds the smallest number that occurs twice in a column?
Try this Array formula (validate with Ctrl+Shift+Enter)
=MIN(A1:A999+IF(COUNTIF(A1:A999,A1:A999)=2,0,1E+23 9)) HTH -- AP "JasonK" a écrit dans le message de ... I have a spreadsheet with a lengthy list of numbers. I need to find the smallest number that occurs twice. I also need to find the smallest number that occurs three times, etc. Is there a way to do this with a formula? I don't mind running a macro to do this, but I would need help with that as well. Placing the answer in a msgbox is fine with me. thanks in advance. jasonk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a formula that finds the smallest number that occurs twice in a column?
=MIN(IF(COUNTIF(A1:A20,A1:A20)=2,A1:A20))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. To change the number of occurrence, change the =2 value. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JasonK" wrote in message ... I have a spreadsheet with a lengthy list of numbers. I need to find the smallest number that occurs twice. I also need to find the smallest number that occurs three times, etc. Is there a way to do this with a formula? I don't mind running a macro to do this, but I would need help with that as well. Placing the answer in a msgbox is fine with me. thanks in advance. jasonk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a formula that finds the smallest number that occurs twice in a column?
=MIN(999,FALSE) returns 0, but your formula works as expected!
Regards, -- AP |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a formula that finds the smallest number that occurs twice in a column?
Both formulas worked great when i altered the range to my sheet.
thanks guys. i wish i could learn this stuff quicker. jasonk unreal. On Sat, 22 Apr 2006 11:15:04 +0200, "Ardus Petus" wrote: Try this Array formula (validate with Ctrl+Shift+Enter) =MIN(A1:A999+IF(COUNTIF(A1:A999,A1:A999)=2,0,1E+2 39)) HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count number of instances a number occurs | Excel Worksheet Functions | |||
Count the number of times a name occurs in a column | Excel Worksheet Functions | |||
The formula to find the smallest number in a row not equal to zero | Excel Worksheet Functions | |||
Which @function finds the mean of a number? | Excel Worksheet Functions | |||
Find the smallest number in a column and change it to red | Excel Programming |