Need a calculation

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Need a calculation

Post by matthewR »

I have a column "FA Contracts". I need to divide it into segments - 2-49, 50-99, 100+ and National. I want to segment National from the other numbers. I have a column called Dist that identifies "National". How would I remove National from the other numbers and only sum the National records and put everything else in the 2-49, 50-99 etc.

I thought I could IIF([FA Contracts] >1 and <49 and [dist] <>"National" etc but I am not sure how to exclude National from the equations and then count the FA contracts for National. :sad: Hopefully I have explained this

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need a calculation

Post by HansV »

Try this in a query:

Segment: IIf([Dist]="National", "National", IIf([FA Contracts]<50, "2-49", IIf([FA Contracts]<100, "50-99", "100+")))

You can change the name 'Segment' of course.
Best wishes,
Hans

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Re: Need a calculation

Post by matthewR »

Thank you that worked. How does your code exclude "National" from the 2-49 if National has fa contracts less than 50 and same for other segments? It seems like it would conflict. I am not questioning you because it works but I was saying things like - and <> "National" etc.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need a calculation

Post by HansV »

The formula first checks whether Dist equals "National". If so, it returns "National" and completely ignores the value of FA Contracts.
Otherwise, i.e. if Dist does not equal "National", the formula looks at the value of FA Contracts. If this is less than 50, it returns "2-49", else if it is less than 100, it returns "50-99", else "100+".
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Need a calculation

Post by Rudi »

Putting it another way, it's a matter of elimination.
I use this formula structure a lot in Excel..so an Excel example might look like this:

=IF(A1=1,"Apples",IF(A1<3,"Bananas",IF(A1<10,"Strawberries","Ice Cream")))

-- The test A1=1 is evaluated first (Excel disregards everything else). So if A1 did = 1, then Excel gives you Apples.
-- Even though the second test is A1<3, (which 'complies with test 1), Excel has already given Apples and has stopped processing. If A1 was 2, this would not match the first test and Excel would then give you Bananas.
-- Effectively, you could read this part IF(A1<3,"Bananas",IF(A1<10,"Strawberries" like this: Since A1<3 as *already* been processed (in the order of evaluation), A1<10 is actually "Between 3 and 9". Less that three is already evaluated, now what is being processed is values 3 through 9.
-- Finally, if the value in A1 does not meet any of the conditions ie: is 10 or greater, we get Ice Cream.

:whisper: Now that you've grasped this concept...go make yourself a banana split! :grin:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.