+ Reply to Thread
Results 1 to 2 of 2

Iterative Formula

  1. #1
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Question Iterative Formula

    Hi I am wondering if the following is possible using Excel Formula.

    I have greatly simplified this, so any suggestion please help me know how to iterate the problem, not just a 'mathematical' solution.

    Each Shop can provide food for up to 5 Properties
    A Shop IS a Property.

    Cell A1 holds the number of houses.
    Cell A2 Holds the number of properties (houses + shops)
    Cell A3 has to have the result for the number of shops needed to provide food for all properties. Currently containing:
    Please Login or Register  to view this content.
    At low values it is not an issue as a single iteration will solve it;

    3 Houses = 3 Properties
    3 Properties require 1 Shop
    This means there are now 4 Properties (3 Houses + 1 Shop)
    4 Properties need 1 shop
    COMPLETED

    7 Houses = 7 Properties
    7 Properties require 2 Shops
    This means there are now 9 properties (7 Houses + 2 Shops)
    9 Properties need 2 shops.
    COMPLETED

    The problem I get is where it does not solve at this point, such as 100 Houses;
    100 Houses = 100 Properties
    100 Properties require 20 Shops
    This means there are now 120 Properties (100 Houses + 20 Shops)
    120 Properties require 24 shops
    This means there are now 124 Properties (100 Houses + 24 Shops)
    124 Properties require 25 shops
    This means there are now 125 properties (100 Houses + 25 Shops)
    125 properties require 25 Shops.
    COMPLETED

    I am just not sure how to feed the total number of properties back into the formula.
    What I 'think' I need is something like this:

    In Cell A2 (number of Properties):
    Please Login or Register  to view this content.
    In Cell A3 (number of shops):
    Please Login or Register  to view this content.
    Of course this then creates a circular reference that Excel moans about instead of just trying to solve, so what is the correct way to do this?

    Thanks
    Last edited by Phil_V; 05-14-2009 at 08:29 AM.

  2. #2
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: Iterative Formula

    No worries, I just found the 'Iterations' checkbox under Options. I guess that is the way to do it?
    Presumably though that setting is at an application level, as in if someone opened my spreadsheet on a different PC they would again be faced with the circular reference 'error' messsage?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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