#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Maybe like this
|
#3
|
||||
|
||||
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 |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculate average age | FTL | Excel | 1 | 08-09-2016 06:56 AM |
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 |
Average of many rows | speedycorn1 | Excel | 1 | 10-30-2010 07:54 PM |