If any value in a range is between 5 and 7 do this, else this

[Steven811]

Hi

Sorry about the description but, I'm unsure how to explain this more clearly and I've been trying to understand how to do this for some time now. I have a range of 10 cells with data in A1:A10. If any value in any of those cells is between 5 and 7 inclusive, then I want to take a value from A11 and use it otherwise return false.

Any guidance would be gratefully received.

Thanks Steven811

[shg]

What if more than one cell is in that range?

[VKS]

In my opinion if function suits u the most

[VKS]

@shg : Guru is a guru and had a better question to ask

[shg]

To return the smallest among possibly several,

=MIN(IF( (A1:A10 >= 5) * (A1:A10 <= 7), A1:A10))

... confirmed with Ctrl+Shift+Enter instead of just Enter.

[bebo021999]

for 2003 ver
=IF(SUMPRODUCT(--(A1:A10>=5)*(A1:A10<=7))>0,A11)
2007+
=IF(COUNTIFS(A1:A10,">=5",A1:A10,"<=7")>0,A11)

[shg]

Reckon I should read questions more closely ...

[Steven811]

Hi Shg

If at least one value is within that range then I perform the second calculation. It doesn't matter whether it's one or all of the values as long as it's at least one.

[shg]

I think bebo has you sorted with his second suggestion.

[Ace_XL]

No need for the >0 in bebo's formula. Hence,

Please Login or Register  to view this content.