Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
Hello,
I used conditional IF to write my formulas but when my table increase in size, it became many IF statements in a cell. I tried to replace IF with lookup function but some how I got so many ERR. Can someone write me the formulas to solve my issue? Here is the example: It can be a row or column but let assume it is a row A1, B1, C1, etc. Ie. A1 = -25, B1 = 0, and C1 = 31. We have another row consist of A7 to AA7 for example. Now we want to search through A7 to AA7 to find a value between A1 and B1 (from -25 to 0), B1 and C1 (from 0 to 31) etc... If it does found, it will display the found value in a cell in question. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
David, It is tough to do with a formula, I agree. LOOKUP() wants to return
the first value that is less than or equal to the lookup parameter, which can cause erroneous results. To expand your example, assume on row 7 that the following values exist beginning in column A (and I just took it out to K) -100 -50 -26 -10 0 10 20 30 41 82 90 =LOOKUP(A1,A7:AA7,A7:AA7) returns -26 which is wrong! you needed -10 to be returned (first number between -25 and 1) Even trying to get fancier, using a formula like =IF(AND(LOOKUP(A1,$A7:$AA7,$A7:$AA7)=A1,LOOKUP(A1 ,$A7:$AA7,$A7:$AA7)<=B1),LOOKUP(A1,$A7:$AA7,$A7:$A A7),LOOKUP(B1,$A7:$AA7,$A7:$AA7)) doesn't work properly, because while it returned 0 (which is between -25 and 0) it skipped -10 which would have been the first value that met the criteria. So, the solution I have come up with is a User Defined Function (UDF), which is simply VBA code that you can call from a formula in a cell just like any built-in Excel function. The code for it is below and to put it to work, you: open your workbook, press [Alt]+[F11] to open the VB editor; in the VB Editor choose Insert -- Module and copy the code below into the empty module presented to you and close the VB editor. Save the workbook. Now, when you need to find a number between 2 numbers you use the function as: =findfirstbetween(A1,B1,7) to find the first value on row 7 that is between the values in A1 and B1, or =findfirstbetween(B1,C1,7) to find the first value on row 7 that is between the values in B1 and C1. It's up to you to make sure that the numbers in the search list (row 7 in our examples) are in ascending order from left to right. And when you enter the cell address, the one with the smaller value should be entered first as we have been doing all along. Hope this helps some. Here is the code: Function FindFirstBetween(lowLimitCell As Range, _ highLimitCell As Range, searchRow As Long) As Variant 'INPUT: lowLimitCell = address of cell with lower limit value in it ' highLimitCell = address of cell with upper limit value in it ' searchRow = row number with values to be searched 'OUTPUT: "No Match" if no values in searchRow are ' between low/high limits, OR ' the FIRST value in searchRow that is: ' greater than or equal to lowLimitCell value, and is ' less than or equal to highLimitCell value. 'Call format in an Excel cell example: ' =FindFirstBetween(A1,B1,7) 'would return first value from row 7 that is between the 'values in A1 and B1 on the same sheet with the formula. ' Dim searchList As Range Dim anySearchEntry As Range 'have to find out what cells to search in the search row 'assumes that entries begin in column A and 'continue without a break (empty cell) to the end of 'the list to search on that row Set searchList = Range("A" & searchRow & ":" & _ Range("A" & searchRow).End(xlToRight).Address) FindFirstBetween = "No Match" For Each anySearchEntry In searchList If anySearchEntry = lowLimitCell And _ anySearchEntry <= highLimitCell Then FindFirstBetween = anySearchEntry Exit For End If Next Set searchList = Nothing ' housekeeping End Function "David" wrote: Hello, I used conditional IF to write my formulas but when my table increase in size, it became many IF statements in a cell. I tried to replace IF with lookup function but some how I got so many ERR. Can someone write me the formulas to solve my issue? Here is the example: It can be a row or column but let assume it is a row A1, B1, C1, etc. Ie. A1 = -25, B1 = 0, and C1 = 31. We have another row consist of A7 to AA7 for example. Now we want to search through A7 to AA7 to find a value between A1 and B1 (from -25 to 0), B1 and C1 (from 0 to 31) etc... If it does found, it will display the found value in a cell in question. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
Now, having given that long solution above, you do realize that if you used a
simple =LOOKUP(B1,$A7:$AA7,$A7:$AA7) to find the value between A1 and B1 would return a result that may be useful to you: the LAST number on row 7 that is <= B1 But I was assuming that you wanted the first number that met the "between" criteria when I wrote up the UDF. "David" wrote: Hello, I used conditional IF to write my formulas but when my table increase in size, it became many IF statements in a cell. I tried to replace IF with lookup function but some how I got so many ERR. Can someone write me the formulas to solve my issue? Here is the example: It can be a row or column but let assume it is a row A1, B1, C1, etc. Ie. A1 = -25, B1 = 0, and C1 = 31. We have another row consist of A7 to AA7 for example. Now we want to search through A7 to AA7 to find a value between A1 and B1 (from -25 to 0), B1 and C1 (from 0 to 31) etc... If it does found, it will display the found value in a cell in question. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
Many thanks for your assistance.
I seemed to have some issue in my 2003 excel. Our sample was from A7 to A18 (Use your sampling -100 to 90) So, your UDF would be: Set searchList = Range("A7" & searchRow & ":" & _ Range("A18" & searchRow).End(xlToRight).Address) Did I understand correctly? How the function can be call? It was seemed to me, that function returned only 0. What did I do wrong? I need all the answers, for instance in our sample, the result would be -10 in one sampling cell and 0 in other cell. If we test B1 to C1 side, we would have answer 0, 10, 20, and 30. Thanks. "JLatham" wrote: Now, having given that long solution above, you do realize that if you used a simple =LOOKUP(B1,$A7:$AA7,$A7:$AA7) to find the value between A1 and B1 would return a result that may be useful to you: the LAST number on row 7 that is <= B1 But I was assuming that you wanted the first number that met the "between" criteria when I wrote up the UDF. "David" wrote: Hello, I used conditional IF to write my formulas but when my table increase in size, it became many IF statements in a cell. I tried to replace IF with lookup function but some how I got so many ERR. Can someone write me the formulas to solve my issue? Here is the example: It can be a row or column but let assume it is a row A1, B1, C1, etc. Ie. A1 = -25, B1 = 0, and C1 = 31. We have another row consist of A7 to AA7 for example. Now we want to search through A7 to AA7 to find a value between A1 and B1 (from -25 to 0), B1 and C1 (from 0 to 31) etc... If it does found, it will display the found value in a cell in question. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
Did I understand correctly?
How the function can be call? It was seemed to me, that function returned only 0 rather than -10. What did I do wrong? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
Set searchList = Range("A7" & searchRow & ":" & _
Range("A18" & searchRow).End(xlToRight).Address) Sorry, I failed to check (x1 to the right). Fortunately, last night, I did also test your original version as well and I got the same result. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
Set searchList = Range("A7" & searchRow & ":" & _
Range("A18" & searchRow).End(xlToRight).Address) Sorry, I failed to check (x1 to the right). Fortunately, last night, I did also test your original version as well and I got the same result. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
David, We seem to have a disconnect here. What you're describing now is not
the way it was described in your original posting (OP). Here's what my UDF does: It takes the value passed to it in the first parameter as the lower limit to look for, and the second value passed to it as the upper limit. Now, here's the apparent disconnect: In your OP you said you wanted to find a match in ROW 7 in a list that occupied cells A7 through AA7. So the UDF assumes that the list to look through are in a single row that begins at column A (and it looks at A7 [7 coming from the entered formula] to the right for the end of the list. That is what this line of code sets up: Set searchList = Range("A" & searchRow & ":" & _ Range("A" & searchRow).End(xlToRight).Address) Your modification of that to Set searchList = Range("A7" & searchRow & ":" & _ Range("A18" & searchRow).End(xlToRight).Address) is probably what has confused things. If searchRow contains 7, then your formula actuall sets the range to A77 ("A7" with 7 appended to it) to A187 ("A18" with 7 appended to it) and where ever that ends (probably out in column IV, or IV187). So it's looking in the wrong places for the answers - range A77:IV187 is probably what is being searched, and they probably are all empty (zero). It kind of looks to me now that your list to search through is in a column from A7:A18 ?? Rather than in a row A7:AA7, or does each column with a value to find something between also have it's own list in rows farther on down the column? That is, could it be that a value in B1 has values to look through in B7:B18? I think the easiest thing to do now is for you to send me a sample of the workbook attached to an email sent to (remove spaces): Help From @ JLatham Site .com Remind me of this discussion, and in the email or on the Excel sheet, make notes about what you expect as results for the data you show on it. "David" wrote: Many thanks for your assistance. I seemed to have some issue in my 2003 excel. Our sample was from A7 to A18 (Use your sampling -100 to 90) So, your UDF would be: Set searchList = Range("A7" & searchRow & ":" & _ Range("A18" & searchRow).End(xlToRight).Address) Did I understand correctly? How the function can be call? It was seemed to me, that function returned only 0. What did I do wrong? I need all the answers, for instance in our sample, the result would be -10 in one sampling cell and 0 in other cell. If we test B1 to C1 side, we would have answer 0, 10, 20, and 30. Thanks. "JLatham" wrote: Now, having given that long solution above, you do realize that if you used a simple =LOOKUP(B1,$A7:$AA7,$A7:$AA7) to find the value between A1 and B1 would return a result that may be useful to you: the LAST number on row 7 that is <= B1 But I was assuming that you wanted the first number that met the "between" criteria when I wrote up the UDF. "David" wrote: Hello, I used conditional IF to write my formulas but when my table increase in size, it became many IF statements in a cell. I tried to replace IF with lookup function but some how I got so many ERR. Can someone write me the formulas to solve my issue? Here is the example: It can be a row or column but let assume it is a row A1, B1, C1, etc. Ie. A1 = -25, B1 = 0, and C1 = 31. We have another row consist of A7 to AA7 for example. Now we want to search through A7 to AA7 to find a value between A1 and B1 (from -25 to 0), B1 and C1 (from 0 to 31) etc... If it does found, it will display the found value in a cell in question. Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
JLatham:
Thanks for helping me out. I realied I made a mistake in mod your UDF. I also found the issue I have was due to excel itself. I found in some instances, excel did logical comparison wrong. In one instance, the output from lookup function was 20 and the other one was 450. In my IF statement, I wrote if cell with 20 was greater than cell with 450. I got the wrong answer. I plugged in number instead and it worked just fine. I think MS needs to redefine the lookup function. Now, I use a several lookup to get a several results and then I place them as value and use IF to redefine the result. It is a long way but somehow it works almost 90% of the times. Thanks for your help. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute IF statement.
Well, I'm glad my part of it ended up working. Remember that all LOOKUP
variants; LOOKUP, VLOOKUP and HLOOKUP always stop looking at the first match they make, so they can't see multiple possible matches in a list. Also, unless you specify the 4th parameter in VLOOKUP() and HLOOKUP() as ,FALSE) then the lists have to be in sequence. "David" wrote: JLatham: Thanks for helping me out. I realied I made a mistake in mod your UDF. I also found the issue I have was due to excel itself. I found in some instances, excel did logical comparison wrong. In one instance, the output from lookup function was 20 and the other one was 450. In my IF statement, I wrote if cell with 20 was greater than cell with 450. I got the wrong answer. I plugged in number instead and it worked just fine. I think MS needs to redefine the lookup function. Now, I use a several lookup to get a several results and then I place them as value and use IF to redefine the result. It is a long way but somehow it works almost 90% of the times. Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substitute | Excel Worksheet Functions | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
More than 7 IF? any substitute? | Excel Worksheet Functions | |||
SUBSTITUTE | Excel Worksheet Functions | |||
substitute for = | Excel Worksheet Functions |