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