Log In
Register
Home
Excel Tables
July 25, 2013
Offline
I need help making a formula dynamic. I use a formula that multiplies an annual salary by the number of elapsed months and then subtracts the sum of the prior computed amounts. This eliminates rounding errors caused my dividing a cost by 12 and then summing the results. However, the formula does not work when there is a mid-period increase in the cost. For example, as shown in the highlighted cells in the attached file, the formula needs to restart the summing counter when the salary is increased. Although I have added helper columns, I get circular errors when I change to month to one that is later than 2/28/21.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Can you share a sample file showing what your data and formula looks like, so we can get a better understanding of what you are trying to achieve.
Br,
Anders
Excel Tables
July 25, 2013
Offline
The file has been added.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
When opening your file I am met with an alert that there one or more circular references, this needs to be looked up on. Also when looking through your data I notice that the formulas in rows 10 and 14 differs from the rest, perhaps intentionally.
In rows 10 and 14, starting from column G, your formulas has lost some references. Notice the #REF! errors.
Example from G10 (the dividers in Swedish regional settings is a semicolon, not a comma):
=IF(G6=EOMONTH($D10;0);$E10/12;IF(AND(G6>$D10;G6<$AX10);$E10*COLUMNS(G:$J)/12-SUM(F10:$I10);IF(G6=$AX10;$AY10/12;IF(G6>$AX10;$AY10*COLUMNS(#REF!)/12-SUM(OFFSET($G10;0;$AZ10;1;COLUMNS(#REF!)));0))))
It seems the formula is not consistent through the row, from column N for example there is only one #REF! error and from column S there are no #REF! errors, but the reference is the same in S10 and T10, but differs in the following columns.
If you correct these errors, do you then get correct results?
Br,
Anders
Excel Tables
July 25, 2013
Offline
Anders,
Thank you for your reply. I've re-attached the file, without #REF errors. Rows 10 and 14 are intentionally different, as they are the rows the I used to develop my formula. The circular error is one of the issues that I'm trying to solve.
Art
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I will look into this in more depth later this evening.
Br,
Anders
VIP
Trusted Members
December 7, 2016
Offline
Hello Arthur,
I have done some changes in your formula, see attached file.
The formula is now consistent for all rows and columns and simplified.
Formula in cell G7 now looks like this:
=IF(G$6<EOMONTH($D7,0),0,
IF(G$6=EOMONTH($D7,0),$E7/12,
IF(OR(AND(G$6>$D7,G$6<$AX7),ISBLANK($AX7)),$E7/12,
IF(G$6>=$AX7,$AY7/12,0))))
I hope this helps you to continue with your work.
Br,
Anders
Excel Tables
July 25, 2013
Offline
Anders,
Thank you for your reply and the re-worked formula. However, I round all amounts to zero decimal places and want my sums to equal the annual salary, without rounding errors. That is why I calculate the annual salary for the number of elapsed months in a year and subtract the sum of the prior month's calculated amounts. Your formula divides the annual salary amount by 12, which results in rounding errors. Do you think you can modify my formula to accomplish my desired goal?
Art
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I don't really see any rounding issue. As attached picture shows (you might need to save it on your computer to be able to zoom in and see anything useful), the file on the left is the one I changed, the file on the right is your original (first uploaded).
I have removed all other data except for rows 10 and 14, as you have other formulas on those rows. I have added so we see four decimals and they are the same in both files. Do double check the file I uploaded against yours, if there are rounding issues, can you please highlight those?
Br,
Anders
*Edit*
I removed the picture as it was not readable.
Excel Tables
July 25, 2013
Offline
Anders,
I very much appreciate the time you've taken to try and help me.
I've re-attached the file and changed the formula in row 16 to better reflect the result that I'm looking for. You'll note that all amounts have been rounded to zero decimal places and that the amounts in columns K,N,Q, etc. are different from the prior columns. This is what I mean by a formula that corrects for rounding errors, as the sum of the monthly amounts equals the annual salary. If I just divided the annual salary by 12, and then rounded the results, the monthly amounts would not sum without a rounding error. For example, 50,000/12 round to 4,167, per month. The annual sum of, 4167 is 50,004 and not 50,000.
Art
VIP
Trusted Members
December 7, 2016
Offline
Hello,
The problem I see with your approach is that you have to break the formula the second month from start, else the monthly salary for the second month will be wrong. I notice that you have done so in K16. As the start date is different per employee, you will have several breaks in the formula consistency and I would avoid that if I were you.
I assume it is important for you to have the monthly salaries rounded. Let's see if I can find a trick to solve this, can't promise though as I normally stay clear of such issues.
Br,
Anders
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I am sorry to say that I can't find a working solution that rounds the monthly salary as you want it. Hopefully someone else in this forum knows a way.
Good luck and stay healthy.
/Anders
July 16, 2010
Offline
Hi Art,
In my investment banking budgeting experience we used to just let the number formatting do the rounding on the face of the cell and allow the actual underlying value to be stored to as many decimal places as required so that the totals added up correctly.
It is immaterial in the scheme of things to worry about forcing some of the values in row 16 to read 4167 and others 4166. It should be commonly accepted among those involved in budgeting and reporting processes that there will be an element of rounding when displaying values without decimals.
I wouldn't waste your time trying to come up with a convoluted formula. How many people are going to spend time adding up the individual values in their head or on a calculator to check the totals. If they do, they should realise that variances of a few dollars here and there will be rounding. If you're concerned, you could put a note on the report/file to make them aware that individual values may not reconcile to the totals due to rounding.
I hope that reassures you that this isn't worth pursuing.
Mynda
Excel Tables
July 25, 2013
Offline
Mynda,
Thank you for your reply. I agree that the degree of rounding that my example uses is not necessary for traditional financial modeling. I posted the question because I'm interested in how to develop a formula that dynamically changes a counter when a variable changes.
Art
1 Guest(s)
