Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
match
Hi, can I modify the formula =MATCH(A33,F7:F15,0)/2 so that it only
give whole number results. Eg not 1.5,2.5,3.5 but 1,2,3. Regard Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
match
=int(yourformulahere)
is one way. RobcPettit wrote: Hi, can I modify the formula =MATCH(A33,F7:F15,0)/2 so that it only give whole number results. Eg not 1.5,2.5,3.5 but 1,2,3. Regard Robert -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
match
I'm going to have to ask why you're trying to divide the results of MATCH by
anything? MATCH does not return the value of a thing, it returns the position number that the match found in the list. You're getting the .5 when match finds the match at items 1, 3, 5, ... any odd number in your list. But to provide a solution to your question, try one of these, first one using INT will alway cause a value ending in .5 to become just the whole number portion (i.e. 1.5 = 1, 2.5 = 2, 3.5 = 3, etc) =INT(MATCH(A33,F7:F15,0)/2) This one will always round up to next larger integer, as 1.5 = 2, 2.5 = 3, etc. =ROUNDUP(MATCH(A33,F7:F15,0)/2,0) "RobcPettit" wrote: Hi, can I modify the formula =MATCH(A33,F7:F15,0)/2 so that it only give whole number results. Eg not 1.5,2.5,3.5 but 1,2,3. Regard Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
match
Thankyou for your replys. Im using =MIN(IF(MOD(ROW(F7:F16),2)=0,F7:F16)) to get the min value, as advised in prev post, and to get the index, use =MATCH(B40,F7:F16,0)/2 so Im trying to get the cottect position in the array, whence ignoring non whole values. Regards Robert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
match
Rob see my reply in the other thread
=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 oups.com... Hi, can I modify the formula =MATCH(A33,F7:F15,0)/2 so that it only give whole number results. Eg not 1.5,2.5,3.5 but 1,2,3. Regard Robert |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
match
It's due to the logic in his previous question.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JLatham" wrote in message ... I'm going to have to ask why you're trying to divide the results of MATCH by anything? MATCH does not return the value of a thing, it returns the position number that the match found in the list. You're getting the .5 when match finds the match at items 1, 3, 5, ... any odd number in your list. But to provide a solution to your question, try one of these, first one using INT will alway cause a value ending in .5 to become just the whole number portion (i.e. 1.5 = 1, 2.5 = 2, 3.5 = 3, etc) =INT(MATCH(A33,F7:F15,0)/2) This one will always round up to next larger integer, as 1.5 = 2, 2.5 = 3, etc. =ROUNDUP(MATCH(A33,F7:F15,0)/2,0) "RobcPettit" wrote: Hi, can I modify the formula =MATCH(A33,F7:F15,0)/2 so that it only give whole number results. Eg not 1.5,2.5,3.5 but 1,2,3. Regard Robert |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
match
Thanks - I'll go look for that just to see where this started from. I
figured that there might be a unique need to do it, and that's why I went ahead and tossed out a couple of possible solutions. I try to remember that sometimes someone may actually know what they're asking for even if it does look very much out of the ordinary. "Bob Phillips" wrote: It's due to the logic in his previous question. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "JLatham" wrote in message ... I'm going to have to ask why you're trying to divide the results of MATCH by anything? MATCH does not return the value of a thing, it returns the position number that the match found in the list. You're getting the .5 when match finds the match at items 1, 3, 5, ... any odd number in your list. But to provide a solution to your question, try one of these, first one using INT will alway cause a value ending in .5 to become just the whole number portion (i.e. 1.5 = 1, 2.5 = 2, 3.5 = 3, etc) =INT(MATCH(A33,F7:F15,0)/2) This one will always round up to next larger integer, as 1.5 = 2, 2.5 = 3, etc. =ROUNDUP(MATCH(A33,F7:F15,0)/2,0) "RobcPettit" wrote: Hi, can I modify the formula =MATCH(A33,F7:F15,0)/2 so that it only give whole number results. Eg not 1.5,2.5,3.5 but 1,2,3. Regard Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match 2 Columns, Return 3rd, Differing Match Types | Excel Worksheet Functions | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |