Hello!
I need to create a running average basically.
This is going to be a different length for each trial though, and will need to reset when a certain column (Lag1) is equal to 0.
I generally will need the CORRECTNESS column to average down and then when Lag1 equals 1 to stop and start over with the averaging process.
Lag1 CORRECTNESS
0 0
1 1
1 0
1 1
1 1
1 0
Can you attach a sample workbook with a mock-up of what you expect as your result.
Go Advanced -> Manage Attachments -> Upload
Here ya go.
Why a word document?
I didn't think it mattered, so I just took a screenshot and pasted into a word doc.
Since you have the workbook you can upload an Excel file the same way. Saves retyping data and if you already have some formulas in play it helps to interpret what you are trying to do.
Dave
I don't currently have any formulas in play that would be displayed here. This is a very small subset of the overall dataset.
Thanks
I suspect we are going to need a larger data set, but try this.Formula:
Please Login or Register to view this content.
So, here is what it is doing with a slightly larger section of my dataset.
Everything is working well, but it is not completely resetting after the Lag1 variable is equal to 0.
The new variable does equal 0 at that point, which is great, but I would need the average to restart as well.
In this example that would mean that the first slot after the Lag1 restarts should be 1.
Try thisFormula:Please Login or Register to view this content.
Last edited by FlameRetired; 07-18-2018 at 02:15 AM.
Great! That worked beautifully.
I am getting an error that says the formula is omitting adjacent cells.
Is that fine?
Without seeing it I can not comment.
See where the formula is entered in column D of the attached.
It appears as though yours is coming up with the same comment in your new column D.
That's not happening at my end.
In which cell / row does it occur?
Edit If you are talking about the little green "triangles" in the upper left corner those are because under Options > Formulas > Error checking rules the "Formulas which omit cells in a region" is likely checked. Try unchecking that and see if it persists.
Last edited by FlameRetired; 07-18-2018 at 11:16 PM.
That is exactly what was happening. Thank you so much!
You are welcome.
So I will need your assistance yet again.
I am using this formula that you figured out for me on my second dataset and I am getting values that are outside of the realm of possibility for an average and am not sure why.
I tried using the exact formula elsewhere and it works as intended. Hopefully I can get your assistance again.
I attached the document to this post. It is on sheet 2 and you have to scroll over to find the data that I left (in order to keep my other formulas in tact).
There is no Sheet2 in the upload. Any chance it's not the right file?
Okay. Well hopefully this works
In the previous examples all the CORRECTNESS sections breaking on Lag1=0 started with 0. The sums were always less than the number of rows. That is not the case with this sample. They all start with 1. In fact the only CORRECTNESS values are all 1s.
Perhaps I misunderstood the first example set. If you replace that formula withyou will get all 1s for the averages (except at the Lag1=0 rows). This seems unlikely.Formula:Please Login or Register to view this content.
What results were you expecting?
This task is significantly easier, which is what would have been expected, than the first dataset that you saw.
It is not surprising that there are a large amount of 1s in this average column. When I look further down the list there are people who did not have a perfect run with this task, but it is by far not as common as it was in the previous dataset.
Thank you so much! That formula worked just great!
You are welcome.
Edit By-the-way that formula is resource heavy. It takes a long time to calculate that much data. If that is always the case ... or there are larger sets than that ... I would recommend some helper columns to limit the recalculations load.
Last edited by FlameRetired; 07-19-2018 at 11:35 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks