Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-07-2019, 01:15 PM
will soar will soar is offline Problem> do not include 999 in calculations Windows 10 Problem> do not include 999 in calculations Office 2016
Novice
Problem> do not include 999 in calculations
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default 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
File Type: xlsx 999calc.xlsx (9.2 KB, 7 views)
Reply With Quote
  #2  
Old 05-07-2019, 03:08 PM
Alansidman's Avatar
Alansidman Alansidman is offline Problem> do not include 999 in calculations Windows 10 Problem> do not include 999 in calculations Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

Use this formula to exclude 999 from the calculation

=AVERAGEIF(B2:J2,"<>999") and copy down.
Reply With Quote
  #3  
Old 05-07-2019, 04:13 PM
will soar will soar is offline Problem&gt; do not include 999 in calculations Windows 10 Problem&gt; do not include 999 in calculations Office 2016
Novice
Problem&gt; do not include 999 in calculations
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default

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.

Thank you for your help,
Attached Files
File Type: xlsx 999calc2.xlsx (10.6 KB, 10 views)
Reply With Quote
  #4  
Old 05-07-2019, 08:06 PM
Alansidman's Avatar
Alansidman Alansidman is offline Problem&gt; do not include 999 in calculations Windows 10 Problem&gt; do not include 999 in calculations Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

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.
Reply With Quote
  #5  
Old 05-07-2019, 10:37 PM
ArviLaanemets ArviLaanemets is offline Problem&gt; do not include 999 in calculations Windows 8 Problem&gt; do not include 999 in calculations Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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
  #6  
Old 05-09-2019, 12:37 PM
will soar will soar is offline Problem&gt; do not include 999 in calculations Windows 10 Problem&gt; do not include 999 in calculations Office 2016
Novice
Problem&gt; do not include 999 in calculations
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default

Quote:
Originally Posted by Alansidman View Post
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.
Reply With Quote
  #7  
Old 05-09-2019, 01:13 PM
will soar will soar is offline Problem&gt; do not include 999 in calculations Windows 10 Problem&gt; do not include 999 in calculations Office 2016
Novice
Problem&gt; do not include 999 in calculations
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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
Reply With Quote
  #8  
Old 05-09-2019, 01:27 PM
ArviLaanemets ArviLaanemets is offline Problem&gt; do not include 999 in calculations Windows 8 Problem&gt; do not include 999 in calculations Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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,"")
Reply With Quote
  #9  
Old 05-09-2019, 01:42 PM
will soar will soar is offline Problem&gt; do not include 999 in calculations Windows 10 Problem&gt; do not include 999 in calculations Office 2016
Novice
Problem&gt; do not include 999 in calculations
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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?
Reply With Quote
  #10  
Old 05-09-2019, 11:29 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Problem&gt; do not include 999 in calculations Windows 7 64bit Problem&gt; do not include 999 in calculations Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@will soar
please do not quote entire posts unnecessarily.
They clutter the thread and make it hard to read.
Use "quick reply" to avoid quotes.
Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #11  
Old 05-10-2019, 12:17 AM
ArviLaanemets ArviLaanemets is offline Problem&gt; do not include 999 in calculations Windows 8 Problem&gt; do not include 999 in calculations Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #12  
Old 05-26-2019, 01:16 PM
will soar will soar is offline Problem&gt; do not include 999 in calculations Windows 10 Problem&gt; do not include 999 in calculations Office 2016
Novice
Problem&gt; do not include 999 in calculations
 
Join Date: Feb 2017
Posts: 14
will soar is on a distinguished road
Default

Thank you for your help
Reply With Quote
Reply

Thread Tools
Display Modes


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
Problem&gt; do not include 999 in calculations Calculations etobias Word 4 12-03-2015 01:36 PM
Problem&gt; do not include 999 in calculations 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft