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:
At low values it is not an issue as a single iteration will solve it;![]()
=ROUNDUP(A2/5,0)
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):
In Cell A3 (number of shops):![]()
=Num_Houses + Num_Shops
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?![]()
=Num_Properties / 5
Thanks![]()
Bookmarks