IF Functions

[Brennen81]

Hello,

I am trying to utilize 1 cell to tell me if a select set of other cells has a value of greater than a specific number. The issue is I only know how to do this with a specific range. The data I am using I need to pick and choose specific cells and from what I can do, the IF functions can do that.

Here is my example. I have these cells that I would like one cell to tell me if any contain a value of over 50.

AR159:AV160,AS164:AW165,AT169:AX170 and so on....

It seems when selecting multiple ranges, the commas mess up the IF function.

Is there any way around this???

[XOR LX]

Hi,

When you say "and so on", is that to suggest that there is a consistent translation from one range to the next?

For example, your second and third ranges appear to be translations by one column to the right and five columns down from the previous range - is this the case for the fourth, fifth, etc. ranges?

If so, that would make the fourth range AU174:AY175, the fifth AV179:AZ180, etc. Is this correct? If so, how many of these ranges are we talking about?

Regards

[Brennen81]

Hello - Yes, there would be approximately 30 of those ranges. And each one is the sames size and is consistently the same distance from the next, with cells I do not want in-between.

[FDibbins]

Maybe try this?
=max(AR159:AV160,AS164:AW165,AT169:AX170)>50

[Brennen81]

That definitely can work for now. Thank you so much! Your replies and help are deeply appreciated. Really wish I could see the averages of the values above 50!!!!

[XOR LX]

Assuming cell A159 holds your desired number of ranges (e.g. 30), this array formula** version of Ford's should do the job:

=MAX(SUBTOTAL(4,OFFSET(AR159:AV160,5*(ROW(INDIRECT("1:"&A159))-1),ROW(INDIRECT("1:"&A159))-1,2,5)))>50

Regards