Home

Help with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-913017%22%20slang%3D%22en-US%22%3EHelp%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-913017%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20help%20in%20rewriting%20this%20formula%20so%20that%20it%20increases%20by%2010%20rows%20at%20a%20time.%26nbsp%3B%20I%20haven't%20been%20able%20to%20figure%20out%20the%20correct%20sequence%20using%20OFFSET%2C%20but%20any%20solution%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(LOOKUP(2%2C1%2F(ISNUMBER('PATIENT%20TRACKING'!R4%3AR13))%2C'PATIENT%20TRACKING'!R4%3AR13)%2C%22NO%20SCORE%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20copy%20down%20I%20would%20like%20the%20second%20cell%20to%20reference%20R14%3A23%20then%20the%20third%20to%20reference%20R24%3A33%20and%20so%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-913017%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-913226%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-913226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425865%22%20target%3D%22_blank%22%3E%40Registryuser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20say%20your%20first%20formula%20is%20in%20row%20%233.%20When%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EOFFSET(%24R%244%2C(ROW()-ROW(%24A%243))*10%2C0%2C10)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ereturns%20R4%3AR13%20range.%20If%20drag%20it%20down%20on%20one%20cell%2C%20next%20returned%20range%20will%20be%20R14%3AR23.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Registryuser
New Contributor

Hello,

 

I'm looking for help in rewriting this formula so that it increases by 10 rows at a time.  I haven't been able to figure out the correct sequence using OFFSET, but any solution would be much appreciated.

 

=IFERROR(LOOKUP(2,1/(ISNUMBER('PATIENT TRACKING'!R4:R13)),'PATIENT TRACKING'!R4:R13),"NO SCORE")

 

When I copy down I would like the second cell to reference R14:23 then the third to reference R24:33 and so on. 

 

Thank you

4 Replies

@Registryuser 

Let say your first formula is in row #3. When

OFFSET($R$4,(ROW()-ROW($A$3))*10,0,10)

returns R4:R13 range. If drag it down on one cell, next returned range will be R14:R23.

@Sergei Baklan thank you for the response.

 

How would I correctly incorporate this into the formula I posted?

 

Thanks,

 

Daniel

@Registryuser 

Daniel, that could be like

=IFERROR(LOOKUP(2,1/(ISNUMBER(OFFSET('PATIENT TRACKING'!$R$4,(ROW()-ROW($A$3))*10,0,10))),OFFSET('PATIENT TRACKING'!$R$4,(ROW()-ROW($A$3))*10,0,10)),"NO SCORE")

if you first formula is within row 3 (e.g in B3).

@Registryuser 

Instead of the volatile OFFSET, a non-volatile alternative using INDEX would be: 

=IFERROR(LOOKUP(2,1/(ISNUMBER(INDEX('PATIENT TRACKING'!R:R,4+(ROWS($1:1)-1)*10):INDEX('PATIENT TRACKING'!R:R,13+(ROWS($1:1)-1)*10))),

INDEX('PATIENT TRACKING'!R:R,4+(ROWS($1:1)-1)*10):INDEX('PATIENT TRACKING'!R:R,13+(ROWS($1:1)-1)*10)),

"NO SCORE")

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies