I think maybe your description isn't quite clear, if Fluff's formula didn't work for you.
Can you walk us through an actual example, showing us exactly what value you arrrive at and how?
BTW, a macro should be totally unnecessary for any of this. Excel's built-in functions should be able to handle it, so I don't see any advantage to using VBA here.