|
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 |
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 |
smallest
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 |
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 |
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 |
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 |
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 |
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 |
smallest
=MIN(IF(MOD(ROW(F7:F16),2)=0,If(F7:F16<0,F7:F16)) )
Charles RobcPettit wrote: 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 |
smallest
=MIN(IF((MOD(ROW(F7:F16),2)=0)*(F7:F16<0),F7:F16) )
one les IF <g -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Die_Another_Day" wrote in message oups.com... =MIN(IF(MOD(ROW(F7:F16),2)=0,If(F7:F16<0,F7:F16)) ) Charles RobcPettit wrote: 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 |
smallest
Thankyou both again. Much apprecieated.
Regards Robert Bob Phillips wrote: =MIN(IF((MOD(ROW(F7:F16),2)=0)*(F7:F16<0),F7:F16) ) one les IF <g -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Die_Another_Day" wrote in message oups.com... =MIN(IF(MOD(ROW(F7:F16),2)=0,If(F7:F16<0,F7:F16)) ) Charles RobcPettit wrote: 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 |
smallest
Hi, sorry but another question. Im having a problem with
=MATCH(B40,F8:F15,0)/2. 99 out of 100 it works. eg if my smallest number is 1 and in the first cell, then it return 1. If its in the second cell it returns 1.5. The problem is that Im ignoring every other cell with =MIN(IF((MOD(ROW(F8:F15),2)=0)*(F8:F15<0),F8:F15) ). So if I have 1 in cell 2 and 1 in cell 3 the result I get is 1.5 but I want cell 3 giving me 2. I hope this makes sense. Regards Robert |
smallest
Rob,
Does this do it =MATCH(H9,F8:F17,0)/2+(ISEVEN(F8)/2) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RobcPettit" wrote in message ups.com... Hi, sorry but another question. Im having a problem with =MATCH(B40,F8:F15,0)/2. 99 out of 100 it works. eg if my smallest number is 1 and in the first cell, then it return 1. If its in the second cell it returns 1.5. The problem is that Im ignoring every other cell with =MIN(IF((MOD(ROW(F8:F15),2)=0)*(F8:F15<0),F8:F15) ). So if I have 1 in cell 2 and 1 in cell 3 the result I get is 1.5 but I want cell 3 giving me 2. I hope this makes sense. Regards Robert |
smallest
Bob, tried the formula, came back with a name error. playing around
with it though. Regards Robert Bob Phillips wrote: Rob, Does this do it =MATCH(H9,F8:F17,0)/2+(ISEVEN(F8)/2) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RobcPettit" wrote in message ups.com... Hi, sorry but another question. Im having a problem with =MATCH(B40,F8:F15,0)/2. 99 out of 100 it works. eg if my smallest number is 1 and in the first cell, then it return 1. If its in the second cell it returns 1.5. The problem is that Im ignoring every other cell with =MIN(IF((MOD(ROW(F8:F15),2)=0)*(F8:F15<0),F8:F15) ). So if I have 1 in cell 2 and 1 in cell 3 the result I get is 1.5 but I want cell 3 giving me 2. I hope this makes sense. Regards Robert |
smallest
Bob, I didnt have the analysis add in, installed. Comes back with same
result as original formula. Regards Robert RobcPettit wrote: Bob, tried the formula, came back with a name error. playing around with it though. Regards Robert Bob Phillips wrote: Rob, Does this do it =MATCH(H9,F8:F17,0)/2+(ISEVEN(F8)/2) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RobcPettit" wrote in message ups.com... Hi, sorry but another question. Im having a problem with =MATCH(B40,F8:F15,0)/2. 99 out of 100 it works. eg if my smallest number is 1 and in the first cell, then it return 1. If its in the second cell it returns 1.5. The problem is that Im ignoring every other cell with =MIN(IF((MOD(ROW(F8:F15),2)=0)*(F8:F15<0),F8:F15) ). So if I have 1 in cell 2 and 1 in cell 3 the result I get is 1.5 but I want cell 3 giving me 2. I hope this makes sense. Regards Robert |
smallest
Post example data.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RobcPettit" wrote in message oups.com... Bob, I didnt have the analysis add in, installed. Comes back with same result as original formula. Regards Robert RobcPettit wrote: Bob, tried the formula, came back with a name error. playing around with it though. Regards Robert Bob Phillips wrote: Rob, Does this do it =MATCH(H9,F8:F17,0)/2+(ISEVEN(F8)/2) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RobcPettit" wrote in message ups.com... Hi, sorry but another question. Im having a problem with =MATCH(B40,F8:F15,0)/2. 99 out of 100 it works. eg if my smallest number is 1 and in the first cell, then it return 1. If its in the second cell it returns 1.5. The problem is that Im ignoring every other cell with =MIN(IF((MOD(ROW(F8:F15),2)=0)*(F8:F15<0),F8:F15) ). So if I have 1 in cell 2 and 1 in cell 3 the result I get is 1.5 but I want cell 3 giving me 2. I hope this makes sense. Regards Robert |
smallest
Thanks for your reply Bob, been away for a couple of days, whence delay
in reply. my data is: 3 2.4 2.4 1048 3.98 500 3.98 1048 So with this data the index for the smalles returns 1.5, were as Im after 2. This is not a major problem as the 2nd,4th,6th, and 8th values rarly drop below 100, but Im trying to anticipate that 1 occasion when it does. In this data Im, starting at 3, I only want everyother. So 3,2.4,3.98 and 3.98. Regards Robert |
| All times are GMT +1. The time now is 12:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com