Build Excel formulas with string replacements

for (long i = 2; i 500000,(J" + i + "-500000)*0.32+125000)))))))";
    WS.Cells[i, 11].Formula = quarry;   
}

I had originally intended to make this alot neater by adding the base string into a StringBuilder and then doing a string.Replace call on the column value with the long variable i, but it did not work saying i cannot be converted to char.

Not limited to a string builder, how else can I improve this statement where I could easily change the value of a very long string by replacing the concerned elements?

(i.e: this is a pretty long Excel formula C# query that gets inserted into the worksheet as the for loop rolls)

I'm not even going to review the C#, because there's a far better way to do the Excel portion.

Instead of creating this enormous formula (which I hope you don't need anymore nested IF portions, you've hit Excel's limit of 7), you can create a table that has the formula properties in it, then lookup what's in that table.

So, let's create our formula table/area:

---L---+--M--+---N---
Level  |Rate |Add
      0| 0.05|      0
  10000|  0.1|    500
  30000| 0.15|   2500
  70000|  0.2|   8500
 140000| 0.25|  22500
 250000|  0.3|  50000
 500000| 0.32| 125000

So, with that, we can build our actual formula:

=(J1-INDEX(L:L,MATCH(J1,L:L,1)))*INDEX(M:M,MATCH(J1,L:L,1))+INDEX(N:N,MATCH(J1,L:L,1))

If you're not an Excel whiz, here's what's happening:

The INDEX(...) function in a formula will do a lookup on the first value in a column that matches a specific location. So, INDEX(L:L,1) will return the value in L1. Simple enough.

Next, MATCH(...) is a function that will find the first value in a column that is less than, equal to or greater than the provided value. So MATCH(J1,L:L,1) will find the index of the cell that is the last cell found which is less than (the final 1 parameter) our current cell. So from our L column, for 1 it finds 0, etc. It then returns the row that cell is on, which can be piped to INDEX to find the actual value in that cell (or a different column).

So when we put it together, assuming J1 is 100000, we're replacing our formula with:

=(100000-70000)*0.2+8500

Which gives us 14,500, the exact value your formula gives us.

Some bonuses of this version:

-
Shorter, more concise formula. Easy copy/paste, easy to change the values later. (Suppose the criteria for 70000 become 65000, easy to change.)

-
More descriptive information, adding values is a piece of cake (and requires 0 work from the programmer).

-
Doesn't require a C# programme to pre-fill the spreadsheet. (That's a huge bonus right there.)

-
Makes you look like a boss being able to show the business guys how this stuff works.

-
Less typing in the future.

These are not mutually exclusive items.

StackExchange Code Review Q#148967, answer score: 18