Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-07-2019, 01:15 PM
will soar will soar is offline Windows 10 Office 2016
Novice
 
Join Date: Feb 2017
Posts: 10
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, 3 views)
Reply With Quote
  #2  
Old 05-07-2019, 03:08 PM
Alansidman's Avatar
Alansidman Alansidman is offline Windows 10 Office 2019
Novice
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 15
Alansidman is on a distinguished road
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 Windows 10 Office 2016
Novice
 
Join Date: Feb 2017
Posts: 10
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, 6 views)
Reply With Quote
  #4  
Old 05-07-2019, 08:06 PM
Alansidman's Avatar
Alansidman Alansidman is offline Windows 10 Office 2019
Novice
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 15
Alansidman is on a distinguished road
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 Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 436
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
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 Windows 10 Office 2016
Novice
 
Join Date: Feb 2017
Posts: 10
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 Windows 10 Office 2016
Novice
 
Join Date: Feb 2017
Posts: 10
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 Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 436
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
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 Windows 10 Office 2016
Novice
 
Join Date: Feb 2017
Posts: 10
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 Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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
__________________
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.
Reply With Quote
  #11  
Old 05-10-2019, 12:17 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 436
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
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
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
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


All times are GMT -7. The time now is 10:06 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft