Error or Not?

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

Guest

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
 
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)))
 
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
 
=IF(V8:V50="","",SUM(V8:V50/43))

One way is to try it as:
=IF(COUNT(V8:V50)=0,"",SUM(V8:V50)/43)
 
=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))

---
 
Back
Top