Rounding by adding or subtracting 1 unit to the highest or lowest value item in list until row totals 100

Question

I have a pre populated Datatable, each row in that Datatable needs to add up to 100

I'm trying to use the approach that if the total value of the row is less than 100, I loop through the values in that row and add 1 unit to the largest value first, check if 100 is reached, if not add 1 unit to the second largest value in the row and so on.

example:

original data

50.2, 40.6, 9.0 total 99.8

after rounding:

50.3, 40.7, 9.0 total 100.0

If the overall total value for the row is more than 100 then I wish to repeat this process but removing 1 unit from the largest value first etc...

I have hit a number of stumbling blocks, first sorting the datatable proved a problem as I don't know the column names etc so I converted it to an array.

The main problem is that I just can't get it to perform the final adjustment in the line for (int a = 0; a < temAdj; a++) as temAdj is always zero by then.

private DataTable RoundingAndScaling(DataTable dtInput, int startingCol, int decPlaces = 1)
    {
        int dtRows = dtInput.Rows.Count;
        int dtCols = dtInput.Columns.Count;
        double temAdj;
        double tempRowTotal;
        int tempSig;

        DataTable dtOuput = new DataTable();
        dtOuput = dtInput.Copy();

        DataTable dtFinal = new DataTable();

        dtFinal = dtInput.Copy();

        double[,] outputArray = new double[dtOuput.Columns.Count, 2];

        for (int r = 0; r < dtRows - 1; r++)
        {
            tempRowTotal = 0;

            for (int c = startingCol; c < dtCols; c++)
            {
                if (dtInput.Rows[r][c] == DBNull.Value)
                {
                    dtOuput.Rows[r][c] = 0.0;
                }
                else
                {
                    dtOuput.Rows[r][c] = Math.Round((double)dtInput.Rows[r][c], decPlaces);
                } 

                outputArray[c - startingCol, 0] = Convert.ToDouble(dtOuput.Rows[r][c]);

                tempRowTotal = tempRowTotal + Convert.ToDouble(dtOuput.Rows[r][c]);
            }              

            //now check if that all the cells in that row = 100
            if (tempRowTotal != 100)
            {

                tempSig = 1;

                //Sort the data
                double[,] arrayDb = new double[dtOuput.Columns.Count, 2];
                arrayDb = SortArray(outputArray, dtCols);

                //Find how many assets need to be adjusted by 1 unit
                temAdj = (tempRowTotal - 1) * (10 ^ decPlaces);      //10^
                if (temAdj < 0)
                {
                    temAdj = -1 * temAdj;
                    tempSig = -1;
                }

                //make the adjustment to the assets that have the largest holdings 
                for (int a = 0; a < temAdj; a++)
                {
                    dtOuput.Rows[(int)(arrayDb[dtCols, 1])][1] = (int)dtOuput.Rows[(int)(arrayDb[dtCols, 1])][1] - tempSig * 1 / (10 ^ decPlaces);   //^10
                }

                //get the data back into the correct structure to return
                //    for (int xx = 0; xx < dtCols; xx++)
                //    {
                //        dtFinal.Rows[dtRows][xx - 1] = dtOuput.Rows[xx][1];
                //    }
            }
        }

        return dtOuput;
    }

 private double [,] SortArray(double [,] inData, int tempLen)
    {
        double temVal1; 
        double temVal2;            

        for (int i = 0; i < tempLen; i++)
        {
            for (int j = i + 1; j < tempLen; j++)
            {
                if (inData[i,0] > inData [j,0])
                {
                    temVal1 = inData[i, 0];
                    temVal2 = inData [i,1];
                    inData [i,0] = inData [j,0];
                    inData[i,1] = inData [j,1];
                    inData[j, 0] = temVal1;
                    inData[j, 1] = temVal2;
                }                    
            }
        }

        return inData;
    } 

Personally I'm not happy with my approach at all and am sure there is a much simpler way to achieve what I am trying to do, I'll happily throw the above out and go for simpler approach :) Any help is much appreciated.

Answer 1

Just if anyone is interested in the finished code (it may help others) I have pasted it below.

I used the code provided by Kevin Cook and modified to my needs. In the example below you pass in a DataTable along with which row and columns you wish to look at. This is then written into a list along with the orginial order of the data. The list is sorted on the numberToBeRounded. The rounding code is then run and once completed the list is re-ordered back to its original order. This is then written back into a DataTable and returned.

private DataTable RoundScale(DataTable dtInput, int startingCol, int startingRow)
    {  
        int xOrder = 0;

        //write datatable values into a list (easier to work with)
        for (int j = startingRow; j < dtInput.Rows.Count; j++)
        {
            List<SortingData> numlist = new List<SortingData>();

            for (int iCol = startingCol; iCol < dtInput.Columns.Count; iCol++)
            {

                if ((dtInput.Rows[j][iCol] != DBNull.Value) && (Convert.ToDouble(dtInput.Rows[j][iCol]) != 0))
                {
                    SortingData LSO = new SortingData
                    {
                        NumberToBeRounded = Convert.ToDecimal(Math.Round((double)dtInput.Rows[j][iCol], 1)),
                        OrderNum = xOrder
                    };
                    numlist.Add(LSO);
                }
                else
                {
                    SortingData LSO_1 = new SortingData
                    {
                        NumberToBeRounded = Convert.ToDecimal(0.0),
                        OrderNum = xOrder
                    };

                    numlist.Add(LSO_1);
                }
                xOrder++;
            }

            //need to sort the list desc so unit is added to or removed from the largest item in the list first
            numlist = numlist.OrderByDescending(o => o.NumberToBeRounded).ToList();

            decimal numTotal = numlist.Sum(y => y.NumberToBeRounded);

            if (numTotal != 0)    //ignore if there are zero totals
            {
                decimal diff = 100.0m - numTotal;

                //the value should be only 1 decimal place
                int update = Convert.ToInt32(diff / .1m);
                if (update > 0)
                {
                    for (int x = 0; x < update; x++)
                    {
                        var sortednumlist = numlist[x % numlist.Count()];
                        sortednumlist.NumberToBeRounded += .1m;                          
                    }
                }
                else
                {
                    for (int x = 0; x < Math.Abs(update); x++)
                    {
                        var sortednumlist = numlist[x % numlist.Count()];
                        sortednumlist.NumberToBeRounded -= .1m;                           
                    }
                }
            }

            //change order of list back to original order
            numlist = numlist.OrderBy(o => o.OrderNum).ToList();

            //now write the list back into that datatable row
            for (int i = startingCol; i < dtInput.Columns.Count; i++)
            {
                var numlistout = numlist[i - startingCol];
                dtInput.Rows[j][i] = numlistout.NumberToBeRounded;
            }
        }

        return dtInput;
    }

public class SortingData
{
public decimal NumberToBeRounded { get; set; }
public int OrderNum { get; set; }   
}

Answer 2

List<decimal> numlist = new List<decimal>();

numlist.Add(50.2m);
numlist.Add(40.6m);
numlist.Add(9.0m);

decimal diff = 100.0m - numlist.Sum();

//This is set because the value should be only 1 decimal place
int update = Convert.ToInt32(diff / .1m);
if (update > 0)
{
    for (int x = 0; x < update; x++)
    {
        numlist[x % numlist.Count()] += .1m;
    }
}
else
{
    for (int x = 0; x < Math.Abs(update); x++)
    {
        numlist[x % numlist.Count()] -= .1m;
    }
}

I used decimal for better precision, but here is some example data to take the difference and try to have it equal 100%. (I also put in the code if you are over 100% and want to cut it down evenly as well)