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
|