How to split an array to take the NPV of a known number of elements?

[max3732]

If you have an array that you've filled with numbers in a previous sub is there a simple way to take a known number of elements a certain number of times to figure out the present value of each one an put it in its own indexed array? The only caveat would be if there are "0's" in the array you would skip over them. There is also a known discount rate.

For example, if an array has 90 numbers and the user inputs 16 and you're doing it twice. You'd need to start from the 1st position in the array that's not zero and then take 16 elements, figure out the NPV of those with the discount rate, put that NPV into the first position of an array. Then you'd look at the 17th element in the array and go till you get a non zero number and then do another 16 elements, figure out the NPV, then put it in the 2nd position of an array that stores the NPV.

It sounds more complicated as I'm writing this than it should be.

I'm using values from other subs and have a lot of variable declarations so I'd have to post the whole complicated worksheet to get it to run, but I'm going to put the relevant part of my attempt below. Even without checking for zeroes the issue I'm having is figuring out how to get the loop to do the number (ex 16) then loop through the next 16 and put it in the next position of the storage array. What I have below figures out the NPV of the 1st 16, but then goes through and does it again and puts it in the 2nd position in the storage array so I have the same values in both.

Is there a simple way to tell VBA to split the array every x items or x items as long as it doesn't find a zero? I'm just getting lose in all these loops.

Please Login or Register  to view this content.

[vba_php]

Is there a simple way to tell VBA to split the array every x items or x items as long as it doesn't find a zero? I'm just getting lose in all these loops.

I don't believe so. but if someone else knows what I don't, they will probably post here. VBA has a function called SPLIT() and the purpose of that is to parse out a list of value separated by a common delimiter. web languages have more advanced functions than VBA does, and obviously for good reason. Honestly, I think you're stuck iterating through your array and conditionally doing what you need. but what about combining code with the NPV() function in the interface? is there a solution with a combo between those 2 I wonder?

[max3732]

Is there a simple way to tell VBA to split the array every x items or x items as long as it doesn't find a zero? I'm just getting lose in all these loops.

I don't believe so. but if someone else knows what I don't, they will probably post here. VBA has a function called SPLIT() and the purpose of that is to parse out a list of value separated by a common delimiter. web languages have more advanced functions than VBA does, and obviously for good reason. Honestly, I think you're stuck iterating through your array and conditionally doing what you need. but what about combining code with the NPV() function in the interface? is there a solution with a combo between those 2 I wonder?

When I saw there was a "SPLIT()" function I thought I could easily split the array, but was dismayed to find it doesn't work like that. I was also hoping that the NPV function would allow me to just put an array with specified start and end or a loop through, but from what I can tell it requires you to put an array that only has the values you want to discount.

[vba_php]

I was also hoping that the NPV function would allow me to just put an array with specified start and end or a loop through

I seriously doubt that. that would be complexity that .net or php would have, and i don't think either of the have it even. did you solve this then?

[Rick Rothstein]

Is there a simple way to tell VBA to split the array every x items or x items as long as it doesn't find a zero? I'm just getting lose in all these loops.

Is the array a one-dimensional array created solely within code or is it a two-dimensional array formed by assigning a range of cells to a variant variable?

What exactly do you mean by "as long as it doesn't find a zero"? Does that mean you want, say, to skip over elements with a zero in them in order to get your 16 elements to calculate with? Or does it mean stop getting elements when you come to one with zero in it?

[max3732]

Is the array a one-dimensional array created solely within code or is it a two-dimensional array formed by assigning a range of cells to a variant variable?

What exactly do you mean by "as long as it doesn't find a zero"? Does that mean you want, say, to skip over elements with a zero in them in order to get your 16 elements to calculate with? Or does it mean stop getting elements when you come to one with zero in it?

It is a public 1 dimensional array created in a previous sub from a 2 dimensional one. It is created from taking a value from a range and then manipulating that within code to fill the array.

What I'd like to do is to skip over the zeroes until you get to another non zero number. The array may have no zeroes or it may have a set number at an interval that comes from a range. For example, it may have 3 zeroes to start, then 16 non zero numbers, then 3 zeroes and then another 16 non zero numbers. I'm trying to get an array or someway to hold the present value of those bunches of 16 numbers that I can work with later.

So I wish I could say "skip the zeroes, take the PV of the next 16 numbers and put them in the 1st position of a new array. Then skip an zeroes and take the PV of the next group of 16 numbers and put them in the 2nd position of the array and continue until the array ends"

[max3732]

What would you think of something like this to split the array? Am I better off making it a sub that I pass arguments to or a function?

Is there a better way to split a 1 dim array?

Please Login or Register  to view this content.

[Rick Rothstein]

So I wish I could say "skip the zeroes, take the PV of the next 16 numbers and put them in the 1st position of a new array. Then skip an zeroes and take the PV of the next group of 16 numbers and put them in the 2nd position of the array and continue until the array ends"

Sorry about the delay in getting back to you... I lost track of this thread on my side.

So are you saying that each group of 16 numbers are always adjacent to each other in the array and that they will always be separated from each other by one or more zero elements?

[Rick Rothstein]

So are you saying that each group of 16 numbers are always adjacent to each other in the array and that they will always be separated from each other by one or more zero elements?

First, let me preface this by saying I am not an accountant nor any other kind of financial type person (I was a Civil Engineer during my working career), so I do not know what NVP is nor how it is to be used... I modelled my reply based on what I read about it in the help files. Okay, now, with that said, assuming what I quoted above is correct, the following snippet of code will grab each group of contiguous values (no matter how many there are in the group) and calculate the NPV for each group of numbers by applying the value stored in the Rate variable via the For..Next loop. With each iteration of the loop, the NVP is calculated so you should do whatever it is you want for it where indicated.

Please Login or Register  to view this content.

[vba_php]

not know what NVP is nor how it is to be used

I'm sure you know this? net present value. it is used widely in the financial world for investment purposes. funny thing is, I don't microsoft caught their error, or maybe the 3 letters were already used as keys? NVP = net value present. but yet, the real concept NPV = net present value. (hmmmmm)

[max3732]

Sorry about the delay in getting back to you... I lost track of this thread on my side.

So are you saying that each group of 16 numbers are always adjacent to each other in the array and that they will always be separated from each other by one or more zero elements?

There will always be a group of non-zero numbers (taken from the excel sheet or user). It could be 3, 13, 16, or any number under 120. That group of numbers will always be separated from each other by any number of zero elements or no zero elements at all. In other words, there could be a group of 20 elements and I'd want to separate it every 20 with no zero to ignore.

[Rick Rothstein]

Ah, that "no zero elements at all" screws up the code I posted as it requires at least one zero value between to work. I can modify the code to do that, but how is the code to know how many elements to use when there is no zero separating them? With at least one zero, my existing code automatically selects the groups no matter how many values make it up... is there a set number of values to use when there is no zeros?

[max3732]

Ah, that "no zero elements at all" screws up the code I posted as it requires at least one zero value between to work. I can modify the code to do that, but how is the code to know how many elements to use when there is no zero separating them? With at least one zero, my existing code automatically selects the groups no matter how many values make it up... is there a set number of values to use when there is no zeros?

There is a variable that contains the number of values to use (ex 14) and another variable that has the number of zeros (ex 0 or 3).

[Rick Rothstein]

See if the code snippet works for you. ValueCount is the number of contiguous non-zero array elements per "group" and ZeroCount is the number of elements in the group equal to zero.

Please Login or Register  to view this content.