LOOKUP: Trying to deglitch repeats in data

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

Trying to get screenshot again:

Hello mchlbrmn and Welcome to Excel Forum.
It may help someone understand if you also manually include the values you want to see in columns H:N.
Let us know if you have any questions.

Thanks for the advice. I'll try reposting, with more explanation, and I'll delete this if I figure out how.

Hey, I can't figure out how to upload an Excel file. I go to Manage Attachments and select it, but I can't get it in. I think it has an error that I need to post a few times first, but I managed before??
(This is so frustrating.)

Perhaps edit post #2 by removing the existing .xlsx file attached there and then attaching an updated file.
After doing that add a new post to the thread so that we will receive a notification.
Let us know if you have any questions.

I posted a new thread. I'd delete this one if that's possible.
I had problems posting even on a new thread. I guess I have to attach, preview, then 'Manage Attachments' again, and then maybe the attachment appeared in the preview (?). It's not intuitive, I still don't know if I understand how it works, and I spent a lot of time trying to post. It's probably simple once one is used to it, but even trying to follow directions I found it to be difficult.

Anyway, feel free to delete this post moderators.

(Weird, now the "Go Advanced" button is there when I don't need it. Nope, it's disappeared again.)

Continue in this thread, please. Attach the workbook here.

We don’t close threads just because of problems posting attachments - if you follow the instructions at the top of the page, you won’t have any problems. Thanks.

Perhaps this qualifies as "any help" as it isn't exactly like you want:
1. The formula that populates C39:F40 is: =ROUND(DATEDIF($B39,C$25,"D")/7,0)
2. The formula that populates H28:O37 is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Try to shorten it a bit and eliminate un-needed semicolon:

Please Login or Register  to view this content.

The results are mixed: 20 or "20,30", so treated like text.

I need to work with the results sorted by age in weeks, as you got it to appear so well. It doesn't need to appear exactly as I requested originally, but is there a route to get numbers I can work with, to summarize data? I would like to get averages of data from subgroups of the mice.

I'm sorry I didn't explain better from the start where I was going with the numbers.

I came up with splitting weeks into two halfs, then sum and average at the bottom for each week age per mouse group.

Thank you so much again Bebo021999 . That works great for all the 2/7 mice. It says First Half and Second Half, but it works separating the numbers even if mice were born in the same half of a week. Great!
There's no sorted results for the last line, with a mouse born on a different day (2/10). In my actual data there are mice born on many dates, listed in the colunm in a random order (it's sorted by other things).
Can it work mixed birth dates?

I see you have line 13 in the formula for the 2/10 mouse on line 12. I put a date in line 13, and results appear for the 2/10 mouse, but if I change the date in line 13, the results change. That shouldn't happen. [Note, see below, now fixed.]

I'm still a helpless baby, and it will take time for me to figure out all the functions that you used. But I will, I hope, eventually! But, maybe I can get my data for this experiment done sooner before my clunky brain has caught up.

Added on:
OK, it looks like it was a simple typo. If I change the 13 to a 12, it now works correctly. Wonderful, Don't know how it works (yet), but I'll see if I can use it on my actual data!

Yes, it was my typo,
In row 4
=IFERROR(INDEX(4:4,AGGREGATE(15,6,COLUMN($C$1:$F$1)/(ROUND(($C$1:$F$1-$B5)/7,0)=LOOKUP(2,1/($H$1:H$1<>""),$H$1:H$1)),LEFT(H$2)+0)),"")
Working in row 4, change $B5 to same line $B4, then it should work.