Hello Forum, this is my first post. I'm relatively inexperienced, but found Lookup almost accomplishes what I want.
I have a form with on the left mice born on various dates, and glucose readings taken on various dates. I want to change it on the right into the readings sorted by how many weeks old the mouse is. First I put in a row under the date of the glucose readings how many weeks this is from the beginning of the year using DATEDIF. I incorporated an IFERROR and an ISBLANK to make it ignore dates where there was no glucose reading taken, and to not return zeros.
The glitches that remain are:
In the created columns for the mouse age at reading, if there is not new data for that age it repeats the previous data.
and
If there are two dates' readings with the mouse being the same age, rounded off, it only gives the second reading data. I added below a couple of lines so that you can see that the 2/7/2020 mouse is 8.6 weeks old on the 4/7 reading date, and 9.4 weeks old on 4/13. Both round off to a 9 week old mouse. It only returns "30", read on 4/13, and the "20" on 4/7 is not shown.
SmallAdvicePng.jpg.png
The formula I used for the cell I6 (which is column: Age 8 Weeks, and row: Mouse B) is: =IFERROR(IF(ISBLANK(LOOKUP(I$2+0.5+DATEDIF($B$2,$B6,"D")/7,$C$3:$F$3,$C6:$F6)),"",LOOKUP(J$2+0.5+DATEDIF($B$2,$B6,"D")/7,$C$3:$F$3,$C6:$F6)),"")
Thankyou anyone. It may be difficult to unravel my train of thought, or intention, but thanks for looking.
Michael
Bookmarks