I have a 3x3 matrix that I want to populate (may grow to 3x4 or 3x5 but not larger). Very simple with a dual for loop except that each row has a unique formula and each column has a unique column within the formula.

I started trying to create for loops, case statements, but ended up just brute force updating each cell.

Then I thought maybe some master crafter has some ideas. Is there any way to make this simpler:

myWorksheet.Cells[1, 1].Formula = "=ROUND(AVERAGE(B$2:B$"   counter   "), 3)";
myWorksheet.Cells[1, 2].Formula = "=ROUND(AVERAGE(C$2:C$"   counter   "), 3)";
myWorksheet.Cells[1, 3].Formula = "=ROUND(AVERAGE(D$2:D$"   counter   "), 3)";
myWorksheet.Cells[2, 1].Formula = "=MAX(B$2:B$"   counter   ")";
myWorksheet.Cells[2, 2].Formula = "=MAX(C$2:C$"   counter   ")";
myWorksheet.Cells[2, 3].Formula = "=MAX(D$2:D$"   counter   ")";
myWorksheet.Cells[3, 1].Formula = "=STDEV(B$2:B$"   counter   ")";
myWorksheet.Cells[3, 2].Formula = "=STDEV(C$2:C$"   counter   ")";
myWorksheet.Cells[3, 3].Formula = "=STDEV(D$2:D$"   counter   ")";

CodePudding user response：

Your formula has three portions, the function name, the argument, and the closing. You can make a method that will create the formula to insert based on the cell's row and column coordinates.

public static string RenderFormula(int row, int column, int counter)
{
    var stringBuilder = new StringBuilder();
    stringBuilder.Append("=");
    
    // part 1 - the function name
    var methodName = row switch
    {
        1 => "ROUND(AVERAGE(",
        2 => "MAX(",
        3 => "STDEV(",
        _ => throw new ArgumentException(nameof(row))
    };
    stringBuilder.Append(methodName);
    
    // part 2 - the argument
    char rangeColumnLetter = (char)('A'   column);
    var range = $"{rangeColumnLetter}$2:{rangeColumnLetter}${counter}";
    stringBuilder.Append(range);
    
    // part 3 - the closing
    var methodEnding = row switch
    {
        1 => "), 3)",
        2 or 3 => ")",
        _ => throw new ArgumentException(nameof(row))
    };
    stringBuilder.Append(methodEnding);
    
    return stringBuilder.ToString();
}

1. In part one, you know which excel function to use based on the row number.
2. In part two, you add the column number to a capital "A" to get the new range column letter. This means that (column 1 "A" = "B"). Add the value of counter into it.
3. In part three, you need to close the function's parentheses. Row 1 formulas have two closing parentheses with another argument in the middle, so account for it.

This uses StringBuilder to avoid a lot of wasteful concatenations.

Then just call the method to find out what the formula to insert is.

Console.WriteLine(RenderFormula(1, 1, 50));
Console.WriteLine(RenderFormula(2, 2, 50));
Console.WriteLine(RenderFormula(3, 3, 50));

// =ROUND(AVERAGE(B$2:B$50), 3)
// =MAX(C$2:C$50)
// =STDEV(D$2:D$50)