| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
smallest
Hi, in ranges f8:f15 i have
3.98 1463 3.98 1567 3.98 1572 4 2 What id like to do is look for the smallest value in every other cells. So the first cell = 3.98, next = 3.98 etc. At the moment I do, odds(1) = Range("f8") odds(2) = Range("f10") odds(3) = Range("f12") odds(4) = Range("f14") Count = 1000 For i = 1 To 4 If odds(i) < 0 Then If odds(i) < Count Then Count = odds(i) hand = i Range("a40") = hand Range("b40") = Count End If End If the hand value is which one of the odds() holds the value, to be used later. This works ok, but I was wondering if there a more efficient way. I though maybe a formula in the worksheet itself. Any ideas? Regards Robert |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
smallest
Here's an array formula that will work for you:
=SMALL(IF(MOD(ROW(A1:A10),2)=0,A1:A10,""),1) As this is an "Array" formula, after coping it into the cell you will need to press Ctrl+Shift+Enter instead of just Enter Charles Chickering RobcPettit wrote: Hi, in ranges f8:f15 i have 3.98 1463 3.98 1567 3.98 1572 4 2 What id like to do is look for the smallest value in every other cells. So the first cell = 3.98, next = 3.98 etc. At the moment I do, odds(1) = Range("f8") odds(2) = Range("f10") odds(3) = Range("f12") odds(4) = Range("f14") Count = 1000 For i = 1 To 4 If odds(i) < 0 Then If odds(i) < Count Then Count = odds(i) hand = i Range("a40") = hand Range("b40") = Count End If End If the hand value is which one of the odds() holds the value, to be used later. This works ok, but I was wondering if there a more efficient way. I though maybe a formula in the worksheet itself. Any ideas? Regards Robert |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
smallest
try this array formula which must be entered using ctrl+shift+enter
=SMALL(IF(MOD(ROW(A1:A7),2)=0,A1:A7),1) -- Don Guillett SalesAid Software "RobcPettit" wrote in message ups.com... Hi, in ranges f8:f15 i have 3.98 1463 3.98 1567 3.98 1572 4 2 What id like to do is look for the smallest value in every other cells. So the first cell = 3.98, next = 3.98 etc. At the moment I do, odds(1) = Range("f8") odds(2) = Range("f10") odds(3) = Range("f12") odds(4) = Range("f14") Count = 1000 For i = 1 To 4 If odds(i) < 0 Then If odds(i) < Count Then Count = odds(i) hand = i Range("a40") = hand Range("b40") = Count End If End If the hand value is which one of the odds() holds the value, to be used later. This works ok, but I was wondering if there a more efficient way. I though maybe a formula in the worksheet itself. Any ideas? Regards Robert |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
smallest
nuttin
-- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... What's wrong with just =MIN(IF(MOD(ROW(F7:F16),2)=0,F7:F16)) And to get the index, use =MATCH(B40,F7:F16,0)/2 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Don Guillett" wrote in message ... try this array formula which must be entered using ctrl+shift+enter =SMALL(IF(MOD(ROW(A1:A7),2)=0,A1:A7),1) -- Don Guillett SalesAid Software "RobcPettit" wrote in message ups.com... Hi, in ranges f8:f15 i have 3.98 1463 3.98 1567 3.98 1572 4 2 What id like to do is look for the smallest value in every other cells. So the first cell = 3.98, next = 3.98 etc. At the moment I do, odds(1) = Range("f8") odds(2) = Range("f10") odds(3) = Range("f12") odds(4) = Range("f14") Count = 1000 For i = 1 To 4 If odds(i) < 0 Then If odds(i) < Count Then Count = odds(i) hand = i Range("a40") = hand Range("b40") = Count End If End If the hand value is which one of the odds() holds the value, to be used later. This works ok, but I was wondering if there a more efficient way. I though maybe a formula in the worksheet itself. Any ideas? Regards Robert |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
smallest
He said small, so I assumed he might possibly need it.
Charles Bob Phillips wrote: What's wrong with just =MIN(IF(MOD(ROW(F7:F16),2)=0,F7:F16)) And to get the index, use =MATCH(B40,F7:F16,0)/2 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Don Guillett" wrote in message ... try this array formula which must be entered using ctrl+shift+enter =SMALL(IF(MOD(ROW(A1:A7),2)=0,A1:A7),1) -- Don Guillett SalesAid Software "RobcPettit" wrote in message ups.com... Hi, in ranges f8:f15 i have 3.98 1463 3.98 1567 3.98 1572 4 2 What id like to do is look for the smallest value in every other cells. So the first cell = 3.98, next = 3.98 etc. At the moment I do, odds(1) = Range("f8") odds(2) = Range("f10") odds(3) = Range("f12") odds(4) = Range("f14") Count = 1000 For i = 1 To 4 If odds(i) < 0 Then If odds(i) < Count Then Count = odds(i) hand = i Range("a40") = hand Range("b40") = Count End If End If the hand value is which one of the odds() holds the value, to be used later. This works ok, but I was wondering if there a more efficient way. I though maybe a formula in the worksheet itself. Any ideas? Regards Robert |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
smallest
Thanks for your replys. All of these a great. This has simplified my
code greatly. I like the match formula. Also its shown me to explore formulas more. Thanks to all. Regards Robert |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
smallest
MIN is SMALL(est)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Die_Another_Day" wrote in message oups.com... He said small, so I assumed he might possibly need it. Charles Bob Phillips wrote: What's wrong with just =MIN(IF(MOD(ROW(F7:F16),2)=0,F7:F16)) And to get the index, use =MATCH(B40,F7:F16,0)/2 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Don Guillett" wrote in message ... try this array formula which must be entered using ctrl+shift+enter =SMALL(IF(MOD(ROW(A1:A7),2)=0,A1:A7),1) -- Don Guillett SalesAid Software "RobcPettit" wrote in message ups.com... Hi, in ranges f8:f15 i have 3.98 1463 3.98 1567 3.98 1572 4 2 What id like to do is look for the smallest value in every other cells. So the first cell = 3.98, next = 3.98 etc. At the moment I do, odds(1) = Range("f8") odds(2) = Range("f10") odds(3) = Range("f12") odds(4) = Range("f14") Count = 1000 For i = 1 To 4 If odds(i) < 0 Then If odds(i) < Count Then Count = odds(i) hand = i Range("a40") = hand Range("b40") = Count End If End If the hand value is which one of the odds() holds the value, to be used later. This works ok, but I was wondering if there a more efficient way. I though maybe a formula in the worksheet itself. Any ideas? Regards Robert |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
smallest
Hi, the formula's saved me alot of time. I was wondering if the formula
can be adapted to ignore cells which contain a zero. Regards Robert |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Smallest positive value | Excel Discussion (Misc queries) | |||
| 1st smallest, 2nd smallest,3rd smallest | Excel Discussion (Misc queries) | |||
| Finding Smallest Value | Excel Discussion (Misc queries) | |||
| next smallest | Excel Discussion (Misc queries) | |||
| smallest | Excel Worksheet Functions | |||
![RobcPettit[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
