ISBLANK on multiple cells

[onthegreen03]

Hi. I have a chart which returns the top 5 locations and a section for bottom 5 locations based on sales. For the bottom 5 I am using the MAX formula to return the 5 highest numbers in the list which is working great. I ran into a problem when a particular account does not have more than 5 locations. Is there a formula I can use that would tell the MAX formula to ignore the top 5 numbers (1 through 5)? So essentially it would only return a value when there are 6 or more locations, if 5 or less return blank. Thanks for the help!


EDIT: Sorry, I am using the LARGE formula to return highest 5 numbers (not MAX).

 

[jasonb75]

Without seeing the formula or a sample of the relevant data there is typically guesswork involved. Typically you would use COUNTBLANK rather than ISBLANK, unless it is part of an array.

If a LARGE / SMALL formula doesn't have enough values to return then it would return a #NUM! error which can be suppressed by nesting the formula in IFERROR.

Another option would be to use COUNT or COUNTIF to verify that there are at least the required number of locations.

 

[onthegreen03]

Thanks for the reply Jason. I actually solved it by using an OR formula to determine if the top 5 are blank or not. If any one of them is blank, it returns blanks in the bottom 5. It probably was not the best option but it worked. I guess there was more than 1 way to skin the cat here. Thanks again for the reply!