0

I have a sheet filled with blood pressure (BP) data, arranged by month.

I am interested in storing the last three BP values in a range per patient, each of which is assigned a row. Note that there are months which do not have BP readings, so there are blanks in the dataset.

The range for the blood pressures is BB:BL.

I was able to find formulas for the last:

=LOOKUP(2,1/(ISNUMBER(BB3:BL3)),BB3:BL3)

and second-to-last:

=LOOKUP(9.99E+307,BB3:INDEX(BB3:BL3,MATCH(9.99E+307,BB3:BL3)-1))

But I am not able to modify the formula above to find the third-to-last non-blank BP value.

Any advice is appreciated!

Improve this question

2 Answers 2

Reset to default
2

To find the third-to-last non-blank value, try using INDEX+AGGREGATE function

1] For data range placed in vertically

In C2, enter formula :

=INDEX(A1:A100,AGGREGATE(14,6,ROW(A1:A100)/(A1:A100<>""),3))

enter image description here

2] For data range placed in horizontally

In B3, enter formula :

=INDEX(1:1,AGGREGATE(14,6,COLUMN(1:1)/(1:1<>""),3))

enter image description here

2.a] If the data range placed in a specified position, e.g. BB3:BH3

The formula become, in B3 formula : >>

=INDEX(BB3:BH3,AGGREGATE(14,6,COLUMN(BB3:BH3)-COLUMN(BA3)/(BB3:BH3<>""),3))

Remark : the Column number must adjusted to COLUMN(BB3:BH3)-COLUMN(BA3) in become {1,2,3,4,5....}

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks @bosco_yip, I think this makes sense. However, rather than looking at the entire row, would it be look at the range of interest, and return the third-to-last value in the same row? =INDEX(BB3:BH3,AGGREGATE(14,6,COLUMN(BB3:BH3)/(BB3:BH3<>""),3)) The above formula is not functional, but is there any modifications we can make? For organizational purposes it would be convenient to store in the same row, but if it's not possible, no worries. I appreciate your insight!
0

If one has the Dynamic Array Formula Filter then:

=INDEX(FILTER(1:1,1:1<>""),COUNTA(FILTER(1:1,1:1<>""))-2)

enter image description here

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.