a complex formula, while i have no idea why it works, it does exactly what i
need it to do
thank you
brock
Glad to help. Thanks for the feedback.
How does it work?
1. Floor(a1,100) rounds down to the nearest 100.
2. MOD(a1,100) returns the last two digits.
3. Check to see if the last two digits are greater than 39, 69, or 99.
(It'll never be greater than 99)
4. The MOD....> ... section will return an array of TRUE or FALSE depending on
what those last two digits are. For example, if the last two digits are 75,
the function will return the array {TRUE,TRUE,FALSE}
5. The MATCH(False,...) returns a number corresponding to the location of the
first FALSE. In the above example, it would return a 3.
6. The INDEX function uses that 3 to decide which of the number in the array
{39,69,99} to return and add to the first term.
If you use Tools/Formula Auditing/Evaluate Formula, you may be able to better
see the progression of the calculation.
--ron
