9
\$\begingroup\$
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)

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

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.

\$\endgroup\$
  • 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\$ – Aroueterra Dec 5 '16 at 9:16
9
\$\begingroup\$

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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