Getting the last time the value was over limit in a dataset

%3CLINGO-SUB%20id%3D%22lingo-sub-3346639%22%20slang%3D%22en-US%22%3EGetting%20the%20last%20time%20the%20value%20was%20over%20limit%20in%20a%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3346639%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20writing%20to%20write%20a%20formula%20for%20a%20dataset%20where%20we%20are%20measuring%20glucose%20values%20overtime.%20The%20values%20fluctuate%20overtime%20and%20I%20need%20excel%20to%20return%20the%20timepoints%20when%20the%20value%20was%20greater%20than%20or%20equal%20to%209%20the%20last%20time%20in%20the%20study.%20I%20have%20written%20a%20crude%20if%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(D4%26gt%3B%3D9%2C%220weeks%22%2CIF(E4%26gt%3B%3D9%2C%224weeks%22%2CIF(F4%26gt%3B%3D9%2C%228week%22%2CIF(G4%26gt%3B%3D9%2C%2212%20weeks%22%2CIF(H4%26gt%3B%3D9%2C%2216weeks%22%2CIF(I4%26gt%3B%3D9%2C%2220weeks%22%2CIF(J4%26gt%3B%3D9%2C%2224weeks%22%2CIF(K4%26gt%3B%3D9%2C%2239weeks%22%2CIF(L4%26gt%3B%3D9%2C%2252weeks%22%2CIF(M4%26gt%3B%3D9%2C%2278weeks%22%2C%22104weeks%22))))))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20this%20returns%20the%20first%20time%20the%20value%20was%20over%20or%20equal%20to%209%2C%20I%20need%20it%20to%20ignore%20the%20first%20value%20if%20the%20glucose%20values%20fall%20at%20the%20next%20timepoint%20and%20only%20give%20the%20value%20when%20the%20values%20are%20finally%20and%20irreversibly%20over%209.%20I%20tried%20writing%20a%20If-and%20formula%20but%20its%20giving%20a%20lot%20of%20errors%20and%20I%20can't%20figure%20out%20what%20I%20am%20doing%20wrong.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ee.g%20in%20the%20fig%20below%20for%20the%20first%20row%20it%20needs%20to%20be%20104%20weeks%20%26nbsp%3Binstead%20of%2024%20weeks%3C%2FP%3E%3CP%3Eand%20for%20the%20second%20row%20it%20should%20be%2039%20weeks%20instead%20of%2020%20weeks.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202022-05-09%20at%201.15.56%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370321i5583D3BD96754B8C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202022-05-09%20at%201.15.56%20PM.png%22%20alt%3D%22Screen%20Shot%202022-05-09%20at%201.15.56%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20would%20be%20the%20right%20function%20to%20use%20in%20this%20case%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EAditi%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3346639%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3346696%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20the%20last%20time%20the%20value%20was%20over%20limit%20in%20a%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3346696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1385210%22%20target%3D%22_blank%22%3E%40anarsale%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20explain%20%22e.g%20in%20the%20fig%20below%20for%20the%20first%20row%20it%20needs%20to%20be%20104%20weeks%20%26nbsp%3Binstead%20of%2024%20weeks%3C%2FP%3E%0A%3CP%3Eand%20for%20the%20second%20row%20it%20should%20be%2039%20weeks%20instead%20of%2020%20weeks%22%3F%20I%20don't%20understand%20that%20at%20all...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3346867%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20the%20last%20time%20the%20value%20was%20over%20limit%20in%20a%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3346867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1385210%22%20target%3D%22_blank%22%3E%40anarsale%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(%24D%242%3A%24N%242%2CLARGE(IF(D4%3AN4%26lt%3B9%2CIF(D4%3AN4%26gt%3B0%2CCOLUMN(A%3AK)))%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20formula.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Visitor

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.

Screen Shot 2022-05-09 at 1.15.56 PM.png

 

what would be the right function to use in this case? 

 

Thanks,

Aditi

1 Reply

@anarsale 

Can you explain "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"? I don't understand that at all...