#1
|
|||
|
|||
Formula Assistance for restricting the number of for analysis
We have a long rows of data among many names. We want to retain the historical input, but for analysis want to limit the data use in the formula to only the last ten inputs. Some rows have only one input and others as many as 100. For analysis only the last (most current) 1 to 10 data points needed. What formula can be used?
|
#2
|
|||
|
|||
Are there blanks between most current 10 data points?
|
#3
|
|||
|
|||
Yes, there are blanks between many of the data points in the row. What I am trying to accomplish is to sum the most current data points, but not more that the last 10. One would be the minimum and ten would be the maximum. That sum would then need to be divided by the number of data points to obtain an average. Currently, all data points are being summed. Then the number of data points for each is captured. Lastly a simple divide results in a percentage. Though the dates are fixed for data entry, there may or may not be data in a consecutive order. In fact, some rows have multiple gaps in data yet there is enough data point to exceed 10.
|
#4
|
|||
|
|||
With your data in column A try this array formula:
=SUM(INDEX(A:A,LARGE(IF(ISBLANK(A1:A100),0,ROW(1:1 00)),10)):A100) When entering the formula remember to hold down Ctrl and Shift before pressing Enter. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need assistance with multiple IF argument formula | Btrrcup610 | Excel | 1 | 02-25-2016 11:14 AM |
Assistance with Word 2010 If formula | ciresuark | Word | 1 | 03-09-2015 12:57 PM |
Formula to add number of pallets shipped | piglovindillard | Excel | 2 | 08-13-2012 06:40 AM |
Restricting paragraph styles without restricting character styles | Red Pill | Word | 5 | 05-25-2012 01:06 PM |
Formula assistance please | blazzercat | Excel | 2 | 07-08-2009 08:45 AM |