mercredi 10 juillet 2019

Google sheet drop down and calculations

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