Formula Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DavidB - 18 Oct 2006 00:39 GMT

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

Reply to this Message

MarkN - 18 Oct 2006 00:53 GMT

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

Signature

Thanks,
MarkN

> 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
[quoted text clipped - 6 lines]
> Cheers
> David

Reply to this Message

DavidB - 18 Oct 2006 02:49 GMT

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,
> You can use up to 30 conditions inside an OR function, I think you have too
[quoted text clipped - 10 lines]
> > Cheers
> > David

Reply to this Message

MarkN - 18 Oct 2006 03:03 GMT

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.

Signature

Thanks,
MarkN

> 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.
[quoted text clipped - 17 lines]
> > > Cheers
> > > David

Reply to this Message

DavidB - 18 Oct 2006 04:10 GMT

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

> Hi David,
> I have added the number 50 to all relevant cells in the first OR and get the
[quoted text clipped - 25 lines]
> > > > Cheers
> > > > David

Reply to this Message

DavidB - 18 Oct 2006 04:11 GMT

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,
> I have added the number 50 to all relevant cells in the first OR and get the
[quoted text clipped - 25 lines]
> > > > Cheers
> > > > David

Reply to this Message

MarkN - 18 Oct 2006 04:46 GMT

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

Signature

Thanks,
MarkN

> 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)))
[quoted text clipped - 28 lines]
> > > > > Cheers
> > > > > David

Reply to this Message

DavidB - 18 Oct 2006 07:50 GMT

Mark, An average out of 100

Cheers
David

> Just before you send it to me, what result are you hoping to get?
>
[quoted text clipped - 30 lines]
> > > > > > Cheers
> > > > > > David

Reply to this Message

MarkN - 19 Oct 2006 00:54 GMT

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.

Signature

Thanks,
MarkN

> Mark, An average out of 100
>
[quoted text clipped - 35 lines]
> > > > > > > Cheers
> > > > > > > David

Reply to this Message

David F Cox - 19 Oct 2006 01:32 GMT

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.

> Hi David,
> Sorry for the delayed response. I think that you will struggle to get the
[quoted text clipped - 55 lines]
>> > > > > > > Cheers
>> > > > > > > David

Reply to this Message

DavidB - 19 Oct 2006 06:12 GMT

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

> I hope someone solves this for you, but, if not ...
>
[quoted text clipped - 84 lines]
> >> > > > > > > Cheers
> >> > > > > > > David

Reply to this Message