Does anyone know how to do an NPV over multiple rows without having to sum
the columns. eg
Y1 Y2 Y3
NHU1 -100 100 100 etc
NHU1 -150 200 300 etc
NHU2 -160 140 300 etc
NHU2 -200 180 120 etc

Complete NPV by looking for NHU , summing each year for the NHU over
multiple rows. Obvious way is to total each NHU them NPV the total, or NPV
each row and sumif NPV's, but I dont want to include totals or NPVs in this
worksheet as its a data sheet.

Thanks

Why not nest the formulas, and have the summations of each NHU inside the NPV
formula?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Philh3325" wrote:

Does anyone know how to do an NPV over multiple rows without having to sum
the columns. eg
Y1 Y2 Y3
NHU1 -100 100 100 etc
NHU1 -150 200 300 etc
NHU2 -160 140 300 etc
NHU2 -200 180 120 etc

Complete NPV by looking for NHU , summing each year for the NHU over
multiple rows. Obvious way is to total each NHU them NPV the total, or NPV
each row and sumif NPV's, but I dont want to include totals or NPVs in this
worksheet as its a data sheet.

Thanks

As long as you're not exceeding the limit of NPV parameters, you can use:

=NPV(rate,sumif(A:A,"NHU*",B:B),sumif(A:A,"NHU*",C :C),...)

Regads,
Fred.

"Philh3325" wrote in message
...
Does anyone know how to do an NPV over multiple rows without having to sum
the columns. eg
Y1 Y2 Y3
NHU1 -100 100 100 etc
NHU1 -150 200 300 etc
NHU2 -160 140 300 etc
NHU2 -200 180 120 etc

Complete NPV by looking for NHU , summing each year for the NHU over
multiple rows. Obvious way is to total each NHU them NPV the total, or
NPV
each row and sumif NPV's, but I dont want to include totals or NPVs in
this
worksheet as its a data sheet.

Thanks

Unfortunately the number of years are 30 and the data is in a different
worksheet so when I start to put the formula together it gets too long for
Xcel to handle. I was going to handle the 30th year as +CF/(1+i)^30 but I
wonder if the whole cashflow can somehow be handled this way rather than
using NPV.

"Fred Smith" wrote:

As long as you're not exceeding the limit of NPV parameters, you can use:

=NPV(rate,sumif(A:A,"NHU*",B:B),sumif(A:A,"NHU*",C :C),...)

Regads,
Fred.

"Philh3325" wrote in message
...
Does anyone know how to do an NPV over multiple rows without having to sum
the columns. eg
Y1 Y2 Y3
NHU1 -100 100 100 etc
NHU1 -150 200 300 etc
NHU2 -160 140 300 etc
NHU2 -200 180 120 etc

Complete NPV by looking for NHU , summing each year for the NHU over
multiple rows. Obvious way is to total each NHU them NPV the total, or
NPV
each row and sumif NPV's, but I dont want to include totals or NPVs in
this
worksheet as its a data sheet.

Thanks