Error or Not?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DavidB - 07 Nov 2006 01:24 GMT

Could someone have a look at this for me and give me feedback please
=IF(OR(V8="",X8=""),"",IF(OR(V8="R",X8="R"),"R",IF(OR(V8=49,X8=49),"NGP",SUM(V8*0.18,X8*0.82)/100)))

Q8 is the destination cell so I want Q8 to produce NGP only if both V8 & X8
=NGP by entering 49 into both V8 & X8. However, if for argument sake V8 has
49 and X8 has 90 as it stands now Q8 still reads NGP but I want it to
actually calculate the score based on the SUM above and not give me an
NGP.... obviously the formula needs changing but I can't get my head around
excactly what needs to change.

Cheers
David

Reply to this Message

JMB - 07 Nov 2006 01:35 GMT

Use AND, not OR.

> Could someone have a look at this for me and give me feedback please:
> =IF(OR(V8="",X8=""),"",IF(OR(V8="R",X8="R"),"R",IF(OR(V8=49,X8=49),"NGP",SUM(V8*0.18,X8*0.82)/100)))
[quoted text clipped - 8 lines]
> Cheers
> David

Reply to this Message

Max - 07 Nov 2006 01:35 GMT

Perhaps just change: .. OR(V8=49,X8=49)
to: .. AND(V8=49,X8=49)

Try it as:
=IF(OR(V8="",X8=""),"",IF(OR(V8="R",X8="R"),"R",IF(AND(V8=49,X8=49),"NGP",SUM(V8*0.18,X8*0.82)/100)))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

DavidB - 07 Nov 2006 04:47 GMT

Thanks JMB and Max extremely helpful.
I have one more small problem and that is this formula:
=IF(V8:V50="","",SUM(V8:V50/43))
I understand the principle of AVERAGE but I get that #VALUE! in the
destination cell V51. Is this a format issue? can V51 be blanked so that it
shows nothing?

Cheers
David

> Perhaps just change: .. OR(V8=49,X8=49)
> to: .. AND(V8=49,X8=49)
[quoted text clipped - 13 lines]
> > Cheers
> > David

Reply to this Message

Max - 07 Nov 2006 05:08 GMT

> =IF(V8:V50="","",SUM(V8:V50/43))

One way is to try it as:
=IF(COUNT(V8:V50)=0,"",SUM(V8:V50)/43)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks JMB and Max extremely helpful.
> I have one more small problem and that is this formula:
[quoted text clipped - 5 lines]
> Cheers
> David

Reply to this Message

Max - 07 Nov 2006 05:35 GMT

> =IF(COUNT(V8:V50)=0,"",SUM(V8:V50)/43)

A variation of the above,
which avoids having to "manually" count the "43":
=IF(COUNT(V8:V50)=0,"",SUM(V8:V50)/ROWS(V8:V50))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Rate this thread: