using an NPV formula

[Tom]

=NPV(After_tax_investment_rate,I14:I23)

The problem I am having is that the I14:I23 needs to vary. In one instance
it needs to be I14:I50 another time I14:I20. Is there a way to automate that
so it automatically sees the different length on the list and enters it
within those brackets? Help.....

Thanks in advance.
Tom

 

[JE McGimpsey]

See

http://cpearson.com/excel/named.htm#Dynamic

 

[Tom]

That's quite tough. I tried what it said but didn't work. got error
messages etc. Is there another way or a way to explain it?

 

[hrlngrv]

Tom wrote...

=NPV(After_tax_investment_rate,I14:I23)

The problem I am having is that the I14:I23 needs to vary. In one instance
it needs to be I14:I50 another time I14:I20. Is there a way to automate that
so it automatically sees the different length on the list and enters it
within those brackets? Help.....

If the range of cashflows always begins in I14 and if cells would be
blank or zero or nonnumeric when they shouldn't be included, then just
use the range for the largest set of cashflows you'd need to
accomodate. So if there would be at most 60 periods starting with the
one in I14, use

=NPV(After_tax_investment_rate,I14:I73)

If the range could contain nonzero numbers which shouldn't be included,
how would any formula know where to cut off?

 

[NewsMan]

=NPV(After_tax_investment_rate,I14:I23)

The problem I am having is that the I14:I23 needs to vary. In one instance
it needs to be I14:I50 another time I14:I20. Is there a way to automate that
so it automatically sees the different length on the list and enters it
within those brackets? Help.....

Thanks in advance.
Tom

Here is the formula, it should work. Assume your data starts at I14 and
D1 is your investement rate.
=NPV(D1,(OFFSET(Sheet1!$i$14,0,0,COUNTA(Sheet1!$I:$I),1)))