+ Reply to Thread
Results 1 to 6 of 6

How to display value of circular reference formula

  1. #1
    Registered User
    Join Date
    02-05-2016
    Location
    Kigali
    MS-Off Ver
    excel 2007
    Posts
    21

    How to display value of circular reference formula

    Hi guys,

    I have an array formula in column B cells that removes duplicates from column A. Unfortunately, column B displays 0 instead of the real values because it's a circular reference formula.
    I've tried changing the number of iterations and maximum change, this time I get my values but in an disorderly way compared to the values in column A.
    I would like to remove duplicates from column A and keep them from smallest to biggest. What values should I use for number of iterations and maximum change?
    Here attached is the workbook sample.

    Thanks for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,810

    Re: How to display value of circular reference formula

    Your sample does not include any formulas, so it is difficult to determine what circular logic you are using for this. Though, I'm not sure that I see that circular logic is really needed. If the circular logic works, then I don't see that "number of iterations" or "max change" will really impact the result of this calculation.

    Is it necessary to use a formula for this? My inclination for this sort of thing is to use the Data-> Remove duplicates command, or Data->Advanced Filter command, or to use a pivot table (Insert-> Pivot table) to get a list of unique entries. I find these are easier to use on the rare occasion when I need to remove duplicates. Is there a reason why these kinds of built in solutions are not desirable?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-05-2016
    Location
    Kigali
    MS-Off Ver
    excel 2007
    Posts
    21

    Re: How to display value of circular reference formula

    Sorry for taking long to reply Mr Shorty, I don't know why I didn't get a notification email for your reply.

    Sorry also I didn't realize the formula was badly copied, let me upload again the workbook.

    Actually that workbook is only an extract of a bigger workbook which contains a lot of data that will be updated periodically, that's why I need a formula to automatically remove those duplicates without having to always remove duplicates manually using commands you mentioned above. I didn't want to upload the original workbook as I'm still working on it and haven't given it a clean look yet, it really looks ugly for now. But if you're interested in having a look at it to understand why I desperately need to use such a formula, I can upload it for you.
    Attached Files Attached Files

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: How to display value of circular reference formula

    Try

    B2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag down
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  5. #5
    Registered User
    Join Date
    02-05-2016
    Location
    Kigali
    MS-Off Ver
    excel 2007
    Posts
    21

    Re: How to display value of circular reference formula

    Thanks a lot Mr. Ankur. It works perfectly.

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: How to display value of circular reference formula

    Thanks for feedback and Rep. Please mark thread as solved if you have got you solution ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula contains a circular reference
    By NVRensburg in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-09-2014, 05:45 AM
  2. Replies: 2
    Last Post: 02-23-2014, 06:06 PM
  3. Circular Reference when formulas reference end of row formula!
    By Spellbound in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-29-2009, 08:26 AM
  4. If formula and circular reference
    By jcavigli in forum Excel General
    Replies: 1
    Last Post: 10-11-2008, 09:50 PM
  5. Replies: 1
    Last Post: 08-21-2007, 07:22 PM
  6. Circular Reference Formula Help
    By acebrown in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2006, 08:25 PM
  7. circular reference formula
    By Abhi in forum Excel General
    Replies: 4
    Last Post: 03-12-2006, 10:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1