locked
Running Total RRS feed

  • Question

  • Hi Everyone,

    I'm new to  C# and I ran into a problem and was wondering if something can provide me with some assistant on the below problem. I'm trying to simulate an excel formula into C#:


    I need to obtain the value of F17-Q17. I already have the value of F10-Q10 store in jan,feb,mar, apr.........dec variable. Row15 formula is straight forward "=Q10*$E$15" and Row16 formula is the following:"=IF((115500-SUM($F10:Q10))>0,0,((115500-SUM($F10:Q10))*$E16)-SUM($F16:P16))".

    My problem is, how do I maintain a running total of Row16 so I can subtract it from Row15 value get a final result of Row17 by month.  I was hoping to write two method and then evaluate the logical operation if the sum of Row10(Jan-Evaluate month) > 115500 and call one of the two method.  

    Appreciate any help or guidance.

    Thanks

    Anthony


    • Edited by zXSwordXz Monday, May 5, 2014 8:09 PM
    Monday, May 5, 2014 8:08 PM

Answers

  • "What I'm try to avoid is create a variable to each element and then have to  store each result in a variable and then keep adding  variable value to  evaluate the running  total"

    In that case use an array or list so that you can use LINQ.  Use an array if you know the size of the data is fixed or a list if the size can be variable.  Irrelevant you'll be relying on the fact that they both implement IEnumerable<T> which is where LINQ comes in.

    For base calculations a simple extension method is probably sufficient (assume values is your array/list of values).  Row15 is trivial and can be handled by using a simple extension method off the entire list if you want.

    static class CalculationExtensions
    {
        public static IEnumerable<double> CalculateRate ( this IEnumerable<int> source, double rate )
        {
            foreach (var value in source)
                yield return value * rate;
        }
    }
    
    //When you need it
    var row15 = values.CalculateRate(0.0765);

    Row16 is slightly harder because you have to calculate the value for the previous rows but other than that it relies on the same base set of data.  Again an extension method could be used.

    I'm making assumptions about the row16 formula based upon your screenshot:

    • SUM(F10:Q10) is actually a running sum so F16 would be SUM(F10:F10), G16 would be SUM(F10:G10), etc. If this weren't true then all the values in row 16 would be the same
    • SUM(F16:P16) is a running total of the previously calculated values for row16
    public static IEnumerable<double> CalculateSpecialRate ( this IEnumerable<int> source, double rate, int minmumValue )
    {
        var runningSum = 0;
        var runningSpecialRate = 0.0;
           
    //This approach calculates the values as you go but you could
    //also precalculate the values and then enumerate normally foreach (var value in source) { runningSum += value; var delta = minmumValue - runningSum; if (delta > 0) yield return 0; else { var result = (delta * rate) - runningSpecialRate; runningSpecialRate += result; yield return result; }; }; } //Later var specialRates = values.CalculateSpecialRate(0.062, 115500);

    An alternative approach would be to create a custom type that stores the base value and the additional calculations but Row16 requires that you have other values.  Since this is ultimately going to the DB then a simple DataTable would be fine.  Ultimately though the core logic wouldn't change.  Note however that since you're storing the data in the DB then any change to the values would invalidate the calculations.  As such if you want to be able to update the month values in the DB then you'll likely need to implement a trigger to recalculate the values or use a computed column for the calculations instead.  That is an entirely different game though.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    • Marked as answer by zXSwordXz Wednesday, May 7, 2014 1:25 AM
    Tuesday, May 6, 2014 2:19 PM

All replies

  • I assume you're using ADO.NET to read the data from Excel.  If so then why not just read all the data rather than trying to replicate the calculated value?  I would recommend against storing each value in a separate variable.  Instead store the main values in an array.  You can then use LINQ to do calculations as needed. In the case of summation you can use this to sum the values in an array:

    var sum = myValues.Sum();

    I started to work out the code for you but your formulas proved to be confusing.  It appears that in some cases you're using fixed cell references but in other cases you're using relative references. As such I don't really know how to actually translate your formulas.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Monday, May 5, 2014 8:47 PM
  • Hi Michael,

    Sorry for  the confusion.  I'm building a C# application and  need to recreate these calculation in C# and store them in an SQL Table.  I can handle the storing  part.  What I'm try to avoid is create a variable to each element and then have to  store each result in a variable and then keep adding  variable value to  evaluate the running  total.  There has to be a quicker more efficient way.  Like creating method in  a class and passing in the monthly  value and let the method  do the calculation.  The end result such be 12 variable holding the monthly  value base on the two different calculation.

    Thanks

    Anthony

    Tuesday, May 6, 2014 3:43 AM
  • Excel has a dependency list so when one cell is changed all the dependency cells also have to be modified.   This is a recursive algorithm so as each cell is updated all other dependency cells also get updated.

    I'm not sure why you need to keep a running total.  The new month calculation can be computed using the previous month total.  In C# I would create a class which contains all the properties in a column from the excel worksheet and then make a list of the class like the code below

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static List<Column> columns = new List<Column>();
            static void Main(string[] args)
            {
            }
            class Column
            {
                int month;
                int year;
                int total;
                List<float> interest = new List<float>();
            }   
        }
    }
    


    jdweng

    Tuesday, May 6, 2014 5:00 AM
  • If you are going to store the data in a SQL database, why not do the calculations there? SQL is really good at this type of calculations. You will find more info on calculating running totals in SQL by following the link below

    http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver

    Tuesday, May 6, 2014 5:45 AM
  • "What I'm try to avoid is create a variable to each element and then have to  store each result in a variable and then keep adding  variable value to  evaluate the running  total"

    In that case use an array or list so that you can use LINQ.  Use an array if you know the size of the data is fixed or a list if the size can be variable.  Irrelevant you'll be relying on the fact that they both implement IEnumerable<T> which is where LINQ comes in.

    For base calculations a simple extension method is probably sufficient (assume values is your array/list of values).  Row15 is trivial and can be handled by using a simple extension method off the entire list if you want.

    static class CalculationExtensions
    {
        public static IEnumerable<double> CalculateRate ( this IEnumerable<int> source, double rate )
        {
            foreach (var value in source)
                yield return value * rate;
        }
    }
    
    //When you need it
    var row15 = values.CalculateRate(0.0765);

    Row16 is slightly harder because you have to calculate the value for the previous rows but other than that it relies on the same base set of data.  Again an extension method could be used.

    I'm making assumptions about the row16 formula based upon your screenshot:

    • SUM(F10:Q10) is actually a running sum so F16 would be SUM(F10:F10), G16 would be SUM(F10:G10), etc. If this weren't true then all the values in row 16 would be the same
    • SUM(F16:P16) is a running total of the previously calculated values for row16
    public static IEnumerable<double> CalculateSpecialRate ( this IEnumerable<int> source, double rate, int minmumValue )
    {
        var runningSum = 0;
        var runningSpecialRate = 0.0;
           
    //This approach calculates the values as you go but you could
    //also precalculate the values and then enumerate normally foreach (var value in source) { runningSum += value; var delta = minmumValue - runningSum; if (delta > 0) yield return 0; else { var result = (delta * rate) - runningSpecialRate; runningSpecialRate += result; yield return result; }; }; } //Later var specialRates = values.CalculateSpecialRate(0.062, 115500);

    An alternative approach would be to create a custom type that stores the base value and the additional calculations but Row16 requires that you have other values.  Since this is ultimately going to the DB then a simple DataTable would be fine.  Ultimately though the core logic wouldn't change.  Note however that since you're storing the data in the DB then any change to the values would invalidate the calculations.  As such if you want to be able to update the month values in the DB then you'll likely need to implement a trigger to recalculate the values or use a computed column for the calculations instead.  That is an entirely different game though.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    • Marked as answer by zXSwordXz Wednesday, May 7, 2014 1:25 AM
    Tuesday, May 6, 2014 2:19 PM