Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

Hi David,
You can use up to 30 conditions inside an OR function, I think you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

Thanks Mark, can you look at this and tell me why I'm getting a crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

Hi David,
I have added the number 50 to all relevant cells in the first OR and get the
answer 90. So I think that your cell formattting is producing the unexpected
result. Right-click on the formula cell, choose Format Cells, on the Number
tab, choose General. Does this solve the problem, if not:
-reply with what it says is the format box and
-suggest some realistic numerical data that I can use.
--
Thanks,
MarkN


"DavidB" wrote:

Thanks Mark, can you look at this and tell me why I'm getting a crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

Mark, while I was waiting I messed around with some more with this formula
and now I get a 3 figure % (eg 139%) in H13. However I did try what you just
suggested and when I chose the General option it gave me 138.71%. I would be
more than happy to supply you with realistic data but I'm more than willing
to email you the speadsheet that you might get a better understanding of what
I'm trying to achieve...once you have a look at this speadsheet you'll
understand immediately.

Cheers
David

"MarkN" wrote:

Hi David,
I have added the number 50 to all relevant cells in the first OR and get the
answer 90. So I think that your cell formattting is producing the unexpected
result. Right-click on the formula cell, choose Format Cells, on the Number
tab, choose General. Does this solve the problem, if not:
-reply with what it says is the format box and
-suggest some realistic numerical data that I can use.
--
Thanks,
MarkN


"DavidB" wrote:

Thanks Mark, can you look at this and tell me why I'm getting a crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

Sorry I forgot to paste the formula....
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2)/100)))

"MarkN" wrote:

Hi David,
I have added the number 50 to all relevant cells in the first OR and get the
answer 90. So I think that your cell formattting is producing the unexpected
result. Right-click on the formula cell, choose Format Cells, on the Number
tab, choose General. Does this solve the problem, if not:
-reply with what it says is the format box and
-suggest some realistic numerical data that I can use.
--
Thanks,
MarkN


"DavidB" wrote:

Thanks Mark, can you look at this and tell me why I'm getting a crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

Just before you send it to me, what result are you hoping to get?
--
Thanks,
MarkN


"DavidB" wrote:

Sorry I forgot to paste the formula....
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2)/100)))

"MarkN" wrote:

Hi David,
I have added the number 50 to all relevant cells in the first OR and get the
answer 90. So I think that your cell formattting is producing the unexpected
result. Right-click on the formula cell, choose Format Cells, on the Number
tab, choose General. Does this solve the problem, if not:
-reply with what it says is the format box and
-suggest some realistic numerical data that I can use.
--
Thanks,
MarkN


"DavidB" wrote:

Thanks Mark, can you look at this and tell me why I'm getting a crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

Mark, An average out of 100

Cheers
David

"MarkN" wrote:

Just before you send it to me, what result are you hoping to get?
--
Thanks,
MarkN


"DavidB" wrote:

Sorry I forgot to paste the formula....
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2)/100)))

"MarkN" wrote:

Hi David,
I have added the number 50 to all relevant cells in the first OR and get the
answer 90. So I think that your cell formattting is producing the unexpected
result. Right-click on the formula cell, choose Format Cells, on the Number
tab, choose General. Does this solve the problem, if not:
-reply with what it says is the format box and
-suggest some realistic numerical data that I can use.
--
Thanks,
MarkN


"DavidB" wrote:

Thanks Mark, can you look at this and tell me why I'm getting a crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

Hi David,
Sorry for the delayed response. I think that you will struggle to get the
result you want because you are calculating a series of values, adding these
values up and dividing by 100 will not return an average percentage. The sum
of 10% of 1,000,000 plus 15% of 100 equals 100,000 plus 15. If you add the
two numbers and divide by 100 you end up with 1000.15. I know this doesn't
get you to an answer but I am struggling with how to get the result you want
all in one formula.
--
Thanks,
MarkN


"DavidB" wrote:

Mark, An average out of 100

Cheers
David

"MarkN" wrote:

Just before you send it to me, what result are you hoping to get?
--
Thanks,
MarkN


"DavidB" wrote:

Sorry I forgot to paste the formula....
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2)/100)))

"MarkN" wrote:

Hi David,
I have added the number 50 to all relevant cells in the first OR and get the
answer 90. So I think that your cell formattting is producing the unexpected
result. Right-click on the formula cell, choose Format Cells, on the Number
tab, choose General. Does this solve the problem, if not:
-reply with what it says is the format box and
-suggest some realistic numerical data that I can use.
--
Thanks,
MarkN


"DavidB" wrote:

Thanks Mark, can you look at this and tell me why I'm getting a crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where the error is in
this formula please as I keep getting that dredded error message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination cell where I'm
entering this formula

Cheers
David

I hope someone solves this for you, but, if not ...

I am going back to basics, because I am trying to help.I mean no insult.
To calculate a percentage a value that represents 100% is required. say
MyVal100
Then a value that represents an amount is required. say MyResult.
The percentage is then (MyResult / MyVal) * 100
In Excel there is a percentage format for a cell, that effectively does the
*100 calculation. So entering .5 in an Excel Percetage cell shows as 50%.
Quite often people (including me) enter 50 into such a cell and are
surprised to see 5000%

Another % problem is that people do not understand adding percentages.
e.g. If the boys scored 40% and the girls scored 60% they believe that the
average score of the class is 50%
If there are 1 boy and 29 girls, as there are in some classes, the average
score is actually about 57.7%

I would expect to see a formula along the lines of:
(Total Result group1 + Total Result group2 .... Total result group N) /
(Max possible in group 1 + .... + Max possible in group N)

I do not recognise this in your formula. I am not clear whether you are
trying to do the wrong thing, or trying to do the right thing the wrong
way.

I am seldom alone in such confusion, so if you could clarify what you are
trying to do more of us may be able to help.







"MarkN" wrote in message
...
Hi David,
Sorry for the delayed response. I think that you will struggle to get the
result you want because you are calculating a series of values, adding
these
values up and dividing by 100 will not return an average percentage. The
sum
of 10% of 1,000,000 plus 15% of 100 equals 100,000 plus 15. If you add the
two numbers and divide by 100 you end up with 1000.15. I know this doesn't
get you to an answer but I am struggling with how to get the result you
want
all in one formula.
--
Thanks,
MarkN


"DavidB" wrote:

Mark, An average out of 100

Cheers
David

"MarkN" wrote:

Just before you send it to me, what result are you hoping to get?
--
Thanks,
MarkN


"DavidB" wrote:

Sorry I forgot to paste the formula....
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2)/100)))

"MarkN" wrote:

Hi David,
I have added the number 50 to all relevant cells in the first OR
and get the
answer 90. So I think that your cell formattting is producing the
unexpected
result. Right-click on the formula cell, choose Format Cells, on
the Number
tab, choose General. Does this solve the problem, if not:
-reply with what it says is the format box and
-suggest some realistic numerical data that I can use.
--
Thanks,
MarkN


"DavidB" wrote:

Thanks Mark, can you look at this and tell me why I'm getting a
crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think
you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where
the error is in
this formula please as I keep getting that dredded error
message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination
cell where I'm
entering this formula

Cheers
David

Thanks David but I fear that I will not be able to adequately explain what
I'm trying to achieve. I don't understand it myself. Let me think upon this
and see if I can put it all into English.
I'll get back to you....and thanks again

Cheers
David

"David F Cox" wrote:

I hope someone solves this for you, but, if not ...

I am going back to basics, because I am trying to help.I mean no insult.
To calculate a percentage a value that represents 100% is required. say
MyVal100
Then a value that represents an amount is required. say MyResult.
The percentage is then (MyResult / MyVal) * 100
In Excel there is a percentage format for a cell, that effectively does the
*100 calculation. So entering .5 in an Excel Percetage cell shows as 50%.
Quite often people (including me) enter 50 into such a cell and are
surprised to see 5000%

Another % problem is that people do not understand adding percentages.
e.g. If the boys scored 40% and the girls scored 60% they believe that the
average score of the class is 50%
If there are 1 boy and 29 girls, as there are in some classes, the average
score is actually about 57.7%

I would expect to see a formula along the lines of:
(Total Result group1 + Total Result group2 .... Total result group N) /
(Max possible in group 1 + .... + Max possible in group N)

I do not recognise this in your formula. I am not clear whether you are
trying to do the wrong thing, or trying to do the right thing the wrong
way.

I am seldom alone in such confusion, so if you could clarify what you are
trying to do more of us may be able to help.







"MarkN" wrote in message
...
Hi David,
Sorry for the delayed response. I think that you will struggle to get the
result you want because you are calculating a series of values, adding
these
values up and dividing by 100 will not return an average percentage. The
sum
of 10% of 1,000,000 plus 15% of 100 equals 100,000 plus 15. If you add the
two numbers and divide by 100 you end up with 1000.15. I know this doesn't
get you to an answer but I am struggling with how to get the result you
want
all in one formula.
--
Thanks,
MarkN


"DavidB" wrote:

Mark, An average out of 100

Cheers
David

"MarkN" wrote:

Just before you send it to me, what result are you hoping to get?
--
Thanks,
MarkN


"DavidB" wrote:

Sorry I forgot to paste the formula....
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2)/100)))

"MarkN" wrote:

Hi David,
I have added the number 50 to all relevant cells in the first OR
and get the
answer 90. So I think that your cell formattting is producing the
unexpected
result. Right-click on the formula cell, choose Format Cells, on
the Number
tab, choose General. Does this solve the problem, if not:
-reply with what it says is the format box and
-suggest some realistic numerical data that I can use.
--
Thanks,
MarkN


"DavidB" wrote:

Thanks Mark, can you look at this and tell me why I'm getting a
crazy output
(ie a 5 figure percentage out of 100) in my destination cell H13.
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF1 3="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",I F(OR(AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N", BF13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13= "N"),"",IF(OR(AB13="R",AF13="R",AH13="R",AJ13="R", AT13="R",BF13="R",BT13="R",BY13="R",CA13="R",CE13= "R",CG13="R"),"",SUM(AB13*0.1,AF13*0.2,AH13*0.18,A J13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13* 0.13,CE13*0.08,CG13*0.2))))

Cheers
David

"MarkN" wrote:

Hi David,
You can use up to 30 conditions inside an OR function, I think
you have too
many.
--
Thanks,
MarkN


"DavidB" wrote:

Could some kind person check this for me and tell me where
the error is in
this formula please as I keep getting that dredded error
message "This
Formula Contains An Error"
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BG1 3="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13= "N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",B T13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13=" R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT 13="R",BY13="R",CA13="R",CE13="R",CG13="R"),"",SUM (AB13*0.10,AF13*0.2,AH13*0.18,AJ13*0.20,AT13*0.2,B G13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG1 3*0.2)/100)

All the above are source cells and H13 is the destination
cell where I'm
entering this formula

Cheers
David