Formula Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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="",BG13="",BT13="",BY13="",CA13="",CE13="",CG13="",AB13="N",AF13="N",AH13="N",AJ13="N",AT13="N",BG13="N",BT13="N",BY13="N",CA13="N",CE13="N",CG13="N",AB13="R",AF13="R",AH13="R",AJ13="R",AT13="R",BG13="R",BT13="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,BG13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG13*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 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="",BF13="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",IF(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,AJ13*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
 
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.
 
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
 
Sorry I forgot to paste the formula....
=IF(OR(AB13="",AF13="",AH13="",AJ13="",AT13="",BF13="",BT13="",BY13="",CA13="",CE13="",CG13=""),"",IF(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,AJ13*0.2,AT13*0.2,BF13*0.2,BT13*0.2,BY13*0.11,CA13*0.13,CE13*0.08,CG13*0.2)/100)))
 
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.
 
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.
 
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
 
Back
Top