AnsweredAssumed Answered

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.

     Thank you for your help!

     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

Outcomes