Multiple IF / OR Fucntions for values within a certain range..?

[Ourkid123uk]

Hi guys!

After the EXCELLENT more than helpfull response to my last post im asking to pick your brains again!

You guys are WAY more advanced than me... but with your help im getting there and appreciate it greatly!

So heres what im trying to achieve...

I have Cells D10 - D19 which return a number to 3 decimal places eg, 1.56, 2.15, 25.2 etc To the left of these cells is the name of people (C10 - C19) each corresponding to the number directly to its right. Eg,

C10 = Andrew Wood D10 = 1.56
C11 = Mark Williams D11 = 1.84
C12 = ...... D12 = ....

all the way down to C19 & D19.



Im tryin to get a Cell to look at all the numbers in the range (D10-D19) and do the following.

1) If TWO or more numbers (D10-D19) are EXACTLY the same between 1.00-3.00 then Display 'X' if not Display 'Y'

OR

2) If THREE or more numbers (D10-D19) are between (and including) 1.00-1.99 (basically in the 1's) AND Three or more numbers (D10-D19) are between (and including) 2.00-2.99 (basicall in the 2's) then Display 'X' if not Display 'Y'

OR

3) If THREE or more numbers (D10-D19) are between (and including) 1.00-1.99 (basically in the 1's) AND Three or more numbers (D10-D19) are between (and including) 3.00-3.99 (basicall in the 3's) then Display 'X' if not Display 'Y'

OR

4) If THREE or more numbers (D10-D19) are between (and including) 2.00-2.99 (basically in the 2's) AND Three or more numbers (D10-D19) are between (and including) 3.00-3.99 (basicall in the 3's) then Display 'X' if not Display 'Y'


Does the above make sense? Have i explianed this well enough!?

I really appreicate you guys helping me out with me starting with Excel and ive already learned SO MUCH! I cant help feeling that for some of you this is EASY! While im still getting my head round everything im trying to achieve.

Thanks alot for taking the time if you decide to reply! Any help appreciated!

Andy

[Bernie Deitrick]

1) Array-enter (enter using Ctrl-Shift-Enter)

=IF(MAX(FREQUENCY(IF(D10:D19>=1,IF(D10:D19<=3,D10:D19)),IF(D10:D19>=1,IF(D10:D19<=3,D10:D19))))>1,"X","Y")

2)

=IF(AND(COUNTIFS(D10:D19,">=1",D10:D19,"<2")>=3,COUNTIFS(D10:D19,">=2",D10:D19,"<3")>=3),"X","Y")

3)

=IF(AND(COUNTIFS(D10:D19,">=1",D10:D19,"<2")>=3,COUNTIFS(D10:D19,">=3",D10:D19,"<4")>=3),"X","Y")

4)

=IF(AND(COUNTIFS(D10:D19,">=2",D10:D19,"<3")>=3,COUNTIFS(D10:D19,">=3",D10:D19,"<4")>=3),"X","Y")

[Ourkid123uk]

Thank Bernie!

I dont think i explained that i have only been using Excel for a couple of days so ill 'Google' "Array-enter" and test it out! (im sure it will work just fine!)

Im learning SO much as i can see what you have done with MAX , FREQUENCY.

Thanks for your help!

[Ourkid123uk]

Ive looked into Arrays but do not fully understand how i can enter the 4 formulas above into the same cell?

I just enetered them all together and pressed CSE (control-shift-enter)... this is what it looks like,

=IF(MAX(FREQUENCY(IF(D10:D19>=1,IF(D10:D19<=3,D10:D19)),IF(D10:D19>=1,IF(D10:D19<=3,D10:D19))))>1,"X","Y")=IF(AND(COUNTIFS(D10:D19,">=1",D10:D19,"<2")>=3,COUNTIFS(D10:D19,">=2",D10:D19,"<3")>=3),"X","Y")=IF(AND(COUNTIFS(D10:D19,">=1",D10:D19,"<2")>=3,COUNTIFS(D10:D19,">=3",D10:D19,"<4")>=3),"X","Y")
=IF(AND(COUNTIFS(D10:D19,">=2",D10:D19,"<3")>=3,COUNTIFS(D10:D19,">=3",D10:D19,"<4")>=3),"X","Y")

The cell displays FALSE and when i added just the second formula it said TRUE. How do i get this array to return 'X' if one or all of the fourmulas are TRUE or 'Y' if none of them are?

Sorry to be a pain, as i say ive only been using excel for a couple of days and really trying as much as i can on my own until i get stuck... then my philosophey is "you dont ask, you dont get"

Thanks again for taking time to help!

[Bernie Deitrick]

Sorry - I interpreted your question as wanting four formulas. Each of those needs to go into its own cell. The first one needs Ctrl-Shift-Enter, and the others don't (just use Enter).


BUT, since you wanted a single cell formula that checked all those conditions (now that I have re-read your original post), you would use this in one cell (entered using Ctrl-Shift-Enter):

=IF(OR(MAX(FREQUENCY(IF(D10:D19>=1,IF(D10:D19<=3,D10:D19)),IF(D10:D19>=1,IF(D10:D19<=3,D10:D19))))>1,AND(COUNTIFS(D10:D19,">=1",D10:D19,"<2")>=3,COUNTIFS(D10:D19,">=2",D10:D19,"<3")>=3),AND(COUNTIFS(D10:D19,">=1",D10:D19,"<2")>=3,COUNTIFS(D10:D19,">=3",D10:D19,"<4")>=3),AND(COUNTIFS(D10:D19,">=2",D10:D19,"<3")>=3,COUNTIFS(D10:D19,">=3",D10:D19,"<4")>=3)),"X","Y")

[Ourkid123uk]

AHHH!!! i see! i added the TRUE or FALSE after each formula when it should just be at the end!!!

THANKYOU!

I cant believe how many people have helped me on my begenning journey with Excel

[Bernie Deitrick]

Everybody was a beginner at some time - we're just paying it forward. So in a few years when you're the expert.....