divide table cells by a certain amount

Question

I made a mistake in the values that i put in the table. I need to divide all the values by 105. Is there a method where i can do it without having to replace this with a completely new table ?

 % Table generated by Excel2LaTeX from sheet 'Sheet1'
    \begin{table}[htbp]
      \centering
      \caption{Add caption}
        \begin{tabular}{rrrr}
        \toprule
              & A     & B     & C \\
        \midrule
        1     & 122   & 133   & 156 \\
        2     & 112   & 135   & 155 \\
        3     & 150   & 139   & 158 \\
        4     & 145   & 135   & 159 \\
        5     & 150   & 130   & 200 \\
        \bottomrule
        \end{tabular}%
      \label{tab:addlabel}%
    \end{table}%

With the dividers or separators inherent to tabular, you can copy-and-paste the result into a spreadsheet package (like Excel), perform the operation and paste it back into TeX... Is this a possibility? — Werner, Jul 31 '12 at 05:30
Related Question: spreadtab insert multiple values to a cell. — Peter Grill, Jul 31 '12 at 08:30

score 3 · Accepted Answer · edited Jun 03 '17 at 13:21

You can use the spreadtab package; a simple example:

\documentclass{article}
\usepackage{numprint}
\usepackage{booktabs}
\usepackage{spreadtab}

\newcommand\Factor{105}

\begin{document}

\renewcommand\STprintnum[1]{\numprint{#1}}
\nprounddigits{3}
\begin{spreadtab}{{tabular}{rrrr}}
\toprule
               & @A              & @B            & @C         \\
\midrule
        @1     & 122/\Factor   & 133/\Factor   & 156/\Factor \\
        @2     & 112/\Factor   & 135/\Factor   & 155/\Factor \\
        @3     & 150/\Factor   & 139/\Factor   & 158/\Factor \\
        @4     & 145/\Factor   & 135/\Factor   & 159/\Factor \\
        @5     & 150/\Factor   & 130/\Factor   & 200/\Factor \\
\bottomrule
\end{spreadtab}

\end{document}

enter image description here

And using \STcopy the job is simpler; we built with one instruction a new table with the results of the divisions and hide the original values:

\documentclass{article}
\usepackage{numprint}
\usepackage{booktabs}
\usepackage{spreadtab}

\newcommand\Factor{105}

\begin{document}

\renewcommand\STprintnum[1]{\numprint{#1}}
\nprounddigits{3}
\begin{spreadtab}{{tabular}{rrrrrrr}}
\toprule
 & \SThidecol @A & \SThidecol @B & \SThidecol @C & @A & @B & @C \\
\midrule
@1 & 122 & 133 & 156 & \STcopy{>3,v4}{b2/\Factor} &  & \\
@2 & 112 & 135 & 155 & & & \\
@3 & 150 & 139 & 158 & & & \\
@4 & 145 & 135 & 159 & & & \\
@5 & 150 & 130 & 200 & & & \\
\bottomrule
\end{spreadtab}

\end{document}

enter image description here

isnt it possible to define a global variable on top so that in case i need to change the amount i divide, i only have to change one global variable ? — user581544, Jul 31 '12 at 05:04
@user581544 I've updated my answer with an improved version of the code. — Gonzalo Medina, Jul 31 '12 at 06:11

score 2 · Answer 2 · edited Jun 10 '20 at 12:32

You can also do this without changing the data entries by using the collcell package.

Below I have defined the R column type which divides the number by the value specified in \Factor, and prints the number using \pgfmathprintnumber which allows for formatting the number. The options [precision=3] have been applied to only print three decimal digits. The table on the left is using the r column type, and the one on the right is using the R column type.

enter image description here

Notes:

Below I have used toggles from the etoolbox, but the same functionality can be achieved with other approaches as in Conditional typesetting / build.

Manually Specify end of Header:

This version require one to add \StartingHeader at the start of the header (which disables the application of the factor to the data entry), and \DoneWithHeader at the end of the header so that subsequent data values are divided by \Factor. Automated version is below.

Code:

\documentclass{article}
\usepackage{booktabs}
\usepackage{collcell}
\usepackage{pgf}
\usepackage{etoolbox}
\newcommand{\Factor}{105}%
\newcommand{\FactorCell}[1]{%
    \iftoggle{DoneWithHeader}{%
        \pgfmathsetmacro{\ComputedValue}{#1/\Factor}%
        \pgfmathprintnumber[,fixed,precision=3]{\ComputedValue}%
    }{%
        #1% Still working on header
    }%
}%
\newtoggle{DoneWithHeader}%
\togglefalse{DoneWithHeader}%
\newcommand{\StartingHeader}{\global\togglefalse{DoneWithHeader}}%
\newcommand{\DoneWithHeader}{\global\toggletrue{DoneWithHeader}}%
\newcolumntype{R}{>{\collectcell\FactorCell}r<{\endcollectcell}}
\begin{document}
\begin{tabular}{rrrr}
    \toprule
              & A     & B     & C \
    \midrule
    1     & 122   & 133   & 156 \
    2     & 112   & 135   & 155 \
    3     & 150   & 139   & 158 \
    4     & 145   & 135   & 159 \
    5     & 150   & 130   & 200 \
    \bottomrule
\end{tabular}%
\hspace{2.0ex}
\begin{tabular}{rRRR}
    \toprule\StartingHeader
              & A     & B     & C \
    \midrule\DoneWithHeader
    1     & 122   & 133   & 156 \
    2     & 112   & 135   & 155 \
    3     & 150   & 139   & 158 \
    4     & 145   & 135   & 159 \
    5     & 150   & 130   & 200 \
    \bottomrule
\end{tabular}%
\end{document}

Automated Method:

If you will always have a \toprule before the header row, and a \midrule prior to the data entries, you can automate the setting of \StartingHeader and \DoneWithHeader. With this version, the only change required is switching the data column type form r to R.

The MWE below produces output identical to the above.

References:

I needed to add \noexpand in the redefinition of \toprule and \midrule. See Wrapping \cmidrule in a macro for more details. It seems that \toprule and \midrule have a similar issue.

Code:

\documentclass{article}
\usepackage{booktabs}
\usepackage{collcell}
\usepackage{pgf}
\usepackage{etoolbox}
\usepackage{letltxmacro}
\newcommand{\Factor}{105}%
\newcommand{\FactorCell}[1]{%
    \iftoggle{DoneWithHeader}{%
        \pgfmathsetmacro{\ComputedValue}{#1/\Factor}%
        \pgfmathprintnumber[precision=3]{\ComputedValue}%
    }{%
        #1% Still working on header
    }%
}%
\newtoggle{DoneWithHeader}%
\togglefalse{DoneWithHeader}%
\newcommand{\StartingHeader}{\global\togglefalse{DoneWithHeader}}%
\newcommand{\DoneWithHeader}{\global\toggletrue{DoneWithHeader}}%
\LetLtxMacro\OldTopRule\toprule
\def\toprule{\StartingHeader\noexpand\OldTopRule}
\LetLtxMacro\OldMidRule\midrule
\def\midrule{\DoneWithHeader\noexpand\OldMidRule}
\newcolumntype{R}{>{\collectcell\FactorCell}r<{\endcollectcell}}
\begin{document}
\begin{tabular}{rrrr}
    \toprule
              & A     & B     & C \
    \midrule
    1     & 122   & 133   & 156 \
    2     & 112   & 135   & 155 \
    3     & 150   & 139   & 158 \
    4     & 145   & 135   & 159 \
    5     & 150   & 130   & 200 \
    \bottomrule
\end{tabular}%
\hspace{2.0ex}
\begin{tabular}{rRRR}
    \toprule
              & A     & B     & C \
    \midrule
    1     & 122   & 133   & 156 \
    2     & 112   & 135   & 155 \
    3     & 150   & 139   & 158 \
    4     & 145   & 135   & 159 \
    5     & 150   & 130   & 200 \
    \bottomrule
\end{tabular}%
\end{document}

divide table cells by a certain amount

2 Answers2

Notes:

Manually Specify end of Header:

Code:

Automated Method:

References:

Code:

Linked