Digbaddy Posted March 18, 2020 Share Posted March 18, 2020 Hi Everyone, I am trying to build a worksheet to calculate landscape area (Australian CDC regulations). For landscaping their is a requirement for water permeable area or 'Landscaped Area' It is a percentage of the total site area determined by lot size: 200- 300m2 10% Landscape Area 300- 450m2 15% Landscape Area 450- 600m2 20% Landscape Area 600- 900m2 30% Landscape Area 900- 500m2 40% Landscape Area 1500m2 + 45% I have created a database line to get the site area from a class. The formula I am having trouble with needs to determine an 'IF' function, something ; like: =IF(C7=>600) and (C7<90) (C7*0.3) Quote Link to comment
michaelk Posted March 18, 2020 Share Posted March 18, 2020 You're close. The formula is like this: IF [logical test] THEN [Result 1] ELSE [Result 2] Which is written in the database header as =IF ([logical test] , [Result 1] , [Result 2]) with parenthesis around the whole if statement. THEN and ELSE get replaced by commas. It often helps to add parenthesis around compound statements. =IF(([logic test 1] AND [logic test2]),[Result 1],[Result 2]) But the results can also be another nested IF,THEN,ELSE Statement. So your whole thing would look like this: =IF((AREA>200) AND (AREA<=300), AREA*0.1, (IF((AREA>300) AND (AREA<=450), AREA*0.15, (IF((AREA>450) AND (AREA<=600), AREA*0.2, (IF((AREA>600) AND (AREA<=900), AREA*0.3, (IF((AREA>900) AND (AREA<=1500), AREA*0.4, (IF((AREA>1500), AREA*0.45, '-'))))))))))) All the parenthesis get confusing. So I usually do them one at a time and then combine them. I'll attach the example of how I got the above formula. This would probably be easier to do in a worksheet script. But that's a whole different kettle of fish. Area math.vwx 4 Quote Link to comment
MullinRJ Posted March 19, 2020 Share Posted March 19, 2020 @Digbaddy , After you get lost in Michael's anatomically perfect answer, there are some ways to reduct the length of the overall expression. Since your expression evaluates a series of stair step conditions, you don't need to test both sides of each step every time. You only need to evaluate the AREA as it crosses over each threshold value, as such: =if(AREA>=1500, AREA*0.45, if(AREA>=900, AREA*0.4, if(AREA>=600, AREA*0.3, if(AREA>=450, AREA*0.2, if(AREA>=300, AREA*0.15, if(AREA>=200, AREA*0.1, 0)))))) Additionally, you can factor out the AREA* term from each nesting of the compound IF and only return the scale factor for each AREA range, which you then multiply with the AREA outside the IF construct, like this: =AREA * if(AREA>=1500, 0.45, if(AREA>=900, 0.4, if(AREA>=600, 0.3, if(AREA>=450, 0.2, if(AREA>=300, 0.15, if(AREA>=200, 0.1, 0)))))) But, if you are really picky (like someone I won't mention) about minute details, you can replace each >= operator with a < operator by reversing the logic, like this: =AREA * if(AREA<200, 0, if(AREA<300, 0.1, if(AREA<450, 0.15, if(AREA<600, 0.2, if(AREA<900, 0.3, if(AREA<1500, 0.4, 0.45)))))) The character count for each IF expression is as follows: 254 (Michael's answer), 155 (my 1st answer), 132 (my 2nd answer), 126 (my last answer). They all evaluate essentially the same, but the shorter expressions are easier to read and modify. The only difference between Michael's answer and mine, is that I return "0" for areas < 200, and Michael returns a "-" character for that condition. Take your pick how you want to handle that condition. HTH, Raymond 4 Quote Link to comment
michaelk Posted March 19, 2020 Share Posted March 19, 2020 Raymond! Love that third method. Of course: if the value is less than 200 it never does the other logical tests! And pulling the multiplication out of the IF/THEN statements makes a lot of sense. Nice work! I always enjoy glimpses of the inside of your brain. I find stacks of right parentheses very hard to read and hard to match up to their partners. So I make an algorithm out of creating the nested IF/THENS. I write one for each case with a dummy string in the ELSE result. Then a trained monkey can copy and paste them all together w/o having to count or even think about parentheses. 2 Quote Link to comment
MullinRJ Posted March 19, 2020 Share Posted March 19, 2020 51 minutes ago, michaelk said: I find stacks of right parentheses very hard to read and hard to match up to their partners. So I make an algorithm out of creating the nested IF/THENS ... Hi Michael, Any way that works is a good way. But if you have TextWrangler, or BBEdit, you can paste the formula in a file and when you double click on a parenthesis (left or right) the program will highlight all text between it and its matching parenthesis. Extra and missing parentheses are very easy to spot this way. Very handy tool for just such occasions. Raymond 2 Quote Link to comment
Digbaddy Posted March 20, 2020 Author Share Posted March 20, 2020 @michaelk I think you are generous to say that I was close! @MullinRJ I have copied your third solution (like a trained monkey, haha) with great success! I appreciate the explanations and various examples, these help me (slowly) understand the greater principles and commands. Thanks both of you for taking the time to post your replies, I am gratefully standing on your shoulders! I have another question, but will digest and play around for a bit before coming back, I don't fully understand all the commands and process yet and need a bit of time to play around. 2 Quote Link to comment
Popular Post Benson Shaw Posted March 20, 2020 Popular Post Share Posted March 20, 2020 (edited) All I can say is WOW! What a great conversation! Great question and great answers! Forum at its best! -B Edited March 20, 2020 by Benson Shaw arrrg 8 Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.