title: Conditional Calculation of Columns in R
body: <p>I have a dataset like the one shown below</p>
  
  <pre><code>library(tidyverse)
  dat &lt;- data.frame(col.1 = 1:16,
                    col.2 = c("B", "B", "B", "B", "B", "B", "A", "B", 
                              "A", "A", "B", "A", "A", "A", "A", "A"),
                    col.3 = c(30, 60, 75, 105, 40, 80, -20, 60, -20, -60, 40,
                              -40,-105,-20,-20,-45),
                    col.4 = c(39.34775, 31.66806, 28.57107, 28.43085, 29.30417, 36.21187,
                              40.29794, 40.70641, 65.85152, 66.85943, 69.26766, 67.24402,
                              74.85330, 79.17230, 78.75405, 64.47038))
  dat
  </code></pre>
  
  <p>I'm trying to reach the final column which looks like this:</p>
  
  <pre><code>dat.2 &lt;- dat %&gt;% 
    mutate(col.Final =  c(1180.43, 1900.08, 2142.83, 2985.24, 1172.17,
                          2896.95, -629.63, 2442.38, -655.37, -1966.11,
                          2770.71, -1460.48, -3833.76, -730.24, -730.24, 
                          -1643.04))
  </code></pre>
  
  <p>So far, I have tried using <code>mutate()</code> function to reach this point.</p>
  
  <pre><code>dat.1 &lt;- dat %&gt;% 
    mutate(col.5 = col.3*col.4) %&gt;% 
    mutate(col.6 = cumsum(col.3)) %&gt;% 
    mutate(col.7 = if_else(col.2 == 'B', col.6, col.6 - col.3),
           col.8 = col.3/col.7)
  </code></pre>
  
  <p>When I'm trying to reach the final column I'm not getting the same results.</p>
  
  <pre><code>dat.1 %&gt;% 
    mutate(col.9 = if_else(col.2 == 'A', col.8*lag(cumsum(col.5)), col.5))
  </code></pre>
  
  <p>Note: This same calculation was done successfully using Excel's <code>SUMIFS()</code> function.</p>
  
  <p>I'm Trying to get the same results with <code>R</code> instead.</p>
  
  <p>I have seen some of the Q&amp;A for similar posts but still stuck with the final calculation. In Excel, it felt as if iteration was performed for certain condition and then the next condition was executed. Though, not sure what was done using excel, I think, somehow this is possible using R as well. Just unable to figure out how to get that.</p>
  
  <p>Any help would be appreciated at this point.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>Values for <code>col.5</code> and <code>col.8</code> corresponding to <code>col.2</code>:</p>
  
  <pre><code>col.2 = c("B", "B", "B", "B", "B", "B", "A", "B", 
            "A", "A", "B", "A", "A", "A", "A", "A")
  col.5 &lt;- c(1180.4325, 1900.0836, 2142.8302, 2985.2393, 1172.1668,
             2896.9496, -805.9588, 2442.3846, -1317.0304, -4011.5658,
             2770.7064, -2689.7608, -7859.5965, -1583.4460, -1575.0810, 
             -2901.1671)
  col.8 &lt;-c(1.00000000, 0.66666667, 0.45454545, 0.38888889, 0.12903226,
            0.20512821, -0.05128205, 0.13953488, -0.04651163, -0.14634146,
            0.10256410,-0.10256410, -0.30000000, -0.08163265, -0.08888889, 
            -0.21951220)
  </code></pre>
  
  <p>Verifying values Using Hand Calculation!</p>
  
  <p>Calculations using <code>col.5</code> &amp; <code>col.8</code></p>
  
  <p>for "B" from top :</p>
  
  <p><code>1180.43 + 1900.08 + 2142.83 + 2985.24 + 1172.17 + 2896.95 = 12277.7020</code></p>
  
  <p>for A after :</p>
  
  <p><code>12277.7020 x -0.05128205 = -629.6266509</code> .... the 1st desired value for A</p>
  
  <p>for "B" after:</p>
  
  <pre><code>12277.720 - 629.6266509 = 11648.07535
  11648.07535 + 2442.3846 = 14090.45995
  </code></pre>
  
  <p>for "A" after: </p>
  
  <pre><code> 14090.45995 x -0.04651163 = -655.37026 ... 2nd desired Value for A
  </code></pre>
  
  <p>for "A" after:</p>
  
  <pre><code> 14090.45995 - 655.37026 = 13435.08969
   13435.08969 x -0.14634146 = -1966.110641 ... 3rd desired value for A
  </code></pre>
  
  <p>and so on....</p>
  
  <p>I hope this explains.</p>