Sorry about this Roger,
I tried this
but...=IF(AI13&AW13&BI13&BW13&CJ13="N","",IF(AI13& AW13&BI13&BW13&CJ13="R","",SUM(AI13/100)+(AW13/100)+(BI13/100)+(BW13/100)+(CJ13/100)))
The cell I'm putting this formula into is L13. However, I still get that
(#Value) if one of the reference cells are blank also the last part of this
formula doesn't give me a percentage out of 100 if you get what I mean...what
I'm trying to achieve is, for example, getting 5 different scores say
80,79,31,56,73 to only give me a percentage out of 100....does that make
sense??

Cheers
David

DavidB,
The answer you get in cell "L3" is 3.19 isn't it.
If the individual points possible for each grade is 100, AI13 is 80 out of
100,AW13 is 79 out of 100, so on and so forth.
The formula in "L3" should be "SUM((AI13,AW13,BI13,BW13,CJ13)/500)*100"
Explaination:
1) (AI13,AW13,BI13,BW13,CJ13) = points earned
2) /500 = divided by total possible points
3) *100 = times 100 to give a percent grade
Hope this helps.

"DavidB" wrote:

Sorry about this Roger,
I tried this
but...=IF(AI13&AW13&BI13&BW13&CJ13="N","",IF(AI13& AW13&BI13&BW13&CJ13="R","",SUM(AI13/100)+(AW13/100)+(BI13/100)+(BW13/100)+(CJ13/100)))
The cell I'm putting this formula into is L13. However, I still get that
(#Value) if one of the reference cells are blank also the last part of this
formula doesn't give me a percentage out of 100 if you get what I mean...what
I'm trying to achieve is, for example, getting 5 different scores say
80,79,31,56,73 to only give me a percentage out of 100....does that make
sense??

Cheers
David

Thank you FloMM2 for your help I have pasted ypur formuls on to the end of
mine so now it reads:
=IF(AI13&AW13&BI13&BW13&CJ13="N","",IF(AI13&AW13&B I13&BW13&CJ13="R","",SUM((AI13,AW13,BI13,BW13,CJ13 )/500)*"100"))
....but I'm still getting that #Value thing in cell L13

Cheers
David

"FloMM2" wrote:

DavidB,
The answer you get in cell "L3" is 3.19 isn't it.
If the individual points possible for each grade is 100, AI13 is 80 out of
100,AW13 is 79 out of 100, so on and so forth.
The formula in "L3" should be "SUM((AI13,AW13,BI13,BW13,CJ13)/500)*100"
Explaination:
1) (AI13,AW13,BI13,BW13,CJ13) = points earned
2) /500 = divided by total possible points
3) *100 = times 100 to give a percent grade
Hope this helps.

"DavidB" wrote:

Sorry about this Roger,
I tried this
but...=IF(AI13&AW13&BI13&BW13&CJ13="N","",IF(AI13& AW13&BI13&BW13&CJ13="R","",SUM(AI13/100)+(AW13/100)+(BI13/100)+(BW13/100)+(CJ13/100)))
The cell I'm putting this formula into is L13. However, I still get that
(#Value) if one of the reference cells are blank also the last part of this
formula doesn't give me a percentage out of 100 if you get what I mean...what
I'm trying to achieve is, for example, getting 5 different scores say
80,79,31,56,73 to only give me a percentage out of 100....does that make
sense??

Cheers
David

Hi David

If you just use
=AVERAGE(AI13,AW13,BI13,BW13,CJ13)
that will produce the average result of 63.8.
If you want to express this as a percentage, then put /100 at the end of
the formula and format the cell as percentage.

Average will ignore blank cells, and cells with text in them in
performing its calculations.

If you are saying, that is any of the cells in that range contains an
"N" or an "R" you don't want a result shown then that is a different
matter, so post back.

--
Regards

Roger Govier


"DavidB" wrote in message
...
Sorry about this Roger,
I tried this
but...=IF(AI13&AW13&BI13&BW13&CJ13="N","",IF(AI13& AW13&BI13&BW13&CJ13="R","",SUM(AI13/100)+(AW13/100)+(BI13/100)+(BW13/100)+(CJ13/100)))
The cell I'm putting this formula into is L13. However, I still get
that
(#Value) if one of the reference cells are blank also the last part of
this
formula doesn't give me a percentage out of 100 if you get what I
mean...what
I'm trying to achieve is, for example, getting 5 different scores say
80,79,31,56,73 to only give me a percentage out of 100....does that
make
sense??

Cheers
David

Hi David

If you don't want an average calculated if there is an "N" or an "R" in
your range of cells, then one method might be to just ensure that you
have 5 numeric values
=COUNT((AI13,AW13,BI13,BW13,CJ13)
will return the number of numeric values in your range

If you wanted to produce a zero result if "N" or "R" or a Null value
appeared, then you could use
=AVERAGE(AI13,AW13,BI13,BW13,CJ13)*(COUNT((AI13,AW 13,BI13,BW13,CJ13)=5)

If the count function returned 5, then the result would be True, and the
multiplication of the two functions together would coerce the value of
True to 1 so the result would stand.
If the value of Count() was less than 5, the result would be False,
which would be coerced to Zero so the whole result would become 0.

Note. This would not produce a result if there were any blanks cells
within the range.

If you are happy to have an average based upon less than 5 results,
provided there are no text characters in your range, then you could use
a test including COUNTA.
=COUNTA((AI13,AW13,BI13,BW13,CJ13)
will return the total of all cells with content, either numeric or text
values

In this case you would need to multiply your Average by (counta() =
count())

=AVERAGE(AI13,AW13,BI13,BW13,CJ13)*
(COUNT((AI13,AW13,BI13,BW13,CJ13)=COUNT((AI13,AW13 ,BI13,BW13,CJ13))

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi David

If you just use
=AVERAGE(AI13,AW13,BI13,BW13,CJ13)
that will produce the average result of 63.8.
If you want to express this as a percentage, then put /100 at the end
of the formula and format the cell as percentage.

Average will ignore blank cells, and cells with text in them in
performing its calculations.

If you are saying, that is any of the cells in that range contains an
"N" or an "R" you don't want a result shown then that is a different
matter, so post back.

--
Regards

Roger Govier


"DavidB" wrote in message
...
Sorry about this Roger,
I tried this
but...=IF(AI13&AW13&BI13&BW13&CJ13="N","",IF(AI13& AW13&BI13&BW13&CJ13="R","",SUM(AI13/100)+(AW13/100)+(BI13/100)+(BW13/100)+(CJ13/100)))
The cell I'm putting this formula into is L13. However, I still get
that
(#Value) if one of the reference cells are blank also the last part
of this
formula doesn't give me a percentage out of 100 if you get what I
mean...what
I'm trying to achieve is, for example, getting 5 different scores say
80,79,31,56,73 to only give me a percentage out of 100....does that
make
sense??

Cheers
David

Hey there Roger....good to hear from you again. It will take me a few hours
just to figure out what you are trying to explain here....sorry I'm a bit
slow as you know. I still need L13 to stay blank if an R or N are entered
into AI13,AW13,BI13,BW13,CJ13 etc instead of a numeric grade.
I will digest this info tonight over a coffee and apply it to the database
or whatever one calls an excel workbook.
Today in Australia it's very, very hot as usual this time of year with more
to come. Hear from you soon.
Cheers
David

"Roger Govier" wrote:

Hi David

If you don't want an average calculated if there is an "N" or an "R" in
your range of cells, then one method might be to just ensure that you
have 5 numeric values
=COUNT((AI13,AW13,BI13,BW13,CJ13)
will return the number of numeric values in your range

If you wanted to produce a zero result if "N" or "R" or a Null value
appeared, then you could use
=AVERAGE(AI13,AW13,BI13,BW13,CJ13)*(COUNT((AI13,AW 13,BI13,BW13,CJ13)=5)

If the count function returned 5, then the result would be True, and the
multiplication of the two functions together would coerce the value of
True to 1 so the result would stand.
If the value of Count() was less than 5, the result would be False,
which would be coerced to Zero so the whole result would become 0.

Note. This would not produce a result if there were any blanks cells
within the range.

If you are happy to have an average based upon less than 5 results,
provided there are no text characters in your range, then you could use
a test including COUNTA.
=COUNTA((AI13,AW13,BI13,BW13,CJ13)
will return the total of all cells with content, either numeric or text
values

In this case you would need to multiply your Average by (counta() =
count())

=AVERAGE(AI13,AW13,BI13,BW13,CJ13)*
(COUNT((AI13,AW13,BI13,BW13,CJ13)=COUNT((AI13,AW13 ,BI13,BW13,CJ13))

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi David

If you just use
=AVERAGE(AI13,AW13,BI13,BW13,CJ13)
that will produce the average result of 63.8.
If you want to express this as a percentage, then put /100 at the end
of the formula and format the cell as percentage.

Average will ignore blank cells, and cells with text in them in
performing its calculations.

If you are saying, that is any of the cells in that range contains an
"N" or an "R" you don't want a result shown then that is a different
matter, so post back.

--
Regards

Roger Govier


"DavidB" wrote in message
...
Sorry about this Roger,
I tried this
but...=IF(AI13&AW13&BI13&BW13&CJ13="N","",IF(AI13& AW13&BI13&BW13&CJ13="R","",SUM(AI13/100)+(AW13/100)+(BI13/100)+(BW13/100)+(CJ13/100)))
The cell I'm putting this formula into is L13. However, I still get
that
(#Value) if one of the reference cells are blank also the last part
of this
formula doesn't give me a percentage out of 100 if you get what I
mean...what
I'm trying to achieve is, for example, getting 5 different scores say
80,79,31,56,73 to only give me a percentage out of 100....does that
make
sense??

Cheers
David