Hi,
I am creating a spreadsheet which can be used by someone else which needs to be dynamic and update when new values are added.
It will contain dates and corresponding weights measured for those dates.
It may well have gaps in the list and the dates may not be at set intervals.
In order to calculate the weight gain per day for each of the subjects I have decided to create a summary table containing the current weight and the date measured and the previously measured weight and the date measured. I want this to automatically update from information put in on Sheet 1.
I have used this formula to find the last item in a list (I also only want it to show a value if there is a subject in that line):
=IF(Sheet1!A5<>"",INDEX(Sheet1!G5:Sheet1!AA5,MATCH(9.99999999999999E+307,Sheet1!G5:Sheet1!AA5)),"")
and to find the corresponding date which is 1 row above for the first subject, 2 rows above for the second subject etc, I have used this formula:
=IF(Sheet1!A5<>"",OFFSET(INDEX(Sheet1!G5:Sheet1!AA5,MATCH(9.99999999999999E+307,Sheet1!G5:Sheet1!AA5)),-1,0),"")
which works well.
However I have struggled to find a formula to find the second last weight in the data set. The first formula I used was this:
=IF(Sheet1!A5<>"",OFFSET(Sheet1!G5,0,(MATCH(MAX(Sheet1!G5:Sheet1!AA5)+1,Sheet1!G5:Sheet1!AA5,1)-1)-1),"")
but if there is a gap in the data-which will be quite likely it shows 0.
So I am using this formula:
=IF(Sheet1!A5<>"",LOOKUP(2,1/Sheet1!G5:INDEX(Sheet1!G5:Sheet1!AA5,MATCH(1E+99,Sheet1!G5:Sheet1!AA5)-1),Sheet1!G5),"")
However with this formula I cannot find a way to find the corresponding date, which is again 1 row above for the first subject, 2 rows above for the second etc.
Is it because LOOKUP won't work with OFFSET?
Can anyone suggest formulas which will
1) tell me the 2nd to last or nth to last value in a data set (all on one row)
and
2)which will then work with a formula which can then tell me the corresponding date
The data is set out as follows:
Subject ID: Birth Date Birth weight Date weighed 1 Date weighed 2 etc
1 05/10/16 50 65 70
I am creating a spreadsheet which can be used by someone else which needs to be dynamic and update when new values are added.
It will contain dates and corresponding weights measured for those dates.
It may well have gaps in the list and the dates may not be at set intervals.
In order to calculate the weight gain per day for each of the subjects I have decided to create a summary table containing the current weight and the date measured and the previously measured weight and the date measured. I want this to automatically update from information put in on Sheet 1.
I have used this formula to find the last item in a list (I also only want it to show a value if there is a subject in that line):
=IF(Sheet1!A5<>"",INDEX(Sheet1!G5:Sheet1!AA5,MATCH(9.99999999999999E+307,Sheet1!G5:Sheet1!AA5)),"")
and to find the corresponding date which is 1 row above for the first subject, 2 rows above for the second subject etc, I have used this formula:
=IF(Sheet1!A5<>"",OFFSET(INDEX(Sheet1!G5:Sheet1!AA5,MATCH(9.99999999999999E+307,Sheet1!G5:Sheet1!AA5)),-1,0),"")
which works well.
However I have struggled to find a formula to find the second last weight in the data set. The first formula I used was this:
=IF(Sheet1!A5<>"",OFFSET(Sheet1!G5,0,(MATCH(MAX(Sheet1!G5:Sheet1!AA5)+1,Sheet1!G5:Sheet1!AA5,1)-1)-1),"")
but if there is a gap in the data-which will be quite likely it shows 0.
So I am using this formula:
=IF(Sheet1!A5<>"",LOOKUP(2,1/Sheet1!G5:INDEX(Sheet1!G5:Sheet1!AA5,MATCH(1E+99,Sheet1!G5:Sheet1!AA5)-1),Sheet1!G5),"")
However with this formula I cannot find a way to find the corresponding date, which is again 1 row above for the first subject, 2 rows above for the second etc.
Is it because LOOKUP won't work with OFFSET?
Can anyone suggest formulas which will
1) tell me the 2nd to last or nth to last value in a data set (all on one row)
and
2)which will then work with a formula which can then tell me the corresponding date
The data is set out as follows:
Subject ID: Birth Date Birth weight Date weighed 1 Date weighed 2 etc
1 05/10/16 50 65 70
