Quote:
Originally Posted by ArviLaanemets
Unless I misunderstood you completely, something like this will do
Code:
=((A$30<>999)*(A$30<>"")*A$30+(B$30<>999)*(B$30<>"")*(5-B$30)+(C$30<>999)*(C$30<>"")*C$30+(D$30<>999)*(D$30<>"")*(5-D$30)+(E$30<>999)*(E$30<>"")*E$30+(F$30<>999)*(F$30<>"")*(5-F$30)+(G$30<>999)*(G$30<>"")*G$30)/(COUNTIFS(A$30:G$30,"<>999")-COUNTIFS(A$30:G$30,""))
|
Hi, this works, you are spot on. Thank you,
Can I complicate things somewhat further?
In the range Q50-Q56, the denominator is 7, if one was blank i.e. not answered the denominator is now six if two were blank the denominator would be 5. Is there a way to write this into the formula? Or could the 999 be changed to a blank for the calculation, by that the 999 equal a blank cell and reduces the denominator?
Thank you for the help