This question about Using an extension: Answered

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 AddressSorted ascending 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 AddressSorted ascending 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 AddressSorted ascending 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 -  

Total Addresses Used 13
Total available addresses 3
Percentage of total address allocation 81%

 

QuestionForm edit

Subject Using an extension
Extension SpreadSheetPlugin
Version Foswiki 1.1.5
Status Answered
Related Topics
Topic revision: r4 - 15 Jan 2013, LynnwoodBrown - This page was cached on 17 Jan 2018 - 19:00.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License