• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

OFFSET to INDEX

GB

Member
Hi, I have written this formula and want to make it more efficient by replacing the OFFSET with INDEX to make it less volatile.

{=MIN(OFFSET(A2,,1,INDEX(MATCH(1,--ISNUMBER(A2:A300),0),)))}

What this formula is doing is finding the first cell below A2 to A300 that contains a value then once that range is found it moves across one column (column B) then finds the min value from column B.

Can you show me another way of making this formula more efficient?

regards
GB
 
Hi GB,

You can try below CSE formula:
{=MIN(INDIRECT("B2:B"&SUM(ROW(B2:B300)*(ISNUMBER(A2:A300)))))}

Regards,
Prasad DN
 
Hi Somendra & Prasad,
I have just got back to this question of mine and the answer sprang to mind. Here is the non volatile formula that works for me.

{=MIN(INDEX(A2:INDEX(B2:B100,MATCH(1,--ISNUMBER(A2:A100),0),,1),,))}

Thanks for assistance to help.

regards
GB
 
Hi GB,

If you can share a sample file with before and after formula usage with some sample data in it, that might be useful for other reader's who might visit this thread with the same query, Showing just the formula might not help them visualize how to utilize this in their situation.

Regards,
 
Back
Top