Build Excel formulas with string replacements

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)

• What happens when J[i] is exactly 500000? – Der Kommissar Dec 5 '16 at 7:08
• Ah, damn, you're right. Lucky none of this made it to production (yet). – Aroueterra Dec 5 '16 at 7:10
• 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. – user2023861 Dec 5 '16 at 16:47

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---
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.

• 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. – Aroueterra Dec 5 '16 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.

• "horizontal scroll is evil" -- your code side-scrolls on my laptop T_T – Alex Reinking Dec 5 '16 at 11:02
• @AlexReinking better? – candied_orange Dec 5 '16 at 13:30
• 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. – user2023861 Dec 5 '16 at 16:44