Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 20th 07, 03:30 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 4
Default #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

  #2   Report Post  
Old December 21st 07, 01:54 AM posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default #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

  #3   Report Post  
Old December 22nd 07, 07:12 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 45
Default #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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error: "Excel encountered an error and had to remove some formatti Carl Excel Discussion (Misc queries) 0 September 18th 06 06:39 PM
Counting instances of found text (Excel error? Or user error?) S Davis Excel Worksheet Functions 5 September 12th 06 04:52 PM
I have Error 1919 Error Configuring ODBC dataSource Database Texanna1 Excel Discussion (Misc queries) 1 September 12th 06 06:35 AM
Excel 2003 Macro Error - Runtime error 1004 Cow Excel Discussion (Misc queries) 2 June 7th 05 01:40 PM


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017