Microsoft Office Forums Problem> do not include 999 in calculations
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-07-2019, 01:15 PM
 will soar Windows 10 Office 2016 Novice Join Date: Feb 2017 Posts: 14
Problem> do not include 999 in calculations

Good evening,

I must start by stating I'm not even a novice when it comes to working formulas in excel. I could really use some help, please.

I have attached a file that I am trying to get the MEAN/AVERAGE for the values in the rows. It is part of a questionnaire I use. Some of the questions have been reversed to maintain the interity of the survey. I have added value for a question that is outside the 1-4 response, 999. when I am calculation the averages I do not want to include the 999. I also have some blank cells which need to remain blank, indicating the question was not answered.

another thing I was hoping to see if possible to add to COUNT part at end of formula is that the average only is counted if the number of questions answered, or the number of 999 is greater than 50%.

I would really appreciate any direction and input you may have.

Thank you
Attached Files
 999calc.xlsx (9.2 KB, 3 views)
#2
05-07-2019, 03:08 PM
 Alansidman Windows 10 Office 2019 Novice Join Date: Apr 2019 Location: Steamboat Springs Posts: 27

Use this formula to exclude 999 from the calculation

=AVERAGEIF(B2:J2,"<>999") and copy down.
#3
05-07-2019, 04:13 PM
 will soar Windows 10 Office 2016 Novice Join Date: Feb 2017 Posts: 14

Hi Alansidman,

Thank you for the response. The formula would work if it was a simple calculation. I have attached another file, have a look at row 30, if cell d30 is changed to 0 it will give the true mean for the questionnaire. The reason is some of the questions are scored in reverse order. Likert scale is 1-4 the answer is reversed to 4-1. Look to the formula, it is reversing the answer by calculating cell D30 like so (5-D30). If I remove the 999 and replace it with a blank I get an error. I cannot change the 999 to a blank because some answers have been left blank. If I replace the 999 with a zero I get the true average for the row. I'd like to stick with the 999 because they represent an answer outside the Likert 1-4, which I added.

Attached Files
 999calc2.xlsx (10.6 KB, 6 views)
#4
05-07-2019, 08:06 PM
 Alansidman Windows 10 Office 2019 Novice Join Date: Apr 2019 Location: Steamboat Springs Posts: 27

I don't have a response for you. Hopefully, someone with a better understanding of your scoring methodology will be able to help. Good Luck.
#5
05-07-2019, 10:37 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 455

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,""))`
#6
05-09-2019, 12:37 PM
 will soar Windows 10 Office 2016 Novice Join Date: Feb 2017 Posts: 14

Quote:
 Originally Posted by Alansidman I don't have a response for you. Hopefully, someone with a better understanding of your scoring methodology will be able to help. Good Luck.

Thank you very much for all the help.
#7
05-09-2019, 01:13 PM
 will soar Windows 10 Office 2016 Novice Join Date: Feb 2017 Posts: 14

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
#8
05-09-2019, 01:27 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 455

You got me fully confused! Are you writing about same formula, or another one? And what is denominator?

I'm quessing now. When you want in range Q50:C56 to get the number of non-empty cells, then use the formula
Code:
`=7-COUNTIFS(Q50:C56,"")`
#9
05-09-2019, 01:42 PM
 will soar Windows 10 Office 2016 Novice Join Date: Feb 2017 Posts: 14

Quote:
 Originally Posted by ArviLaanemets You got me fully confused! Are you writing about same formula, or another one? And what is denominator? I'm quessing now. When you want in range Q50:C56 to get the number of non-empty cells, then use the formula Code: `=7-COUNTIFS(Q50:C56,"")`
My Apologies, it is complicated, {if you could only be in my mind: D} I'm working with the formula that you have kindly provided. The denominator is what is used to divide by, so when I'm working the average of all answered questions that is Q50 to Q56 (7 questions) the responses are added and divide by 7. If one of the questions was not answered and were left blank the denominator would be 6 (divide by 6). is it possible to account for the blank in the COUNT(A30:G30)?

Am I making any sense?
#10
05-09-2019, 11:29 PM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,387

@will soar
please do not quote entire posts unnecessarily.
Use "quick reply" to avoid quotes.
Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
#11
05-10-2019, 12:17 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 455

Maybe you load an example workbook up here, with comments about where you want the formula, what the formula must to do, and from where data are read.

Currently you have writing about 2 different ranges (Q50:Q56 and A30:G30), and there is no hint how they are related, or are they related at all. In initial workbook you uploaded the second range is blank!
#12
05-26-2019, 01:16 PM
 will soar Windows 10 Office 2016 Novice Join Date: Feb 2017 Posts: 14

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post etobias Word 2 11-09-2016 10:14 AM etobias Word 1 01-08-2016 01:50 PM etobias Word 4 12-03-2015 01:36 PM lynchbro Excel 5 07-17-2014 11:01 AM SteveF Word 4 09-27-2013 08:34 AM

All times are GMT -7. The time now is 09:52 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top