Hi,
I have a list of monthly investment returns in a column. I'm looking for a formula that will count the number of positive returns for most recent 12 months, such that when I add data in subsequent months, the formula will still work. I can do this with helper columns but it would be much more efficient to use a single formula.
Any help would be appreciated.
Thanks,
Phillycheese
Last edited by Phillycheese5; 09-03-2014 at 04:32 PM.
An example would be nice.
Absent that....
With A1:V5 containing this list:
This regular formula, copied down, begins summing the last up-to-12 positive values in the row
Please Login or Register to view this content.
In that example, these are the results:Please Login or Register to view this content.
492
415
340
367
Is that something you can work with?
Hi Ron,
Thanks for the reply. I'm looking to get a count for the data that is arranged in more of a column structure than across the row. I'm attaching a spreadsheet where you can see at the bottom I'll continue to add months to the dataset. Maybe the formula you provided could be adapted?
Thanks,
Phillycheese
Using your posted workbook, this regular formula returns the count of positive values in the last 12 entries
In your sheet, that formula returns: 9Please Login or Register to view this content.
Is that something you can work with?
Ron,
That works great, thanks!
I appreciate the help!!
Phillycheese
Hello!
The above formula works great, until I tried to adapt it slightly by doing two things:
---adjusting the index reference from the entire column to the range above my computations
---creating a version that counts the most recent negative values for the most recent 12, 36, 60 months
I have attached a file where I am trying to get the formulas (highlighted in orange) to match the results which are highlighted in green.
I'd appreciate any help.
Thanks,
Phillycheese
Got it...
The formulas in your workbook are looking for the last 12 value >0...but you want to count the non-blank cells. Consequently, we need to skip the header row and use these formulas (and copy them across to the right):
Does that help?Please Login or Register to view this content.
Ron,
That solves the issue!
Thank you so much!!
Phillycheese
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks