Hi,
I am writing to write a formula for a dataset where we are measuring glucose values overtime. The values fluctuate overtime and I need excel to return the timepoints when the value was greater than or equal to 9 the last time in the study. I have written a crude if formula:
=IF(D4>=9,"0weeks",IF(E4>=9,"4weeks",IF(F4>=9,"8week",IF(G4>=9,"12 weeks",IF(H4>=9,"16weeks",IF(I4>=9,"20weeks",IF(J4>=9,"24weeks",IF(K4>=9,"39weeks",IF(L4>=9,"52weeks",IF(M4>=9,"78weeks","104weeks"))))))))))
but this returns the first time the value was over or equal to 9, I need it to ignore the first value if the glucose values fall at the next timepoint and only give the value when the values are finally and irreversibly over 9. I tried writing a If-and formula but its giving a lot of errors and I can't figure out what I am doing wrong.
e.g in the fig below for the first row it needs to be 104 weeks instead of 24 weeks
and for the second row it should be 39 weeks instead of 20 weeks.
what would be the right function to use in this case?
Thanks,
Aditi
