Jump to content

If Function in worksheets


Digbaddy

Recommended Posts

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)

 

Link to comment

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

  • Like 4
Link to comment

@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

 

 

  • Like 4
Link to comment

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.

 

 

 

 

 

  • Like 2
Link to comment
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

  • Like 2
Link to comment

@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. 

 

  • Like 2
Link to comment

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...