Hi Neil!
No problem. Never hesitate to ask why or how something works; you'll
get a lot more out of the help that way.
=DATE(A2,A1,1)
DATE function has three arguments. Year, Month, Day. You wanted the
first cell to be day 1 of the month in A1 and the year in A2. I
cheated a little and assumed that in A1 you would have the month
number rather than the month name, but it would be possible to convert
the name to the number using a more complex formula for the month
argument or use an intermediate helper cell.
=IF(A5="","",IF(MONTH(A5+1)<>$A$1,"",A5+1))
Start with the basic:
=IF(MONTH(A5+1)<>$A$1,"",A5+1)
The MONTH function returns the number of a month from a date serial
number argument. The condition argument therefore "asks", "Is the
month of the previous cell's date plus 1 day different from the month
number in A1?" If it is we return "". If it isn't, we just add 1 to
the previous cell's date.
However, that basic formula would hit problems in the second cell
after the last day of the month because in that case the previous cell
has been made "". We therefore "wrap" the basic formula in another IF
function so that if the previous cell is already "" we return "". If
it isn't, we use the basic formula.
=IF(A6="","",IF(C5>0,C5*((1+2%/12)^(12/365)-1),IF(C5>=-15000000,C5*((1+4%/12)^(12/365)-1),(C5*((1+4%/12)^(12/365)-1))+((C5-15000000)*((1+2%/12)^(12/365)-1)))))
Here we've nested another base calculation in a formula that returns
"" if the day column has been made "".
The base formula is:
=IF(C5>=0,C5*((1+2%/12)^(12/365)-1),IF(C5>=-15000000,C5*((1+4%/12)^(12/365)-1),(C5*((1+4%/12)^(12/365)-1))+((C5-15000000)*((1+2%/12)^(12/365)-1))))
There's three cases covered:
Case 1: C5 (the previous balance) > 0
Case 2: C5 >=-15000000
Case 3: C5 Other (in this case that means <15000000 because overdrafts
up to that level are covered by the first two cases.
For each case we have a separate calculation.
So the base formula structure is:
=IF(Case1Condition,Case1Calculation,IF(Case2Condition,Case2Calculation,Case3Calculation))
We have three calculations:
Case 1 Calculation:
C5*((1+2%/12)^(12/365)-1)
C5 is multiplied by an interest rate. I assumed a Nominal rate
compounded monthly and needed to convert to daily effective
equivalent. That is what the following does using a rate of return on
positive balances of 2% Nominal compounded monthly.
((1+2%/12)^(12/365)-1)
This is based upon the general conversion of interest rates formula
(1+Nomx/Freqx)^Freqx = (1+Nomy/Freqy)^Freqy
Note:
Nomx/Freqx = Effx
Nomy/Freqy = Effy
If Freqx or Freqy is 1 then the Nomx or Nomy is annual effective.
Case 2 Calculation:
C5*((1+4%/12)^(12/365)-1)
Same as before only for balances down to 15000000 we use 4%.
Case 3 Calculation:
(C5*((1+4%/12)^(12/365)-1))+((C5-15000000)*((1+2%/12)^(12/365)-1))
Two parts to this calculation; 4% used on the total balance plus
additional 2% on the overdraft above 15000000.
Looking at the problem again, I'd revise the formula to meet a
potential problem that is likely to be noticeable at data entry: I'd
use:
=IF(C5>=0,C5*((1+2%/12)^(12/365)-1),IF(C5>=-15000000,C5*((1+4%/12)^(12/365)-1),(C5*((1+4%/12)^(12/365)-1))+((C5-15000000)*((1+2%/12)^(12/365)-1))))
Notice the first test is C5>=0 and not as before C5>0.
As it was, C5=0 was handled as Case 3 and would produce an interest
calculation for a zero overdraft entry.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/