Help with count function

[cheggie1]

I have a row of test scores in cells b4 through e4 (beginning in b4 they are 60, 50, 40 and 79) and a test average in cell f4...The formula has to contain sum, min and count functions. The test average is the sum of b4:e4, minus the lowest score which is in cell d4 and then divided by the count of the other 3 columns...

I have the formula as follows: =sum(b4:e4)-min(b4:e4) which equals a total of 189, but I cannot figure out how to use the count function to divide this total by 3 to get the test average of 63.0...

Can anyone help me with this formula???

Thanks...

Connie

[Bryan Hessey]

Hi
try

=(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1)

hth
---

[cheggie1]

The total came to 46.25 and should be 63.0

[cheggie1]

Hi
try

=(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1)

hth
---

The total came to 46.25 but it should be 63.0

[Paul]

If you leave off the () around the divisor you will get 46.25.

Either type carefully or copy and paste Bryan's original formula (or a copy of it here) into your spreadsheet:

=(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1)

If you don't put the () around the "COUNT.." function, it divides your SUM-MIN by the count, and then simply subtracts 1 at the end. With the COUNT and -1 in parenthesis, the SUM-MIN is divided by the COUNT-1.

=(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1)
=(SUM(60,50,40,79)-MIN(60,50,40,79))/(4-1)
=(229-40)/(3)
=(189)/(3)
=63

[Bryan Hessey]

The total came to 46.25 but it should be 63.0

Hi,

include the red brackets

=(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1)

---

[cheggie1]

Thanks a million...

[Bryan Hessey]

Thanks a million...

I guess that means it worked, thanks for your response and thanks also to Paul ( pjoaquin ) for his better explanation.

---

[cheggie1]

You were both most helpful...