computations within conditional criteria list extraction - eliminating helper columns

I’ve been thinking about this problem off and on for quite a while, now that I’ve learned a bit more in Excel it’s time to redesign a performance spreadsheet which I use frequently. I’ve always resorted to sorting/filtering manually then copying and pasting into my destination cells which have various formulas set up to perform calculations on the filtered items/list. However I feel as if I’m on the verge of making this a less tedious process if I can figure a few things out.
The long way:
I set up a very simplified dummy spreadsheet compared to the ‘real data’; I import the correct activity records into my destination/calculation sheet. The criteria for which lines need to move are certain activities (withdrawals and deposits) that fall in a particular date range. I have a named range for these codes, “activity_codes”. I’m pretty good with vlookup and sumifs, index/match I’m getting better at and I have used Sumproduct with arrays but I don’t fully understand it yet (although I do understand matrix math, just not in Excel )….
The efficient way:
My ultimate goal is to get a time weighted performance number for a given period. I got that formula down. However there is one component of the performance calculation I would love to arrive at without sorting/filtering/copy/paste or using helper columns if possible. I’m currently stuck on trying to arrive at a sum of time weighted cash flow (WtdCF) without using helper columns. I really just want to design my sheet so that I can upload or reference the raw data without having to copy/paste helper column formulas each time. It’s probably easiest to inspect the time wieghted formulas written in my example. I have a feeling if this is possible the answer will further my knowledge of sumproduct and use of array formulas in Excel.

Thanks in advance for a fruitful discussion

the sum of weighted cash flow is arrived at by taking each relevant activity (need a date and cash flow amount as well), figure out the total number of days into the date range that the activity occurs, add 1 and subtract that number from the total number of days in the date range. Then multiply that number by the cash flow amount then divide by the total number of days in the date range, the resulting number will be the weighted cash flow of that particular activity. I want to arrive at a summation of the weighted cash flows of all the relevant activity in the given date range. my spreadsheet solves this but with the use of helper columns, hopefully there is a way to eliminate the use of the helper columns.

Your questions in cell J20 and J21 are solved here: http://www.excelforum.com/excel-form...med-range.html
Basically you created an array when you decided to use a named range or list as the criteria. You just need to add =SUMPRODUCT() around your SUMIF() and it will work.

Thanks for the quick reply and the helpful help sheet. I will study it tonight. I didn't know you can use line breaks like that in the formula space.

I've been slowly dabbling in VBA, I can usually follow along a pre written code or recorded macro and make minor adjustments/cell references. It doesn't come natural just yet.

Also I was originally thinking haveing something that would automatically copy over the relevant lines .... just didn't know how. I'll research the Index(....small(

Attachment 394546

In this sheet you still need the helper columns in the Data tab, but because of the formulas in column K, you don't need to copy and paste anything. The formulas do all of the filtering and sorting for you.

this works like a charm! I'll mark this thread solved but I may post some follow questions after I review some of the other Ideas mentioned. thanks again!

There are some really great Ideas here, I like your helper files, as I’m taking the sample solution and re building the ‘real’ sheet …(there are actually several instances of the calculation table since it’s taking data from multiple sources, the formats and activity codes are completely different in each source).. it’s helping reinforce how everything works. Nigelbloomy even simplified some of my date math with named ranges I already had in place

As I stumble through trial and error (fixing #name? and # value errors) I have a few follow up questions. For now I’m focusing on the Sum of weighted cash flow single cell formula, I’m saving the index /small idea for completely different sheet/problem where that will likely work wonders .

In the first part of the Sumproduct formula, where you are testing the array to match each of the desired codes individually to get the True/False/1/0 array; do you have to test each code individually? Meaning that potentially my activity code list could be much larger than a handful of codes, for instance there could be hundreds of codes I want to ‘look’ for. Is there a way to get to the true/false/1/0 array using a named range of the activity codes I’m interested in?

I’m trying to build this so that when I get new/fresh activity data, I just upload it into the data tab (in the future I may just wind up referencing a location where the data is stored). Potentially the activity data dimensions could vary greatly. Your formula was giving the array dimension of 2:5000, which will likely be sufficient for current needs, but I could also envision a greater scale where 5000 would be miniscule. Is it not possible to just reference a whole column? Do you always have to eliminate the header row? For now I’m just going to use named ranges and change the dimensions as needed,… just trying to know more about what you can and can’t do. Thanks again!

I did some experimenting, and I have experienced the latency issues with expanded dimensions. Since I want this sheet to perform other tasks I will have keep the array dimensions to a reasonable minimum.

A note to others following along: to avoid #value errors, when you set up named ranges that will be part of the array logic be sure to leave out header rows!