Variable length array that exludes (possible) blanks in the middle
 

Hi,
I am trying to automate some formula's to prevent having to manually
adjustment them every time the sheet is used but encountered a problem when
it came to a varriable length array where it is possible to have blanks in
the middle of the array that need to be exluded.

To help set the scene a bit:
* The formulas I am haing trouble with are NPV and IRR calcualtions that
link to the reuslts of various other formulas.

What I have done so far:
* defined the row with the cashflows as a named range using an offset
formula combined with a countif <0 so that the width of the array can varry
automatically. A copy is below:
=OFFSET('Advance Purchase Economics'!$D$31:$K$31,0,0,1,COUNTIF('Advance
Purchase Economics'!$D$31:$K$31,"<0"))
Where cells D31:k31 contain the cashflow results
* The problem is the cashflow results could have a value in cell D31, but
E31 could be blank, and F31 has a value.

My offset formula doesn't work in this situation. If I include all cashflow
cells the IRR appears to works till but the NPV calculation does not. I need
to be able to only include cells have values in them.

NPV formula:
=NPV(IRate_PY/12,Cashflows)+C31
IRate_PY = interest rate percentage per year.

Does anyone have any advice on how to fix the formula?

Thanks in advance to anyone who can help!!
Emma

Variable length array that exludes (possible) blanks in the middle
 

IGNORE - SORTED OUT
"Emma" wrote:

Hi,
I am trying to automate some formula's to prevent having to manually
adjustment them every time the sheet is used but encountered a problem when
it came to a varriable length array where it is possible to have blanks in
the middle of the array that need to be exluded.

To help set the scene a bit:
* The formulas I am haing trouble with are NPV and IRR calcualtions that
link to the reuslts of various other formulas.

What I have done so far:
* defined the row with the cashflows as a named range using an offset
formula combined with a countif <0 so that the width of the array can varry
automatically. A copy is below:
=OFFSET('Advance Purchase Economics'!$D$31:$K$31,0,0,1,COUNTIF('Advance
Purchase Economics'!$D$31:$K$31,"<0"))
Where cells D31:k31 contain the cashflow results
* The problem is the cashflow results could have a value in cell D31, but
E31 could be blank, and F31 has a value.

My offset formula doesn't work in this situation. If I include all cashflow
cells the IRR appears to works till but the NPV calculation does not. I need
to be able to only include cells have values in them.

NPV formula:
=NPV(IRate_PY/12,Cashflows)+C31
IRate_PY = interest rate percentage per year.

Does anyone have any advice on how to fix the formula?

Thanks in advance to anyone who can help!!
Emma