Help with a function

[Bri]

Hello. I'm quite new to excel VBA and could really use help with a
function. Here's the situation.

When judging dance competitions, there are up to 5 judges. Their scores are
listed in adjacent columns. I need to call a function in the next column
that computes the average score subject to some conditions.
a) If there are 5 or 4 judges, the high and low scores are dropped and the
average of the remaining scores is used. (Scores may have up to three
decimal places, eg 9.115)
b) If there are 1, 2 or 3 judges, no scores are dropped when finding the
average
c) When a judge is missing from the panel, the cell is left blank.(not to be
confused with a judged score of 0.000)
d) The final score is rounded DOWN to three decimal places, so that 7.13583
becomes 7.135, not 7.136 as you might expect.

I've got a competition coming up soon, so I hope someone can help!

Thanks in advance
Bri

 

[scooper]

1. In the columns to the right put boolean conditions to test whether an
entry has been made etc.
2. Refine these down to a single if statement in the column you wish to
show with the result.

scooper

 

[Ken Johnson]

Hi Bri,
I think this formuls follows you rules. I have assumed that dance
partner being judged are in column A, starting at A2, and the five
judge scores are in columns B to F. This formula could be pasted into
G2.

=ROUND(IF(COUNT(B2:F2)>=4,(SUM(B2:F2)-(MIN(B2:F2)+MAX(B2:F2)))/(COUNT(B2:F2)-2),SUM(B2:F2)/COUNT(B2:F2)),2)

Ken Johnson

 

[Ken Johnson]

Hi Bri,
Sorry,I missed the last rule, make that:

=ROUNDDOWN(IF(COUNT(B2:F2)>=4,(SUM(B2:F2)-(MIN(B2:F2)+MAX(B2:F2)))/(COUNT(B2:F2)-2),SUM(B2:F2)/COUNT(B2:F2)),3)

Ken Johnson

 

[utkarsh.majmudar]

Assuming the scores of the five judges are entered in cells A1 through
E1 then in cell F1 enter the following formula:

=IF(OR(H1=4,H1=5),ROUNDDOWN((SUM(A1:E1)-SMALL(A1:E1,1)-LARGE(A1:E1,1))/G1,3),ROUNDDOWN(SUM(A1:E1)/G1,3))

In cell G1 the formula

=IF(OR(COUNT(A1:E1)=4,COUNT(A1:E1)=5),COUNT(A1:E1)-2,COUNT(A1:E1))

and in cell H1

=COUNT(A1:E1)

The output in cell F1 will give you the average score that you need.

You may wish to hide columns G and H for neatness.

You don't really need VBA to do this!

Utkarsh

 

[Ron Rosenfeld]

Hello. I'm quite new to excel VBA and could really use help with a
function. Here's the situation.

When judging dance competitions, there are up to 5 judges. Their scores are
listed in adjacent columns. I need to call a function in the next column
that computes the average score subject to some conditions.
a) If there are 5 or 4 judges, the high and low scores are dropped and the
average of the remaining scores is used. (Scores may have up to three
decimal places, eg 9.115)
b) If there are 1, 2 or 3 judges, no scores are dropped when finding the
average
c) When a judge is missing from the panel, the cell is left blank.(not to be
confused with a judged score of 0.000)
d) The final score is rounded DOWN to three decimal places, so that 7.13583
becomes 7.135, not 7.136 as you might expect.

I've got a competition coming up soon, so I hope someone can help!

Thanks in advance
Bri

The following **array** formula should do what you specify.

=ROUNDDOWN(AVERAGE(LARGE(Scores,
ROW(INDIRECT(1+(COUNT(Scores)>3)&":"&
MIN(COUNT(Scores),3)+(COUNT(Scores)=5))))),3)

"Scores" is the five cell range where your judges scores are potentially
entered.

To enter an **array** formula, after copying or typing it into the cell, hold
down <ctrl><shift> while hitting <enter>. Excel will place braces {...} around
the formula.

I note from your specifications that with four judges, you only use two scores;
but with three judges, you use three scores. Is this correct?


--ron