Thread: Problem> do not include 999 in calculations
View Single Post
 
Old 05-07-2019, 10:37 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
  
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default
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,""))
Reply With Quote