Excel formula addins?

[cdotyii]

I built a spreadsheet which contains the following formula:

+IF(R2<Summary!$G$2,1,MROUND(DATEDIF(Summary!$G$2,R2,"d")/30.5,0.5))

This is to calculate the number of months (to the half month) between the 1/1/12 and the Early Termination date of the lease.

Some of my colegues that need to use the sheet are getting errors for this formula. It is most likely due to an addon I have that they do not.

Is there a way to either:

Determine what addon the need?
or
Fix the formula to do the same thing without needed the addon? (Perferred)

thanks

[cdotyii]

Please, I really need help on this!

[The Exceller]

Hi cdotyii,

I cannot find anything wrong with your formula and it works great for me. I'm really not sure why it's not working for your colleagues but maybe this formula change will work for them. Let me know if this formula works for you and accomplishes the same task you set out to accomplish.

=IF(($R$2-Summary!$G$2)<0,1,ROUNDUP(DAYS360(Summary!$G$2,$R$2)/30,1))

This formula gets rid of the DATEDIF formula which I think might be why it's not workin for your colleagues. I read up on the DATEDIF formula and learned some interesting things (http://www.cpearson.com/excel/datedif.aspx). It might be partly because your colleagues are in a different version of MS Excel than you are working in.

Anyhow, let me know if the above formula works for you.

Best,
The Exceller

[The Exceller]

Actually, give this one a try instead. The above suggestion was not rounding to the multiple you wanted it to round to. Use this instead.

=IF(($R$2-Summary!$G$2)<0,1,MROUND(DAYS360(Summary!$G$2,$R$2)/30.5,0.5))

[cdotyii]

That wont help, the problem I found is they do not have the Analysis Toolpak which causes the Mround() to give the error. Is there a way to get the same result (ie to the half month) with a different formula?

Example: If the Lease term date is 6/15/12 then that should be 6.5 Months, if it is 5/31/12 or 6/30/12 that would give 6 or 7 respectively. So, basically I need it to calculate to the neares whole or half month, to get the overall rent calculation to be more accurate. I figured doing it by DAY would be to complicated, due to the 30 and 31 days & the Days360() vs. the actual 365 days. Any thoughts on that?

Need to calculate remaining rent from the 1/1/12 to the actual Expiration and/or Early Termination dates.

[royUK]

The analysis toolpak is delivered with Excel, it needs installing by going to Tools -> Addins then checking Analysis ToolPak. If it isn't in that list then Excel has not been installed properly

[shg]

You can always change

=MROUND(number1, number2)

to

=ROUND(number1/number2, 0) * number2

so ...

=IF(R2<Summary!$G$2, 1, ROUND(DATEDIF(Summary!$G$2, R2, "d") / 30.5 / 0.5, 0) * 0.5)