I'm a real rookie at this excel stuff. Hope someone can help. Here's what i need... in cells A1,B1,C1,D1,E1 a 1,2, or 3 will be entered in each. I need cell F1 to tell me which number occurred 3 times or more within those cells. If a # didn't occur more than three times I need a 0 displayed in cell F1. Additionally, if cell F1=0,1,or 2 I need the QUOTIENT of (A1+B1+C1+D1+E1)/15 displayed in G1. Is this possible in excel???

mdosh01

There are multiple ways to approach this. Let's see if I have the conditions correct. Cells A1 through E1 may contain one number: 1, 2, or 3. You need to know if any number occurs 3 times or more. So if the number 1 occurs 3 or more times, display 1 in cell F1. Otherwise display 0. Also, only if the results in F1 is 0, 1, or 2, sum the numbers in cells A1 thru E1 and divide by 15. However, if F1 equals 3, do not perform this calculation.

An easy way to do this is to use three separate cells to determine if a number occurs more than 3 times.

In cell B3 enter: =COUNTIF(A1:E1,"1")
In cell B4 enter: =COUNTIF(A1:E1,"2")
In cell B5 enter: =COUNTIF(A1:E1,"3")

Now I know how many times each number occurs in the range. The formulas above could be placed anywhere in the spreadsheet.

The formula in F1 becomes:

=IF(B3<3,IF(C3<3,IF(D3<3,0,3),2),1)

So I'm checking to see if the number 1 occurs less than 3 times, if it does, I move on to the number 2. If not, I know no other number can occur more than 3 times so the value becomes "1".

The formula in G1 is:

=IF(F1>=0,IF(F1<3,SUM(A1:E1)/15,""))

If the cell F1 contains a number 0, 1 or 2, I sum(a1:e1) and divide by 15. Otherwise I leave the cell blank. Note that if I want to include the number 3, I would change "F1<3" to "F1<=3".

Many other ways to do this. Hope this one works for you.

smokingbaby

You're a genius big head! Thanks so much!

mdosh01

No problem. Glad this worked for you.