 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008Formulas gone bonkers
Help!! I am creating a spreadsheet to handle a relatively complicated cashflow analysis. I was doing fine until I started replacing explicit cell references with named ranges. All of a sudden, I started getting circular reference errors. To cure them, I tried going back, in some cases, to explicit cell references. That worked to a point, but then this started happening: I would create a formula, I could see that the value the formula created in that row was correct, I would copy the formula down, but the values appearing in the subsequent rows were the same value that appeared in the row from which the formula was copied! Why is this happening? And how can I fix the problem? calculation automatic??  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Help!! I am creating a spreadsheet to handle a relatively complicated > cashflow analysis. I was doing fine until I started replacing explicit [quoted text clipped - 10 lines] > how > can I fix the problem? Yes. I checked that, and I tried hitting F9. > calculation automatic?? > [quoted text clipped - 12 lines] > > how > > can I fix the problem? I think I found one solution: Apparently functions like MIN, MAX, and SUM, when faced with a reference to a range, evaluate the entire named range rather than just the value in the cell located in the same row within the named range. To refer to the cell in the same range, I added a "+" before references to named ranges within MIN functions. Not very elegant, but it might do the trick. > Yes. I checked that, and I tried hitting F9. > [quoted text clipped - 14 lines] > > > how > > > can I fix the problem? If you name a range like A1:A10 as MyRange, you're creating an absolute reference - $A1:$A10 If your formula is =SUM(A1:A10) and you change that to be =SUM(MyRange) you are now saying: =SUM($A$1:$A:$10). Bear that in mind. When you copied (dragged?) the formula down, the relative references (A1:A10) changed.. Now when you drag the formula down the absolute references (MyRange - $A$1:$A$10) do NOT change,. As far as problems with implict reference are concerned I would need to see an example. Tyro > Help!! I am creating a spreadsheet to handle a relatively complicated > cashflow analysis. I was doing fine until I started replacing explicit [quoted text clipped - 10 lines] > how > can I fix the problem? Correction: If you name a range like A1:A10 as MyRange, you're creating an absolute reference - $A1:$A10 should read: If you name a range like A1:A10 as MyRange, you're creating an absolute reference - $A$1:$A$10 I check these things 3 times before I post them but never see the error until I have posted. :( Tyro > If you name a range like A1:A10 as MyRange, you're creating an absolute > reference - $A1:$A10 If your formula is =SUM(A1:A10) and you change that [quoted text clipped - 21 lines] >> how >> can I fix the problem? You can change the absolute references in the named range to relative, but to do so, you must be on the first cell that references the range, before dragging the formula to other cells. You have to be very careful doing so. Generally not recommended. The purpose of naming something is to have a static reference to that something. Tyro > Correction: If you name a range like A1:A10 as MyRange, you're creating an > absolute reference - $A1:$A10 should read: If you name a range like [quoted text clipped - 30 lines] >>> how >>> can I fix the problem? Tyro, Perhaps I'm using named ranges incorrectly. I have named say, C16:C99 as Cash_In and D16:D99 as Cash_Out. Each cell in each column represents a value for one of those quantities in a different period. Sometimes, the formulas in columns to the right of C and D which reference a value in either Column C or Column D are relatively simple, and the formula seems to understand that it should look back in the same row. But other formulas are more complex, and as you may have seen, I did discover that if the formula uses a function such as MIN or MAX, the function within the formula apparently doesn't know to confine its evaluation to the value appearing in the referenced column and in the same row. In still other instances, I would have liked to have referred to the value in the previous row (as in, if the cash inflow in the previous period was less than x, do this; otherwise, do that). Anyway, thank you for your help. Capt. Bangs > You can change the absolute references in the named range to relative, but > to do so, you must be on the first cell that references the range, before [quoted text clipped - 38 lines] > >>> how > >>> can I fix the problem? The functions MAX and MIN are designed to work with more than one cell. Why are you using them to refer to one cell? "I did discover that if the formula uses a function such as MIN or MAX, the function within the formula apparently doesn't know to confine its evaluation to the value appearing in the referenced column and in the same row." That is one cell. If you name a single cell, you can refer to that and you'll be referring to only one cell, but absolutely. I guess I don't understand what you're doing with MIN and MAX which should refer to more than one cell. You imply that you're using these functions to refer to one cell. Why? Tyro > Tyro, > [quoted text clipped - 78 lines] >> >>> how >> >>> can I fix the problem? I need the MIN of several values, one of which is the value in a particular cell within a column which is a named range. The other values that are evaluated by the MIN (or MAX) function appear elsewhere in the spreadsheet. > The functions MAX and MIN are designed to work with more than one cell. Why > are you using them to refer to one cell? "I did discover that if the formula [quoted text clipped - 90 lines] > >> >>> how > >> >>> can I fix the problem? You can create a relative reference with a named range. For example. I enter the values 1, 2 and 3 in cells A1, A2 and A3. I select cell B2. Then I define a range, cell A1, with the name MyCell and change the absolute reference $A$1 that Excel creates to the relative reference A1 which is now relative to cell B2. In cell B2 I enter the formula =MyCell + 1 which shows the value 2. Then I drag B2 down to B3 and B4. The values 2, 3, 4 appear in those cells. Try this. It is of paramount importance that you select the cell, in this case B2, before creating the named range and then changing the addresses of the named range from absolute to relative for this method to work. When you change the addresses of the named range to relative, they are relative to the selected cell, in this case, B2. Tyro > Tyro, > [quoted text clipped - 78 lines] >> >>> how >> >>> can I fix the problem? I appreciate all your efforts, but not sure how this suggestion helps. Each column in the spreadsheet is basically a time-series. Each row represents the values that various variables take on at a particular point in the time series, and they are often interdependent which is why, in column W I may need to look back to the value in column N. Defining individual cells as named ranges would, in most cases, not be helpful, because the model is, at this point, roughly 90 columns by 85 rows. That's a lot of cells! > You can create a relative reference with a named range. For example. I > enter the values 1, 2 and 3 in cells A1, A2 and A3. I select cell B2. Then I [quoted text clipped - 92 lines] > >> >>> how > >> >>> can I fix the problem? Perhaps you could upload your workbook to http://savefile.com. Show me the original formula and the replacement formula with the named range. Tell me where these are located on the sheet. Tyro >I appreciate all your efforts, but not sure how this suggestion helps. >Each [quoted text clipped - 128 lines] >> >> >>> how >> >> >>> can I fix the problem? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.