|
Help in finding Value in Index Array
I am trying to search a column (C5:C400) to find out when it runs to a null
value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
You have to be *very* precise in your terminology!
What *exactly* do you mean by "null value"? Empty (blank, unused) cell Zero length string ( "" ) 0 in cell -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
One way
=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
By null value, I mean the cell will be Empty (blank, unused).
Thanks, "RagDyer" wrote: You have to be *very* precise in your terminology! What *exactly* do you mean by "null value"? Empty (blank, unused) cell Zero length string ( "" ) 0 in cell -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
Hi Peo,
Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
Then this should work
=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... By null value, I mean the cell will be Empty (blank, unused). Thanks, "RagDyer" wrote: You have to be *very* precise in your terminology! What *exactly* do you mean by "null value"? Empty (blank, unused) cell Zero length string ( "" ) 0 in cell -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
I misunderstood, I thought you were looking for zero in column G, this will
work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
Either I totally misunderstood what you wanted or you are not using the same
cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
I am looking for the first positive value in column G (not C), after the
first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
Try this
=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
It now shows #N/A. Could it be because Column G has blank (empty cell) rows
before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
No, sounds like you forgot to enter it with ctrl + shift & enter. I have a
small sample I could email you if you provide me with an email address but make sure you disguise it so the spambots don't fish it up -- Regards, Peo Sjoblom "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
Try this *array* formula:
=INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40, MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
That formula does not work for me,it fails if G5 is zero
-- Regards, Peo Sjoblom "RagDyer" wrote in message ... Try this *array* formula: =INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40, MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
I don't know Peo, but I think both our formulas do what they're supposed to
do. And, putting back the 0's that I took out of the G & C400, they're just about the same size too!<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... No, sounds like you forgot to enter it with ctrl + shift & enter. I have a small sample I could email you if you provide me with an email address but make sure you disguise it so the spambots don't fish it up -- Regards, Peo Sjoblom "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
Works OK for me!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... That formula does not work for me,it fails if G5 is zero -- Regards, Peo Sjoblom "RagDyer" wrote in message ... Try this *array* formula: =INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40, MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
So if you have a zero in C5 and the first blank in C22 and you have 3 in G9
and the first positive value let's say 800 in G28 what does it return? For me it returned 3 while mine returned 800 -- Regards, Peo Sjoblom "RagDyer" wrote in message ... Works OK for me! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... That formula does not work for me,it fails if G5 is zero -- Regards, Peo Sjoblom "RagDyer" wrote in message ... Try this *array* formula: =INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40, MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
That works!
Thank you both very much. "RagDyer" wrote: Works OK for me! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... That formula does not work for me,it fails if G5 is zero -- Regards, Peo Sjoblom "RagDyer" wrote in message ... Try this *array* formula: =INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40, MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
You first mentioned *G*5, which I checked, and found nothing wrong.
You then mentioned *C*5, which did as you said, where it used the zero cell as the first reference point. A simple revision brings it in line with yours: =INDEX(INDEX(G5:G400,MATCH(TRUE,C5:C400="",0)):G40 0, MATCH(TRUE,INDEX(G5:G400,MATCH(TRUE,C5:C400="",0)) :G4000,0)) I guess this is what causes bugs, even for MS, where not *all* scenarios are checked out before releasing the product. It's nice of you and Biff to check me out most of the time.<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... So if you have a zero in C5 and the first blank in C22 and you have 3 in G9 and the first positive value let's say 800 in G28 what does it return? For me it returned 3 while mine returned 800 -- Regards, Peo Sjoblom "RagDyer" wrote in message ... Works OK for me! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... That formula does not work for me,it fails if G5 is zero -- Regards, Peo Sjoblom "RagDyer" wrote in message ... Try this *array* formula: =INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40, MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
Help in finding Value in Index Array
Appreciate the feed-back ...*BUT* ...
If there's *any possibility* that you might have zeroes in Column C, Make sure that you use either my *revised* formula, or Peo's formula. Peo's formula *does* work! Don't know what you might have done, that you had trouble trying it out. As far as I can tell, *both* formulas do *exactly* the same thing! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... That works! Thank you both very much. "RagDyer" wrote: Works OK for me! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... That formula does not work for me,it fails if G5 is zero -- Regards, Peo Sjoblom "RagDyer" wrote in message ... Try this *array* formula: =INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40, MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George" wrote in message ... It now shows #N/A. Could it be because Column G has blank (empty cell) rows before the first positive value? "Peo Sjoblom" wrote: Try this =INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1) array entered -- Regards, Peo Sjoblom "George" wrote in message ... I am looking for the first positive value in column G (not C), after the first empty cell in column C. (I tried just replacing the C & G's but it didn't work.) "Peo Sjoblom" wrote: Either I totally misunderstood what you wanted or you are not using the same cell references/formula as posted because it does return the first value in C5:C400 greater than zero starting from the first blank cell in G5:G500. This is what I have starting in G5 0 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -5 -6 -7 8 9 so the first blank is below -7 in C22, this is what I have starting in C5 0 -17 -16 -15 1 -13 -12 16 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 0 0 0 0 5 6 7 8 9 10 the first value greater than 0 starting in row C22 is 5 in cell C28 and that is what my formula returns -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Sorry but that returns the C5 value (column G is zero at that point, if it helps). I need the first positive number in column G after column C is empty. Thanks, "Peo Sjoblom" wrote: I misunderstood, I thought you were looking for zero in column G, this will work =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1) -- Regards, Peo Sjoblom "George" wrote in message ... Hi Peo, Thanks, but the formula below returns the first value in column C (after row 5), but I am actually looking for the first positive value in column G after column C declines to an empty cell. "Peo Sjoblom" wrote: One way =INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "George" wrote in message ... I am trying to search a column (C5:C400) to find out when it runs to a null value, then search down a different column (G) starting from that same row and return the first positive number found. Can anyone help? I've been using the following as a starting point, but I'm stuck on how to complete it. =INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0)) Thanks! |
| All times are GMT +1. The time now is 07:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com