I have a worksheet which contains a number of pupils results with gaps
inbetween then. I want to put a formula at the end of each row to calculate
how many people have A*-C grade.

However I can only seem to do this for a selected range instead of choosing
the spercific cells I want to include. This is a copy of the formula I have
for the entire range;
{=SUM(IF((A1:J1="A*")+(A1:J1="A")+(A1:J1="B")+(A1:J1="C"), 1, 0))}

However, as I said I need it to apply to just cells A1,C1,E1. I have tried
entering this as a comma seperated list eg, (A1,C1,E1="A*). However this
doesn't work as it claims there is a value error (Like everything that goes
wrong on Excel)

Any ideas? I have put the correct syntax and the array is in the correct
place. I have many otherworksheets with this formula working through an
entire range but this one is different as I need it through specified cells.

Cheers

Tom

On Mon, 9 Jun 2008 07:14:03 -0700, Tom5 <(E-Mail Removed)> wrote:

>I have a worksheet which contains a number of pupils results with gaps
>inbetween then. I want to put a formula at the end of each row to calculate
>how many people have A*-C grade.
>
>However I can only seem to do this for a selected range instead of choosing
>the spercific cells I want to include. This is a copy of the formula I have
>for the entire range;
>{=SUM(IF((A1:J1="A*")+(A1:J1="A")+(A1:J1="B")+(A1:J1="C"), 1, 0))}
>
>However, as I said I need it to apply to just cells A1,C1,E1. I have tried
>entering this as a comma seperated list eg, (A1,C1,E1="A*). However this
>doesn't work as it claims there is a value error (Like everything that goes
>wrong on Excel)
>
>Any ideas? I have put the correct syntax and the array is in the correct
>place. I have many otherworksheets with this formula working through an
>entire range but this one is different as I need it through specified cells.
>
>Cheers
>
>Tom

Here's one way:


=SUMPRODUCT((A1={"A*";"A";"B";"C"})+(C1={"A*";"A";"B";"C"})+(E1={"A*";"A";"B";"C"}))

Of course, I would enter the grades you are searching for into a Named range,
and then use this simpler appearing (and editable) formula:

=SUMPRODUCT((A1=Grades)+(C1=Grades)+(E1=Grades))

Make sure there are no blank cells in the named range "Grades"
--ron

This worked very well.

Is there a way to condense the formula as I have 27 different cells in which
I want to include e.g; =SUMPRODUCT((A1=Grades)+(C1=Grades)+(E1=Grades)) x 27

I also have another question that you might be able to help me with:

http://www.microsoft.com/office/comm...c30&sloc=en-us

Hopefully you will be able to do this.

Thanks for your help greatly appreciated. I now have my entire ICT class
grades on a single document and it is much easier to deal with. As I am sure
are aware after a full day at work the last thing you want to be doing is
spending hours putting in kids grades into different spreadsheets.

Tom

"Ron Rosenfeld" wrote:

> On Mon, 9 Jun 2008 07:14:03 -0700, Tom5 <(E-Mail Removed)> wrote:
>
> >I have a worksheet which contains a number of pupils results with gaps
> >inbetween then. I want to put a formula at the end of each row to calculate
> >how many people have A*-C grade.
> >
> >However I can only seem to do this for a selected range instead of choosing
> >the spercific cells I want to include. This is a copy of the formula I have
> >for the entire range;
> >{=SUM(IF((A1:J1="A*")+(A1:J1="A")+(A1:J1="B")+(A1:J1="C"), 1, 0))}
> >
> >However, as I said I need it to apply to just cells A1,C1,E1. I have tried
> >entering this as a comma seperated list eg, (A1,C1,E1="A*). However this
> >doesn't work as it claims there is a value error (Like everything that goes
> >wrong on Excel)
> >
> >Any ideas? I have put the correct syntax and the array is in the correct
> >place. I have many otherworksheets with this formula working through an
> >entire range but this one is different as I need it through specified cells.
> >
> >Cheers
> >
> >Tom
>
> Here's one way:
>
>
> =SUMPRODUCT((A1={"A*";"A";"B";"C"})+(C1={"A*";"A";"B";"C"})+(E1={"A*";"A";"B";"C"}))
>
> Of course, I would enter the grades you are searching for into a Named range,
> and then use this simpler appearing (and editable) formula:
>
> =SUMPRODUCT((A1=Grades)+(C1=Grades)+(E1=Grades))
>
> Make sure there are no blank cells in the named range "Grades"
> --ron
>

On Tue, 10 Jun 2008 01:37:01 -0700, Tom5 <(E-Mail Removed)>
wrote:

>Is there a way to condense the formula as I have 27 different cells in which
>I want to include e.g; =SUMPRODUCT((A1=Grades)+(C1=Grades)+(E1=Grades)) x 27

You could write the entire thing as a UDF, but I think it would be more useful
to write a UDF that will join non-contiguous cells into an array and then use
that UDF in a formula.

To write the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and copy the
code below into the window that opens. The formula will accept up to 29
arguments.

Then use this **array-entered** formula (enter with <ctrl><shift><enter> ) into
some cell:

=SUM(--(ARRAYJOIN(A1,C1,C2,E1)=Grades))

The above assumes that Grades is in a vertical array. If Grades is in a
horizontal array, then you will need:

=SUM(--(ARRAYJOIN(A1,C1,C2,E1)=TRANSPOSE(Grades)))


Excel will place braces {...} around the formula if you did it correctly.

==============================
Function ArrayJoin(ParamArray CellRefs())
ArrayJoin = CellRefs
End Function
===============================

Laurent Longre has a free add-in: morefunc.xll which includes a similar
function, along with many other useful functions, but that site is "down" this
morning with a Error 403 http://xcell05.free.fr/

If available, you could download and install that add-in, and then use

=SUM(--(ARRAYJOIN(A1,C1,C2,E1)=TRANSPOSE(Grades)))

Note that ARRAYJOIN and ARRAY.JOIN are subtly different in that the first
returns a horizontal and the latter a vertical array, so the requirement for
TRANSPOSE is reversed.

The advantage of installing morefunc.xll is that it provides access to a number
of other useful functions.
--ron

On Tue, 10 Jun 2008 01:37:01 -0700, Tom5 <(E-Mail Removed)>
wrote:

>I also have another question that you might be able to help me with:
>
>http://www.microsoft.com/office/comm...c30&sloc=en-us
>
>Hopefully you will be able to do this.

Interesting.

Although I see the posting at the link, it does not show up in my newsreader
(Agent). I don't understand why.

In any event, your problem is soluble, although not clear.

When you write: "Two grades lower than ..." I have assumed you mean two grades
down in the NAME'd list of Grades. So if your list is:

Grades
A*
A
B
C
D
E
F

Then "B" would be two lower than "A*"

I have also assumed that your list of grades for each student is in a row, and
also in discontiguous cells (as in your other problem), starting with column C,
and that the predicted grade is in column B.

Given that setup, the conditional format formula for the individual grade cells
is:

=OR(C2>"C",(MATCH(C2,Grades,0)-MATCH($B$2,Grades,0))>=2)

If your Predicted Grade is different for each actual grade, you will need to
modify the above.

With regard to the student name cell, your formula will need to test the
individual grade cells against the criteria used for those cells.

=SUMPRODUCT(OR(C2>"C",(MATCH(C2,Grades,0)-MATCH($B$2,Grades,0))>=2)+
OR(E2>"C",(MATCH(E2,Grades,0)-MATCH($B$2,Grades,0))>=2)+
OR(G2>"C",(MATCH(G2,Grades,0)-MATCH($B$2,Grades,0))>=2))

You may find it more convenient to put this formula into a separate cell, and
then test that cell.

--ron