Problem> do not include 999 in calculations

ArviLaanemets · #1 05-07-2019, 10:37 PM

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,""))

will soar · #2 05-09-2019, 01:13 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculations	etobias	Word	2	11-09-2016 10:14 AM
Calculations	etobias	Word	1	01-08-2016 01:50 PM
Calculations	etobias	Word	4	12-03-2015 01:36 PM
Tiered Fee Calculations	lynchbro	Excel	5	07-17-2014 11:01 AM
How to do Calculations In a Text Box?	SteveF	Word	4	09-27-2013 08:34 AM