Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-06-2019, 01:46 PM
JARVAN JARVAN is offline Running Average Windows 10 Running Average Office 2016
Novice
Running Average
 
Join Date: Feb 2019
Posts: 3
JARVAN is on a distinguished road
Question Running Average

Good afternoon. I am relatively inexperienced with Excel, but am tasked to create a spreadsheet in which the goal is to have running averages for columns. I am running Excel 2016 on Windows 10.

The answer choices for the column are "yes" "no" and I want a running average of the "yes" answers.

I found some tutorials on using COUNTIF/COUNTA. But, all the examples show using a defined data range and I this is an open-ended column, hence my need for a running average that would recalculate when a new cell is populated.

I hope this makes sense.



Is someone able to assist with a formula that I can use for this?

Much appreciated.
Reply With Quote
  #2  
Old 02-06-2019, 09:39 PM
xor xor is offline Running Average Windows 10 Running Average Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe like this
Attached Files
File Type: xlsx Average_yes.xlsx (10.4 KB, 10 views)
Reply With Quote
  #3  
Old 02-07-2019, 12:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Running Average Windows 7 64bit Running Average 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

To use a dynamic range, and based on Xor's example ( as you did not provide a sample hseet), select B2:C21 -Click - Insert - click Table - click " My table has headers " (or not) - Ok

Realign Xor's result range
Now when you enter more data the formula will adapt automatically
Beware that when you enter a "yes" in the first column and nothing in the second, the function will recalculate based on a zero value for col C
To avoid this use =IF(OR(B3="",C3=""),"",AVERAGEIF($B$3:B3,"yes",$C$ 3:C3))
__________________
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
  #4  
Old 02-07-2019, 12:32 AM
ArviLaanemets ArviLaanemets is offline Running Average Windows 8 Running Average 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

Do you have a column with some numeric values, and another column with yes/no? And you want an average of numeric values with "yes" in another column 'until' current record?

The problem is, how do you define this 'until'! When it is simply position of entry in table, then any sorting you are making will recalculate this average, and it will be different compared with average the same entry did have before.

When you have a column with dates (without hours) in your table, you can base your running average on those dates. But there is no way to decide which entry was earlier for cases with several entries in same date - you will have same running average for all entries with 'yes' on this date.

The best case will be, when you have some unique ordered identifier in your table, like entry registering date and time, or entry number - entered manually or through VBA code. Then you can calculate a real running average for every entry - and this will remain same unless you enter forcibly some earlier entry.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Running Average Calculate average age FTL Excel 1 08-09-2016 06:56 AM
Running Average Average if and sumif jennamae Excel 4 01-17-2014 05:10 AM
Average for the month joflow21 Excel 4 10-21-2013 06:57 AM
'AVERAGE' Formula nfphilpot Excel 3 11-24-2010 02:19 PM
Running Average Average of many rows speedycorn1 Excel 1 10-30-2010 07:54 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:42 PM.


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