for (long i = 2; i <= numberOfRows; i++)
    string quarry =
        "=IF(J"+i+"<10000,J"+i+"*0.05,IF(J"+i+ "<30000,(J" + i + "-10000)*0.1+500,IF(J" + i + "<70000,(J" + i + "-30000)*0.15+2500,IF(J" + i + "<140000,(J" + i + "-70000)*0.2+8500,IF(J" + i + "<250000,(J" + i + "-140000)*0.25+22500,IF(J" + i + "<500000,(J" + i + "-250000)*0.3+50000,IF(J" + 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)

  • 1
    \$\begingroup\$ What happens when J[i] is exactly 500000? \$\endgroup\$ Commented Dec 5, 2016 at 7:08
  • \$\begingroup\$ Ah, damn, you're right. Lucky none of this made it to production (yet). \$\endgroup\$
    – Avan
    Commented Dec 5, 2016 at 7:10
  • \$\begingroup\$ You should consider splitting up the Excel formula. Larger Excel formulas have the same problems that larger C# methods have. They're difficult to maintain. Can you imagine searching this formula for a missing paranthesis? An Excel spreadsheet has lots of columns; why not use them? Store your intermediate results in columns somewhere on your sheet, or on another sheet to hide them. \$\endgroup\$ Commented Dec 5, 2016 at 16:47

2 Answers 2


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:

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:


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:


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.

  • 1
    \$\begingroup\$ This is great, though I can't comment about the accuracy of the code since I definitely do not qualify as an Excel whiz, it's certainly something I've never thought of. Will need some time to digest the info. \$\endgroup\$
    – Avan
    Commented Dec 5, 2016 at 9:16

Horizontal scroll is evil.

for (long i = 2; i <= numberOfRows; i++)
    string quarry 
        = "=" 
        + "IF(J" + i + "<10000,"+" J" + i + "*0.05,"
        + "IF(J" + i + "<30000,(J" + i + "-10000)*0.1+500,"
        + "IF(J" + i + "<70000,(J" + i + "-30000)*0.15+2500,"
        + "IF(J" + i + "<140000,(J" + i + "-70000)*0.2+8500,"
        + "IF(J" + i + "<250000,(J" + i + "-140000)*0.25+22500,"
        + "IF(J" + i + "<500000,(J" + i + "-250000)*0.3+50000,"
        + "IF(J" + i + ">500000,(J" + i + "-500000)*0.32+125000"
        + ")))))))"
    WS.Cells[i, 11].Formula = quarry;   

Even if you're just trying out an idea don't let code become unreadable. It only makes it that much harder to spot bugs and make improvements.

  • 2
    \$\begingroup\$ "horizontal scroll is evil" -- your code side-scrolls on my laptop T_T \$\endgroup\$ Commented Dec 5, 2016 at 11:02
  • 1
    \$\begingroup\$ @AlexReinking better? \$\endgroup\$ Commented Dec 5, 2016 at 13:30
  • \$\begingroup\$ Your code relieves the horizontal scrolling in the C# code, but not in Excel. If someone wanted to check out the formula in Excel, they'll be scrolling left and right. \$\endgroup\$ Commented Dec 5, 2016 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.