#VALUE error
 

I am using Excel from Office 2007.

I transfered a spreadsheet program from a Works spreadsheet, which I had
adapted from one supplied with the program. It calculates your mortgage
payments. I adapted it to calculate ROI from several variables on prespective
rental properties.

In Excel I get a #VALUE error when a cell conditionally references another
cell that references another cell.

I have attached several of the cell functions.
=IF(AND(F5,F6,F7),PMT((F6/12)/100,F11,-F5),"")

=IF(AND(G8,G9,G10),PMT((G9/12)/100,G13,-G8),"")

=IF(G24,G24/G5,"")

The online help says that I shouldn't reference a function, but I need the
return of that cell's function to complete another function in another cell.

The cell may return my monthly mortgage payment. I then use that product and
the product of say property taxes/12, management fees and yearly repair
expenses/12 to calculate my estimated monthly expenses on a property. I can
then calculate my free cash flow from that property for the month.

If the cell referenced is blank, I'd like the cell referencing it to remain
blank too.

The spread sheet seems to work properly when the variable cells are filled,
but I want the cells doing a calculation to remain blank if there are no
values in the other cells.

If someone can also point me to where I can read more extensively on the
matter, I would be most grateful.

--
YMHS

Radar

#VALUE error
 

Some thoughts:
In Excel I get a #VALUE error when a cell conditionally
references another cell that references another cell.

Yes, if you've got error value/s upstream, that will likely propagate to all
downstream formulas pointing to it

Perhaps as an example, to replace your:
=IF(AND(F5,F6,F7),PMT((F6/12)/100,F11,-F5),"")

you could try trapping all the upstream cells using COUNT as a check that
these contain numbers before evaluating further:
=IF(COUNT(F5,F6,F7,F11)<4,"",PMT((F6/12)/100,F11,-F5))

I'm not sure how F7 figures in your calc: PMT((F6/12)/100,F11,-F5)
but since you showed it in the AND part, I just included it.

The "<4" in: =IF(COUNT(F5,F6,F7,F11)<4,"",
is the check for numbers to be present in all 4 cells within the COUNT

You could replace COUNT with COUNTA for checks involving a combination of
text/numbers, eg: =IF(COUNTA(F5,F6,F7,F11)<4,"", ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Radar" wrote:
I am using Excel from Office 2007.

I transfered a spreadsheet program from a Works spreadsheet, which I had
adapted from one supplied with the program. It calculates your mortgage
payments. I adapted it to calculate ROI from several variables on prespective
rental properties.

In Excel I get a #VALUE error when a cell conditionally references another
cell that references another cell.

I have attached several of the cell functions.
=IF(AND(F5,F6,F7),PMT((F6/12)/100,F11,-F5),"")

=IF(AND(G8,G9,G10),PMT((G9/12)/100,G13,-G8),"")

=IF(G24,G24/G5,"")

The online help says that I shouldn't reference a function, but I need the
return of that cell's function to complete another function in another cell.

The cell may return my monthly mortgage payment. I then use that product and
the product of say property taxes/12, management fees and yearly repair
expenses/12 to calculate my estimated monthly expenses on a property. I can
then calculate my free cash flow from that property for the month.

If the cell referenced is blank, I'd like the cell referencing it to remain
blank too.

The spread sheet seems to work properly when the variable cells are filled,
but I want the cells doing a calculation to remain blank if there are no
values in the other cells.

If someone can also point me to where I can read more extensively on the
matter, I would be most grateful.

--
YMHS

Radar

#VALUE error
 

=if(iserror(IF(AND(F5,F6,F7),PMT((F6/12)/100,F11,-F5),"")= true, "",
=IF(AND(F5,F6,F7),PMT((F6/12)/100,F11,-F5),"")))

This will not return a value if the formula results in an error and you
won't see the #error value in the cells.

Roger

"Max" wrote:

Some thoughts:
In Excel I get a #VALUE error when a cell conditionally
references another cell that references another cell.

Yes, if you've got error value/s upstream, that will likely propagate to all
downstream formulas pointing to it

Perhaps as an example, to replace your:
=IF(AND(F5,F6,F7),PMT((F6/12)/100,F11,-F5),"")

you could try trapping all the upstream cells using COUNT as a check that
these contain numbers before evaluating further:
=IF(COUNT(F5,F6,F7,F11)<4,"",PMT((F6/12)/100,F11,-F5))

I'm not sure how F7 figures in your calc: PMT((F6/12)/100,F11,-F5)
but since you showed it in the AND part, I just included it.

The "<4" in: =IF(COUNT(F5,F6,F7,F11)<4,"",
is the check for numbers to be present in all 4 cells within the COUNT

You could replace COUNT with COUNTA for checks involving a combination of
text/numbers, eg: =IF(COUNTA(F5,F6,F7,F11)<4,"", ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Radar" wrote:
I am using Excel from Office 2007.

I transfered a spreadsheet program from a Works spreadsheet, which I had
adapted from one supplied with the program. It calculates your mortgage
payments. I adapted it to calculate ROI from several variables on prespective
rental properties.

In Excel I get a #VALUE error when a cell conditionally references another
cell that references another cell.

I have attached several of the cell functions.
=IF(AND(F5,F6,F7),PMT((F6/12)/100,F11,-F5),"")

=IF(AND(G8,G9,G10),PMT((G9/12)/100,G13,-G8),"")

=IF(G24,G24/G5,"")

The online help says that I shouldn't reference a function, but I need the
return of that cell's function to complete another function in another cell.

The cell may return my monthly mortgage payment. I then use that product and
the product of say property taxes/12, management fees and yearly repair
expenses/12 to calculate my estimated monthly expenses on a property. I can
then calculate my free cash flow from that property for the month.

If the cell referenced is blank, I'd like the cell referencing it to remain
blank too.

The spread sheet seems to work properly when the variable cells are filled,
but I want the cells doing a calculation to remain blank if there are no
values in the other cells.

If someone can also point me to where I can read more extensively on the
matter, I would be most grateful.

--
YMHS

Radar