Ignore blank cells in array formula

  • I've got a list with productnumbers. Like this:
    RS0001
    RS0002
    RS0030
    RS0050


    All numbers are RS followed by four numbers.


    I use the following array formula to find the highest number:


    {="RS"&TEXT(MAX(--(RIGHT($B$1:$B$100,LEN($B$1:$B$100)-2))),"0000")}


    However the list goes further than B100, but I can't use B:B because there are also empty cells in column B which gives #Value!.
    How can I get this formula to ignore blanks?

  • Re: Ignore blank cells in array formula


    Something like:


    [COLOR="#0000FF"]="RS"&TEXT(MAX(IF($B$1:$B$100<>"",--(RIGHT($B$1:$B$100,LEN($B$1:$B$100)-2)))),"0000")[/COLOR]


    or you can create a dynamic named range, and use that range name as the range reference without having to add the IF() function...

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016