Trying to calculate total allocations and remaining items in a table

Hello all,

I'm trying to use the SpreadSheetPlugin to calculate remaining IP addresses in various items in a list. I'm trying to get away from using Excel. I'm having trouble getting the caculations right, because I just can't figure out how to get the right spreadsheetplugin commands to function correctly. If anyone is willing to lend a hand that would be awesome.

As you can see below, I've provided a sample of my lists of subnets and what the caculations need to come out to. The DIV got a little messed up on the end row a bit, all three items should sit side by side on the webpage. I can fix that later.

Currently, it's just a sandbox item for me, but if you want to have a stab at it, please do. Thanks again!



%TABLE{ sort="on" initsort="1" tableborder="0" cellpadding="4" cellspacing="3" cellborder="0" headerbg="#D5CCB1" headercolor="#666" databg="#FAF0D4, #F3DFA8" headerrows="2" footerrows="2" }%
| *192.168.100.0 Example Subnet* |||
| *IP Address* | *Description* | *Machine Number* |
| 192.168.100.0 | -NETWORK ID- | |
| 192.168.100.1 | FIREWALL | |
| 192.168.100.2 | FIREWALL | |
| 192.168.100.3 | -RESERVED- | |
| 192.168.100.4 |||
| Allocated IP addresses: | Answer should be 4 ||
| Remaining IP addresses: | Answer should be 1 ||





%TABLE{ sort="on" initsort="1" tableborder="0" cellpadding="4" cellspacing="3" cellborder="0" headerbg="#D5CCB1" headercolor="#666" databg="#FAF0D4, #F3DFA8" headerrows="2" footerrows="2" }%
| *192.168.200.0 Example Subnet* |||
| *IP Address* | *Description* | *Machine Number* |
| 192.168.200.0 | -NETWORK ID- | |
| 192.168.200.1 | FIREWALL | |
| 192.168.200.2 | FIREWALL | |
| 192.168.200.3 | -RESERVED- | |
| 192.168.200.4 | -RESERVED- | |
| Allocated IP addresses: | Answer should be 5 ||
| Remaining IP addresses: | Answer should be 0 ||





%TABLE{ sort="off" initsort="1" tableborder="0" cellpadding="4" cellspacing="3" cellborder="0" headerbg="#D5CCB1" headercolor="#666" databg="#FAF0D4, #F3DFA8" headerrows="2" footerrows="0" }%
| *Total Addresses Used* | Answer Should be 9 ||
| *Total available addresses* | Answer should be 10 ||
| *Percentage of total address allocation* | Answer should be 90% ||

-- MattWilson - 07 Dec 2012

Working Solution

Matt,

Below is a working solution to your question. A few comments:

I made only one modification to your example: I inserted a dash ("-") in the empty cells because counting truly empty cells (particularly at end of list) is problematic.
I'll leave it to you to read up on SpreadSheetPlugin to understand most of how this was done but I will point out that I defined two re-usable formulas (using the $NOEXEC function) up front so these could be used in any number of Subnet tables. I've added one additional table in this example.
I'll be the first to admit it's not particularly pretty, mostly because of the inability to have line-breaks in SpreadSheetPlugin formulas. I use GluePlugin if I'm working with really complex formulas because it enables me to insert line breaks and indents within the formula. To help make it somewhat more understandable, here's a formatted version (with explanatory comments) of the first CALC macro that does most of the work:

%CALC{"
   $SET(calc_allocated,                                                       // Defines formula as variable to be inserted in allocated cell in each table
     $NOEXEC(                                                                 // Delays execution of formula
       $SET(list,$LIST(R3:C2..R$ROW(-1):C2))                                  // Creates list of items from current table
       $SET(allocated,$LISTSIZE($LISTIF($NOT($EXACT($item,-)),$GET(list))))   // Counts allocated ips
       $SET(not_allocated,$LISTSIZE($LISTIF($EXACT($item,-),$GET(list))))     // Counts un-allocated ips
       $SETM(total_allocated, + $GET(allocated))                              // Adds allocated count to running total
       $GET(allocated)                                                        // Displays allocated count for current table
     )
   )
   $SET(calc_not_allocated,                                                   // Defines formula as variable to be inserted in un-allocated cell in each table
      $NOEXEC(                                                                // Delays execution of formula
        $SETM(total_not_allocated, + $GET(not_allocated))                     // Adds un-allocated count to running total
        $GET(not_allocated)                                                   // Displays allocated count for current table
    )
   )
"}%

Hope this helps!

-- LynnwoodBrown - 15 Jan 2013

192.168.100.0 Example Subnet
IP Address	Description	Machine Number
Allocated IP addresses:	4
Remaining IP addresses:	1
192.168.100.0	-NETWORK ID-
192.168.100.1	FIREWALL
192.168.100.2	FIREWALL
192.168.100.3	-RESERVED-
192.168.100.4	-

192.168.200.0 Example Subnet
IP Address	Description	Machine Number
Allocated IP addresses:	5
Remaining IP addresses:	0
192.168.200.0	-NETWORK ID-
192.168.200.1	FIREWALL
192.168.200.2	FIREWALL
192.168.200.3	-RESERVED-
192.168.200.4	-RESERVED-

192.168.300.0 Example Subnet
IP Address	Description	Machine Number
Allocated IP addresses:	4
Remaining IP addresses:	2
192.168.300.0	-NETWORK ID-
192.168.300.1	FIREWALL
192.168.300.2	FIREWALL
192.168.300.3	-
192.168.300.4	-RESERVED-
192.168.300.6	-