Structured Spreadsheets

These are essentially Microsoft Excel® files and contain the same data as the CSV files with additional structure added by means of the MADMACS Excel macros.  If you are also using Microsoft® Internet Explorer 4.01 or later the file may open directly in the browser window and you can work directly with the data.  For more elaborate calculations, you may find it more convenient to download a copy of the file and then open it in Excel to work on it.  In the following section we give some brief indications of some uses of the additional structure in these files. Some basic familiarity with Excel can take you much further.

We have verified that these files also work with other spreadsheets: all features described below seem to work properly in the Gnumeric Version 1.2.6 and Open Office 1.1 spreadsheet applications.   Some features do not work in the Koffice spreadsheet.

Hints on using the structured Spreadsheets

When you click on a link to one of these files, you will normally be asked whether you want to Open or Save the file. 

If you select Open, the file may open in your Web browser and you will not have access to all the functionality of Excel.  If you save a copy, you will be able to follow the following instructions.

Convenient scrolling of the spreadsheets

The column area of some tables is very large and you will only be able to view a small section on your screen.  Unless you are close to the left-hand edge and top of this area you will not be able to see the element names and column labels that identify the data you are looking at.  To remedy this, a text box briefly indicates what to do.  In more detail, you should

1. Scroll vertically until the red cell is in a convenient, but still visible position and you can see the first column of element names and the row of labels (shaded yellow).   Something like this:

   

2. Select the red cell by clicking in it.
3. From Excel's Window menu, select the Freeze Panes command.
4. You will then be able to scroll verticall or to the right to make other regions of the column table visible but retaining the element names and the column labels, e.g.
5. You will not be able to scroll up in the region above the column label row.  To do this you will have to use Window/Unfreeze Panes.

What do the data mean?

The top left section of the spreadsheet contains a number of cell pairs looking like this

These are quantities characterising either the source of the data (e.g., DATE is the date it was computed), global  parameters of the optics (e.g., BETYMAX is the maximum value of  occurring in the optics), or a parameter of the beam (e.g., CHARGE is the charge of the particles in units of the electronic charge, +1 corresponding to positive particles).

Lower down, the main body of the spreadsheet contains a block of columns, looking like this:

Each row corresponds to the end of an element in the LHC structure.  The element can be identified by the NAME column and its basic MAD type by the KEYWORD column.  Further columns describe physical parameters of the element (e.g., K1 is the integrated quadrupole strength) or values of the optical functions ("Twiss functions") at the end of the element (e.g., BETY is the local value of ).

Data filtering

When you open a structured spreadsheet, you will see that a data filter has already been applied to the block of columns.  In the following screen-shot, it is being used to select only the elements with the MAD keyword "QUADRUPOLE" 

Having selected the quadrupoles, one could, go on to find only those where the horizontal beta function exceeds 200 m.  For this, you can define a custom filter on the BETX column: 

Named ranges

The descriptors and the columns of element data are named Excel ranges. The list of named ranges in the file can be dropped down from the box at the top left: 

Or the names can be typed or pasted into formulas (via Insert/Name/Paste ... ) to do many kinds of calculations.  

which evaluates to

It is worth learning about array formulas which can be used to generate new columns of data.  As an example, you can generate the quadrupole gradients from the K1L values by multiplying by the symbol Brho and the length of each magnet, L.

Type a new column header to the right of the main block:

Go to the bottom right of the new column (from the previous position type LeftArrow, Ctrl-DownArrow, RightArrow) and select the empty cells of the new column (type Ctrl-Shift-UpArrow, DownArrow). 

Type the formula

followed by Ctrl-Shift-Enter to enter it as an array formula.  This gives the quadrupole gradients in T/m

To avoid the error messages on division by zero, use a more elaborate formula such as 

=IF(L>0,Brho*K1L/L,0).

Array formulas will not work if a data filter is applied. One can also use simple formulas and fill down the filtered column. 

Hiding irrelevant columns produces a view of the quadrupoles and their strengths in T/m.

With more elaborate formulas and some knowledge of Excel, you can easily compute all element strengths, beam sizes, driving terms for non-linear resonances, plot the information in many ways, etc., 

This Web, the MAD input files and all the data  were created automatically by the Mathematica notebook LHCOpticsWebPages.nb, based on the Madtomma packages.
This page created at <*textDate[]*>
The template for this page was last modified Sunday, 28 May 2006 17:25:34