APGroup command

Syntax

=APGROUP("SUM", command)

e.g. =APGROUP("SUM", "[a,*TOTREV]") sums named range TOTREV for all the companies in the group.

Parameters

• 1 Companies The first parameter specifies the companies for which data is required:
  • For a single company, "0", "1", "2" etc. "0" means the group company; "1" means the parent; "2" means the first subsidiary; "3" means the second subsidiary and so on. The count includes not just subsidiaries that are in the group currently, but also those that were in the group in the previous 5 years so that comparatives for a company that is no longer in the group can be shown.
  • For multiple companies, "SUM" or "SUMEX". "SUM" means all the companies in the group; "SUMEX" means all the companies in the group excluding the group company itself.
• 2 Command The second parameter is any meaningful AP() command. The command could be "[a,*TOTREV]" for a named range, "#on99956" for a Statutory Database reference or "RangeCode,[*TOTREV],,a,-1,-1,a" for a list of account balances.

Examples

=APGROUP("3", "#cd2") gives the name (#cd2) of the second subsidiary ("3"), provided the second subsidiary is part of the group in the current period. Note that =APGROUP("SUM", "#cd2") would not be meaningful as the system cannot summarise a text field.

=APGROUP("SUM", "[a,*TOTREV]") gives the total of named range TOTREV for the current year for all the companies in the group.

=APGROUP("SUM","b,#on99956") gives the total of Statutory Database reference #on99956 for last year "b" for all companies in the group.

=APGROUP("SUM","(a,3990)") gives the total balance of nominal 3990 across the group for the current period. The parentheses are needed to clarify that a,3990 is a nominal formula and not just plain text.

=APGROUP("SUM","RangeCode,3990,3995,a,-1,0,ab") gives a list of the account balances across the group for nominals in the range 3990 to 3995.

=APGROUP("1","[a,*PROFIT]-#cf999060") gives the balance on name range PROFIT less the balance on Statutory Database reference #cd999060 on the parent company.

=APGROUP("SUM","RangeCode,[*EXCEP],,a,-1,0,ab") gives a list of the account balances in name range EXCEP summarised by account, i.e. 1 line for each nominal in the range.

=APGROUP("SUM","RangeCode,[*EXCEP],,a,-1,0,ab,Cred") gives a list of the credit account balances in name range EXCEP summarised by account.  

Note

• The last example may not be what you want. The command gets all the credit balances for companies in the group and then summarises them. If one subsidiary has a credit on a particular nominal and another has a debit then the same nominal will show in both  =APGROUP("SUM","RangeCode,[*EXCEP],,a,-1,0,ab,Deb") and =APGROUP("SUM","RangeCode,[*EXCEP],,a,-1,0,ab,Cred"). In practice, you are more likely to want to see the account only once depending on whether it is a debit or credit overall.
• This illustrates how APGROUP works. It runs the command for each company specified by the first parameter, then, when it's got all the results, it summaries them by account code. In this case you would probably want it to get all the account balances both credit and debit, then to summarise them, and then to only print the credit balances. But the APGROUP command cannot do that. The command gives a lot of flexibility but is not tailored for each special case. 
• When period codes such as "a" for the current year and "b" for last year are used, they refer to the periods of the group company. Of course, usually the periods of the subsidiaries are the same. But it is possible for differences to arise if a subsidiary joined or left the group. So in a formula like =APGROUP("2", "[c,*TOTREV]"), the system finds period "c" for the group company and then gets the matching period for the subsidiary, providing the subsidiary was in the group in that period.