I'm trying to figure out how to dynamically have multiple drop-down values calculated as per what's in the cells in Google Sheets. Not sure if I'm using the correct text or how to describe what I'm looking for.
I'm working for a building company I want to find out how to calculate which house will fit on a certain sized amount of land, based off the R code that the suburb has and the frontage of the property.
Example data:
Size of a block is from 80m2 and up to 5000m2
R-Codes are: 2,2.5,5,10,12.5,15,17.5,20,25,30,35,40,50,60,80
Frontage (m) can be: 7.5,8.5,10,12,12.5,14,15,15.65,17
R Codes determine the size of the house that can be built on the land provided.
Example:
R Code: Min size house:
2,2.5 20% of land size
5 30% of land size
10 40% of land size
12.5 45% of land size
15,17.5,20,25 50% of land size
30,35,40 55% of land size
50,60 60% of land size
80 70% of land size
So if a client has a 350m2 piece of land and the code for that area is R20 then the size of the house that can be built on that land is 175m2.
I want the drop down to have the option for each field
Block size | R Code | Frontage | and calculate the size of the house size
With this information, we could reference a house a model that can fit on the block and be shown to a client.
Example:
Name House size (m2) Frontage (m)
Davenport 176.8 8.5
I'm playing around with these formulas:
=if(A2<5,A2*2,A2*3)
=if(A4>average(B2:B15),”Cell A4 is bigger than the average”,””)
Trying to figure out which formulae can be introduced into the calculation and produce the correct answer with all the conditions.
I don't have any coding experience
Aucun commentaire:
Enregistrer un commentaire