#### #VALUE error

```I am using Excel from Office 2007.

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

```
 0
12/20/2007 3:30:00 AM
excel.newusers 15348 articles. 2 followers.

2 Replies
785 Views

Similar Articles

[PageSpeed] 19

```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
---
> I am using Excel from Office 2007.
>
> 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
>
```
 0
demechanik (4694)
12/21/2007 1:54:01 AM
```=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
> ---
> > I am using Excel from Office 2007.
> >
> > 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
> >
```
 0
12/22/2007 7:12:01 PM

Similar Artilces:

Adding some values on a sheet???
Hello and thanks in advance. I'm working on a calculator for some Cable TV plants designs. Here is what I need: I'd like to have a drop down menu with several different values of design in it. When I click on a value, I'd like it to insert some "value specific" numbers on this type of chart below...... 30 mhz= Ch 5= Ch 40= Ch 78= Ch 80= Ch 116= Any ideas on how I go about that and where to insert and store the info for each type of "value" Cheers! Doug Strinz Hudson Falls, NY Hi Doug Strinz, Assume that you have a worksheet named 'master'. On ...

How to draw 2D values to windows screen??
Suppose now I have a 2D array containing binary values only (1 or 0). I would like to draw these values on the windows screen (created by MFC dialog-based app in MSVC++ 2005) with 0 be black (0, 0, 0) and 1 be white (255, 255, 255). Which MFC class and function should I use to achieve this effect?? Newbie Question wrote: > Suppose now I have a 2D array containing binary values only (1 or 0). I would > like to draw these values on the windows screen (created by MFC dialog-based > app in MSVC++ 2005) with 0 be black (0, 0, 0) and 1 be white (255, 255, 255). > Which MFC class a...

error when reinstalling XP Pro
thHi, I have a Dell Latitude D620. That was having some issues with ethernet and wireless connections. Nothing really found as fas as malware or viruses, without updates of course. I tried to do a windows repair from the XP CD and right when it shows starting windows to choose repair or install windows I get a blue screen with the error... PCI.SYS-address F76130BF base at F76000000 Date stamp 3B7D855C. I tried a few times and even set the BIOS to minimal. Then I pulled the hard drive out and connected to another laptop using a usb cradle, formated the drive and put it back in th...

text value
I need to rank a column containing concatonated names and addresses. Ranking only handles numeric values, is there any way to give text a numeric value? Hi, It would have helped to see some test data but let's start from here. With your data in column A starting in A2 enter this in b2 and drag down =COUNTIF(\$A\$2:\$A\$100,"<"&A2)+1 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "AllyH" wrote: > I need to rank a colum...

Value Axis Scale Change Problem #2

How to retrieve value from specific cell value?
Does anyone have any suggestions on how to retrieve value from specific cell value with specific worksheet? I would like to create a if-statement within macro, If specific cell within specific worksheet = 1 then process following code End If Does anyone have any suggestions? Thanks in advance for any suggestions Eric hi If Sheets("sheet1").Range("A1") = 1 Then MsgBox "run code" Else MsgBox "skip code" End If regards FSt1 "Eric" wrote: > Does anyone have any suggestions on how to retrieve value from specific cell &...

Multiple Value Lookup field
I need to add multiple values to a look up field and I just found out that new to Access 2007 I can add multi-values to my look-up fields. I just tried doing this, but for some reason in the look-up wizard there is no check box that says "Allow Multiple Values", so then I went to the Lookup tab in the field properties to see if there was an Allow Multiple Values property and there was none. Not sure if this would mean anything, but this table was converted from Access 2003 to Access 2007. Why do I not have this option and what I can do about it? On Mon, 12 Nov 2007 12:58:0...

Item Value List vs Item Movement History Report (including cost)
I have been trying to create reports for previous points in time, but I am so confused at this point as to what is what. I've ran an Item Value list, which shows the current value of items, and got an extended cost. Then I ran an Item Movement History Report that includes cost with a filter that begins before the store opened and ends today. The extended cost in this report was very close, but not the same as the extended cost in the Item Value list. Is there something that the Item Movement History Report takes into account that the Item Value list doesn't, or vise versa? Beca...

Unique Values from Access
Dear all, I'm trying to create a validation list with data from access. The code below already do it, but I have a big problem: in the field of access database I can have duplicate data... table: period fields year_month year_quarter year 200701 200701 2007 200702 200701 2007 200703 200701 2007 200704 200702 2007 .. .. .. And the code below brings duplicate data. How can I solve this problem? I wouldn't like to bring all information to excel ...

SOAP error
I am getting this error when I am making SOAP call to a webservice. I am using perl - SOAPLite HTTP/1.1 500 (Internal Server Error) Internal Server Error. Cache-Control: private Date: Tue, 07 Feb 2006 14:49:36 GMT Server: Microsoft-IIS/6.0 Content-Length: 476 Content-Type: text/xml; charset=utf-8 Client-Date: Tue, 07 Feb 2006 14:48:27 GMT Client-Peer: x.x.x.x Client-Response-Num: 1 X-AspNet-Version: 1.1.4322 X-Powered-By: ASP.NET <?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi= "...

SQL 2008 intellisense errors Invalid Object
3 days ago I switched out my old SQL 2005 (SQL_Latin1_General_CP1_CI_AS) server for a new one with SQL 2008(SQL_Latin1_General_CP1_CI_AS). I created the Linked Server to my second server that runs SQL 2005 (Latin1_General_BIN) with the default Linked Server settings. When trying to compile stored procedures from the new SQL 2008 server that calls objects on the second SQL 2005 server I was getting an error (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation) I found that SQL 2008 by...

Error.exe
My (sole) E2003 server reports a couple of 1022 errors each week. I am not particularly worried about these as they are for currently unused accounts, but when I click the 'More information' link from the Event log, it tells me to use error.exe to decode the error. What is error.exe and where can I get hold of it? Thanks I tend to use this: http://www.microsoft.com/downloads/details.aspx?FamilyID=be596899-7bb8-4208-b7fc-09e02a13696c&DisplayLang=en -- Neil Hobson Exchange MVP http://www.msexchange.org/Neil_Hobson/ http://www.msexchangeblog.com "Alistair Taylor" &...

Default values
How can I set up default values for text box fields for new records? Fore example: I would like to set up default value for Country so when user creates new account - country field is populated with that vaue. Thanks, m Hi there You would need to use a picklist field instead of a text field and add a value for each country. You can then set the default country. This is all done in Systom Customisation > Customise Form. Other than this you could use a workflow based on Account Create. Insert a condition to check the country field is null, and insert an action to update the country fi...

VBA Error Message "Compile Error...."
I used a macro from Ron's website to mail each worksheet to the email address in cell a1. I ran this macro and it worked perfectly for one workbook BUT... when I tried to use it for another workbook I got the follwing error message "Microsoft Visual Basic Compile Error Can't find project or library Ok Help". Help me please... do I have to check some more references in the VBA tools? Thanks, Steve Hi Steve Have you set the reference to Outlook in that other workbook also ? You must do that in every workbook or you can use Late binding. See my site for a example --...

DLookup error
I'm trying to reference a control on a subreport in a DLookup formula. This is my formula =(DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Reports![rptcomplaintsPage1]![rptcomplaints].[txtYear]") & [txtrefnbr]) Where rptcomplaintsPage1 is the main report and rptcomplaints is the subreport and the control txtyear is on the subreport. can anyone see where I'm going wrong? I've tried using a ! before txtyear and that doesn't work either. Thanks Tony Solved I missed out the last Report! before the control name and I've been loo...

Can't Assign Value to this Object
I think I've seen this problem before but I can't remember the reason or solution. I have a form (in add mode) whose record source is a single table. For most fields on the form, the user will input data, but for a couple of fields I am trying to populate them with the values of global variables. I've got the global variables working (I can assign one to an unbound text box on the form), but when I try to assign the variable value to a bound text box, I get an error message that "Run-time error 2448: You can't assign a value to this object". Here is ...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

FRx Printer Error
We're trying to change a printer in FRX; but get the error: Error: 0. Occured in: PrintCommonDialog() I did find an earlier thread on this; but the solution did not work.. This is for FRx 6.7 SP10 and GP10 All GP users are Terminal Server Users. Any ideas? Willwonka, I suggest you initially try resolving this issue by visiting KB article 931722 and see which cause may apply to your situation. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "willwonka" wrote: > We're trying to change a print...

Reporting Services error when reinstalling DPM 07
I'm in the process of upgrading DPM 2007 to 2010. I did the O/S upgrade from 2003 to 2008 Server. Now I'm trying to re-install DPM 2007 and I can't get past the prerequisite check. The error is below: Setup has detected that the installation of Reporting Services is not correctly configured or no instance of Reporting Services is linked to of SQL Server. 1)To use the local dedicated MS\$DPM2007\$ instance with DPM, uninstall SQL Server and run setup again. 2)To use the option of installing DPM with an existing instance of SQL Server, make sure that the Reporting ...

Lookup a value in a table and display text if a value exists
I have a customer database form named frmMain with a field called txtAgency. I also have a table that has a column of agency codes that are in the Outlook Global directory. The table is called tbl_Global and consist of about 40 records. If the user inputs an agency that is within this table on the frmMain, I want the phone number field (txtPhone) on the database form to automatically display "See Outlook". If the agency is not within this list, I want the end user to be able input a value on the form. What is the best way to go about this? Use DLookup() to get t...

Need a lookup formula that matches 2 values and returns the 3rd va
I neeed to merge 16000 rows of data with some duplicate vales. Poor record keeping lead to two lists and each was updated independantly if at all. Some orders show up one list and not the other so I can't just sort or do a dup search. I'm trying to recreate this data and track the status of all orders for the past 12 months. When orders were shipped out in parts the same order number was used and this has been a nightmare trying to sort the duplicates and match the dates. I need a formula for F that returns the current status based on the order number and date. I tried a...

CRM 3.0 Data Migration FrameWork Error
Hello, I am runing CRM 3.0 with window 2003 Small Business Server. I install CRM 3.0 data migration framework. However, when I run the framework, system throw a run time error as: Microsoft.Crm.DataMigration.Shell.exe Application has generate an exception that could not be handled. Process id = 0x13cc (5068), Thread id = 0x13d0 (5072). I try to uninstall and reinstall it again, but the same error occur. Does anyone sucessfully run the data migration framework ? -- Thanks, Hi Ken, Just so you know your are not alone, I have the same issue. Did you ever manage to fix it? Regards, N...

Summing visible column values but not hidden column values
I need to write a SUM function that will add up the numbers in several columns of data (for example, A4:S4), but I only want it to add those columns that are visible, not those that are hidden. Is there any way to have Excel add only the numbers in the visible columns, not in any hidden columns? Thanks. Hi have you hiddent he columns manually?. If yes you'll need VBA -- Regards Frank Kabel Frankfurt, Germany "BW" <BW@discussions.microsoft.com> schrieb im Newsbeitrag news:CBAFDFDF-FFFD-4521-97EC-CD3941FD8686@microsoft.com... > I need to write a SUM function that ...

HELP! Chart Zero Values
Hi, I'm new to this forum...I hope someone can help. I have just become trainee Office Trainer, and a delegate has asked me a question that can't immediately help her with. She has a job interview thi afternoon and has been given some Excel tasks to do. She has been asked to create a scatter chart using 2 columns of data i the spreadsheet. How do you get the chart to not display the data i either the x or y axis data is null? I know you can get the chart to not display the values if both x and are zero, but what if just one of the axis is zero? If anyone can help me with this ...

Paste Values Based on Conditional Value
I have a workbook with 2 worksheets. Basically it is a daily sales log. On worksheet 1 the user will input multiple daily sales figures and the date. Worksheet 2 is the historical sales figures. The theoretical usage will be for the end user to go to worksheet 1, enter the date and daily sales figures and click the save data button. I am seeking a formula that will look at the date entry and paste the values from worksheet 1 into the proper line on worksheet 2 based on the date entry from worksheet 1. Example: Column A of worksheet 2 is simply a list of dates. January 13, 201...