# Ideas on how to simplify calculation

Question asked by JerryAPowers on Jun 5, 2013
Latest reply on Jun 7, 2013 by JerryAPowers

### Title

Ideas on how to simplify calculation

### Post

I have a very long calculation in my database that appears that it could be simplified with some sort of loop counter and wlldcard parameter with the field names. However, I cannot seem to figure out the coding to make this work. If you can think of a better way to create this calculation it would be greatly appreciated. Additionally, I am thinking that I should create a custom function for this calculation since there are MANY fields that also utilize this calculation. I have hit the character limits for calculations when I duplicate this long calculation in the formula and add additional calculation for each particular field.

Jerry

Following is my current calculation:

Round(Average (
If ( Comps in ARV = "Yes"; Average (
If (Comp 1 Select = "Yes";
((Comp 1 Purchase Price + If(Number Bedrooms<1 or Comp 1 Beds<1;0;((Number Bedrooms-Comp 1 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 1 Baths<1;0;((Number Baths-Comp 1 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 1 Lot Area<1;0;((Lot Area-Comp 1 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 1 Square Footage));
If (Comp 2 Select = "Yes";
((Comp 2 Purchase Price + If(Number Bedrooms<1 or Comp 2 Beds<1;0;((Number Bedrooms-Comp 2 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 2 Baths<1;0;((Number Baths-Comp 2 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 2 Lot Area<1;0;((Lot Area-Comp 2 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 2 Square Footage));
If (Comp 3 Select = "Yes";
((Comp 3 Purchase Price + If(Number Bedrooms<1 or Comp 3 Beds<1;0;((Number Bedrooms-Comp 3 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 3 Baths<1;0;((Number Baths-Comp 3 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 3 Lot Area<1;0;((Lot Area-Comp 3 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 3 Square Footage));
If (Comp 4 Select = "Yes";
((Comp 4 Purchase Price + If(Number Bedrooms<1 or Comp 4 Beds<1;0;((Number Bedrooms-Comp 4 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 4 Baths<1;0;((Number Baths-Comp 4 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 4 Lot Area<1;0;((Lot Area-Comp 4 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 4 Square Footage));
If (Comp 5 Select = "Yes";
((Comp 5 Purchase Price + If(Number Bedrooms<1 or Comp 5 Beds<1;0;((Number Bedrooms-Comp 5 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 5 Baths<1;0;((Number Baths-Comp 5 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 5 Lot Area<1;0;((Lot Area-Comp 5 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 5 Square Footage));
If (Comp 6 Select = "Yes";
((Comp 6 Purchase Price + If(Number Bedrooms<1 or Comp 6 Beds<1;0;((Number Bedrooms-Comp 6 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 6 Baths<1;0;((Number Baths-Comp 6 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 6 Lot Area<1;0;((Lot Area-Comp 6 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 6 Square Footage));
If (Comp 7 Select = "Yes";
((Comp 7 Purchase Price + If(Number Bedrooms<1 or Comp 7 Beds<1;0;((Number Bedrooms-Comp 7 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 7 Baths<1;0;((Number Baths-Comp 7 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 7 Lot Area<1;0;((Lot Area-Comp 7 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 7 Square Footage));
If (Comp 8 Select = "Yes";
((Comp 8 Purchase Price + If(Number Bedrooms<1 or Comp 8 Beds<1;0;((Number Bedrooms-Comp 8 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 8 Baths<1;0;((Number Baths-Comp 8 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 8 Lot Area<1;0;((Lot Area-Comp 8 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 8 Square Footage));
If (Comp 9 Select = "Yes";
((Comp 9 Purchase Price + If(Number Bedrooms<1 or Comp 9 Beds<1;0;((Number Bedrooms-Comp 9 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 9 Baths<1;0;((Number Baths-Comp 9 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 9 Lot Area<1;0;((Lot Area-Comp 9 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 9 Square Footage));
If (Comp 10 Select = "Yes";
((Comp 10 Purchase Price + If(Number Bedrooms<1 or Comp 10 Beds<1;0;((Number Bedrooms-Comp 10 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 10 Baths<1;0;((Number Baths-Comp 10 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 10 Lot Area<1;0;((Lot Area-Comp 10 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 10 Square Footage));
If (Comp 11 Select = "Yes";
((Comp 11 Purchase Price + If(Number Bedrooms<1 or Comp 11 Beds<1;0;((Number Bedrooms-Comp 11 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 11 Baths<1;0;((Number Baths-Comp 11 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 11 Lot Area<1;0;((Lot Area-Comp 11 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 11 Square Footage));
If (Comp 12 Select = "Yes";
((Comp 12 Purchase Price + If(Number Bedrooms<1 or Comp 12 Beds<1;0;((Number Bedrooms-Comp 12 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 12 Baths<1;0;((Number Baths-Comp 12 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 12 Lot Area<1;0;((Lot Area-Comp 12 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 12 Square Footage));
If (Comp 13 Select = "Yes";
((Comp 13 Purchase Price + If(Number Bedrooms<1 or Comp 13 Beds<1;0;((Number Bedrooms-Comp 13 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 13 Baths<1;0;((Number Baths-Comp 13 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 13 Lot Area<1;0;((Lot Area-Comp 13 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 13 Square Footage));
If (Comp 14 Select = "Yes";
((Comp 14 Purchase Price + If(Number Bedrooms<1 or Comp 14 Beds<1;0;((Number Bedrooms-Comp 14 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 14 Baths<1;0;((Number Baths-Comp 14 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 14 Lot Area<1;0;((Lot Area-Comp 14 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 14 Square Footage));
If (Comp 15 Select = "Yes";
((Comp 15 Purchase Price + If(Number Bedrooms<1 or Comp 15 Beds<1;0;((Number Bedrooms-Comp 15 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 15 Baths<1;0;((Number Baths-Comp 15 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 15 Lot Area<1;0;((Lot Area-Comp 15 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 15 Square Footage));
If (Comp 16 Select = "Yes";
((Comp 16 Purchase Price + If(Number Bedrooms<1 or Comp 16 Beds<1;0;((Number Bedrooms-Comp 16 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 16 Baths<1;0;((Number Baths-Comp 16 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 16 Lot Area<1;0;((Lot Area-Comp 16 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 16 Square Footage));
If (Comp 17 Select = "Yes";
((Comp 17 Purchase Price + If(Number Bedrooms<1 or Comp 17 Beds<1;0;((Number Bedrooms-Comp 17 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 17 Baths<1;0;((Number Baths-Comp 17 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 17 Lot Area<1;0;((Lot Area-Comp 17 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 17 Square Footage));
If (Comp 18 Select = "Yes";
((Comp 18 Purchase Price + If(Number Bedrooms<1 or Comp 18 Beds<1;0;((Number Bedrooms-Comp 18 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 18 Baths<1;0;((Number Baths-Comp 18 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 18 Lot Area<1;0;((Lot Area-Comp 18 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 18 Square Footage));
If (Comp 19 Select = "Yes";
((Comp 19 Purchase Price + If(Number Bedrooms<1 or Comp 19 Beds<1;0;((Number Bedrooms-Comp 19 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 19 Baths<1;0;((Number Baths-Comp 19 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 19 Lot Area<1;0;((Lot Area-Comp 19 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 19 Square Footage));
If (Comp 20 Select = "Yes";
((Comp 20 Purchase Price + If(Number Bedrooms<1 or Comp 20 Beds<1;0;((Number Bedrooms-Comp 20 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 20 Baths<1;0;((Number Baths-Comp 20 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 20 Lot Area<1;0;((Lot Area-Comp 20 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 20 Square Footage));
If (Comp 21 Select = "Yes";
((Comp 21 Purchase Price + If(Number Bedrooms<1 or Comp 21 Beds<1;0;((Number Bedrooms-Comp 21 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 21 Baths<1;0;((Number Baths-Comp 21 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 21 Lot Area<1;0;((Lot Area-Comp 21 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 21 Square Footage));
If (Comp 22 Select = "Yes";
((Comp 22 Purchase Price + If(Number Bedrooms<1 or Comp 22 Beds<1;0;((Number Bedrooms-Comp 22 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 22 Baths<1;0;((Number Baths-Comp 22 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 22 Lot Area<1;0;((Lot Area-Comp 22 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 22 Square Footage));
If (Comp 23 Select = "Yes";
((Comp 23 Purchase Price + If(Number Bedrooms<1 or Comp 23 Beds<1;0;((Number Bedrooms-Comp 23 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 23 Baths<1;0;((Number Baths-Comp 23 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 23 Lot Area<1;0;((Lot Area-Comp 23 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 23 Square Footage));
If (Comp 24 Select = "Yes";
((Comp 24 Purchase Price + If(Number Bedrooms<1 or Comp 24 Beds<1;0;((Number Bedrooms-Comp 24 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 24 Baths<1;0;((Number Baths-Comp 24 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 24 Lot Area<1;0;((Lot Area-Comp 24 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 24 Square Footage));
If (Comp 25 Select = "Yes";
((Comp 25 Purchase Price + If(Number Bedrooms<1 or Comp 25 Beds<1;0;((Number Bedrooms-Comp 25 Beds)*Defaults::Bedroom Value))+If(Number Baths<1 or Comp 25 Baths<1;0;((Number Baths-Comp 25 Baths)*Defaults::Bedroom Value))+Round(If(Lot Area<1 or Comp 25 Lot Area<1;0;((Lot Area-Comp 25 Lot Area)*(Lookup ( Tax Districts::District Land Cost per acre ; Defaults::Land Cost )/43560)));-2))/Comp 25 Square Footage)))
* Square Footage);
If ( RealQuest in ARV = "Yes";Realquest Valuation);
If ( HomeGain in ARV = "Yes";Homegain Valuation);
If ( Zillow in ARV = "Yes";Zillow Valuation))/Defaults::Sales Price Rounding;0)*Defaults::Sales Price Rounding