Posts by khr

Re: Find last digit in row < X

I can post a sample

The first column is case number and the top row is time in 5 minute intervals.

forum.ozgrid.com/index.php?attachment/56055/

I hope that helps. The rest of it is just repetitions of similar data

Re: Find last digit in row < X

Thanks, that works well.

The only thing is that there are some blank cells at the end of the row and the formula doesn't exclude those. Is there anyway to overcome that other than altering the range? I have to apply it to about 3000 rows that are all of varying length

If I have the following sequence:

[TABLE="width: 1690"]

[tr]

[TD="class: xl64, width: 65, align: right"]35.9[/TD]
[TD="class: xl64, width: 65, align: right"]35.4[/TD]
[TD="class: xl64, width: 65, align: right"]34.9[/TD]
[TD="class: xl64, width: 65, align: right"]34.1[/TD]
[TD="class: xl64, width: 65, align: right"]33.9[/TD]
[TD="class: xl64, width: 65, align: right"]33.7[/TD]
[TD="class: xl64, width: 65, align: right"]33.5[/TD]
[TD="class: xl64, width: 65, align: right"]33.5[/TD]
[TD="class: xl64, width: 65, align: right"]33.5[/TD]
[TD="class: xl64, width: 65, align: right"]33.5[/TD]
[TD="class: xl64, width: 65, align: right"]33.7[/TD]
[TD="class: xl64, width: 65, align: right"]33.7[/TD]
[TD="class: xl64, width: 65, align: right"]33.7[/TD]
[TD="class: xl64, width: 65, align: right"]34.3[/TD]
[TD="class: xl64, width: 65, align: right"]35.1[/TD]
[TD="class: xl64, width: 65, align: right"]36.1[/TD]
[TD="class: xl64, width: 65, align: right"]36.6[/TD]
[TD="class: xl64, width: 65, align: right"]36.8[/TD]
[TD="class: xl64, width: 65, align: right"]37.0[/TD]
[TD="class: xl64, width: 65, align: right"][/TD]
[TD="class: xl64, width: 65, align: right"][/TD]
[TD="class: xl64, width: 65, align: right"][/TD]
[TD="class: xl64, width: 65, align: right"][/TD]
[TD="class: xl64, width: 65, align: right"][/TD]
[TD="class: xl64, width: 65, align: right"][/TD]
[TD="class: xl64, width: 65, align: right"][/TD]

[/tr]

[/TABLE]

I would like to find the last number in the row < 34 (i.e. the 33.7 in the 13th position)

Thanks to this forum, I already have a formula for finding the last minimum value in the row: =LOOKUP(1,1/(MIN(B2:AR2)=B2:AR2),ADDRESS(ROW(B2),COLUMN(B2:AR2),4))

As well as one for finding the FIRST number in the row < 34: =CELL("address",INDEX(B2:AR2,MATCH(TRUE,B2:AR2<=34,0)))

But I cannot seem to find a formula that will give me the last number in the row that is less than 34.

Thanks.

Re: Identify First number in row that is less than or equal to x

Perfect. Thanks a million.

Re: Identify First number in row that is less than or equal to x

Yes the first number less than 34. This function was able to do it: =INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0)) which gives the numerical value, so now I just need to find the coordinates for the numerical value I've found

Re: Identify First number in row that is less than or equal to x

This is just giving me #VALUE!, what is the "a1:" for?

Re: Identify First number in row that is less than or equal to x

I also tried =ADDRESS(ROW(INDEX(B2:AR2,MATCH(TRUE,B2:AR2<=34,0))),COLUMN(B2:AR2),4) with control + Shift + Enter, this results in the row, but does not give the correct column. i.e. it just gives A1, A2, A3, A4 etc

Re: Identify First number in row that is less than or equal to x

Great thanks, that works well. Can the ADDRESS function just be applied to this to find the coordinates of the cell? I tried =ADDRESS(INDEX(A1:L2,MATCH(TRUE,A1:L1<=34,0))),A1:L1,4) but there are 'not enough arguments'?

Re: Identify First number in row that is less than or equal to x

Only I need it to identify the first number from the left that is less than 34. i.e. the 33.9 in the srd column, not the 33.9 in the 8th column...

Re: Identify First number in row that is less than or equal to x

The first number from the left that is less that or equal to 34.

Re: Identify First number in row that is less than or equal to x

I think the second function is better, it still has the same problem of ignoring the < as soon as I put in = i.e. =INDEX(MAX((A1:L1<=34)*(A1:L1)),1)

Re: Identify First number in row that is less than or equal to x

I want the first number in the row that is less than or equal to 34. =LARGE((A1:L1<=34)*(A1:L1),1) gives me the first number equal to 34 (i.e. it ignores the <) also I just realised that this function returns the largest number less than 34, not the first number less that 34.

Re: Identify First number in row that is less than or equal to x

Thanks,

That works well, but the only thing is that the '=' overrides the '<', so that when I apply it to the above formula it gives me 34 (in position 9) rather than 33.9 (in position 3). I've tried adding " " and ( ) around the <=34 to isolate it, but to no avail. Any suggestions?

Re: Identify First number in row that is less than or equal to x

I thought I could use =MAX(A1:L1,COUNTIF(A1:L1,"<=34")) to give me the highest value from the left hand side that is less than or equal to 34 but this doesn't seem to be working.

Does anyone know a good formula for identifying the first value in a row that is less or equal to than another?

For example, if I have the row:

35 34.2 33.9 33.8 33.9 33.6 33.8 33.9 34 35 36 37

I would like to identify the first number from the left that is less than or equal to 34 i.e. 33.9 in position 3.

Thanks

Re: use cell entry for column function, not cell coordinates

It works, that was nice and simple. Thank you very much for all your help, and yes sorry the initial explanations were confusing.

Re: use cell entry for column function, not cell coordinates

Ok so referring to the above table. In column M I have listed 3 sets of coordinates which each relate to a cell somewhere in the row. If we imagine that I would like to use the column function to find the number of the column for coordinates 'E1' that are listed in cell M1.Using the column function for cell M1 gives: =COLUMN(M1) giving an answer of 13 (i.e. the 13th column from the left). However what I would like to do is use the contents of M1 (i.e. E1) in the COLUMN function i.e. =COLUMN(E1) giving me an answer of 5.

Sorry about the confusion, I hope that makes it more clear. Help will be greatly appreciated!

Re: use cell entry for column function, not cell coordinates

Sorry that may not have been adequately explained. I know that the column function gives the number of the cell from the left, but what I'm trying to do is find a function that will give me the column number according to coordinates that are listed in a cell, not using the coordinates of the cell itself. For example, if we use the picture below:- Sorry the picture wont upload, but from the description below I'm sure you can get the picture.

In column M, I have listed 3 different sets of coordinates which each relate to a cell somewhere within that row. Now lets imagine I would like to use the column function to find the number of the column for coordinates 'E5' (i.e. I would like it to generate the number 5). However, when I use the COLUMN function, =COLUMN(M1) gives me 13, not 5. So what I would like to do is find a way to make the column function recognise the coordinate E1 (even though it's listed in M1).

Thanks.

I am trying to use the 'COLUMN' function to give me the column number for a cell. The problem is that the cell coordinates I want to use are entered as coordinates within another cell.

For example, the coordinates I want to use are Q4, but these coordinates are entered into cell AW4, therefore when I use the column function for that cell it gives the column number 49, not 17 as I require.

Any suggestions for how to overcome this?

Thanks.

Re: Identifying the last of several repeated smallest values in a row

I need it to calculate the difference between the last smallest value and the largest value (which always appears after that). Thanks for your reply, though I think I solved the problem by a different method

For future users, I found the following formula at this address http://www.youtube.com/watch?v=cpRlYaaztx4
=LOOKUP(1,1/(MIN(A1:Z1)=A1:Z1),ADDRESS(ROW(A1),COLUMN(A1:Z1),4)) which gives the coordinates of the last cell in the row that contains the smallest value.

By changing this to =INDIRECT(LOOKUP(1,1/(MIN(A1:Z1)=A1:Z1),ADDRESS(ROW(A1),COLUMN(A1:Z1),4))) you can obtain the value of the contents of the cell rather than the coordinates.