This may be considered an algorithm question rather than Excel, but my question is specific to an implementation in Excel.
I need to reduce a daily rate based on calendar days. From days 1 to 20, the daily rate is 100% of a given rate, at day 21 the rate is reduced by 2%, day 28 an additional 2% and so on every 7 days until day 100. I want to avoid using VBA and I can already accomplish this by using multiple cells to calculate the different values and sum them. But I'm hoping there's a more elegant formula, regardless of how complex, that I can put in a single cell to solve the problem such that A1 contains the full daily rate, A2 contains the total number of days, and A3 gives me the total dollar amount based on the reduction formula. I suspect there's a way to use MOD for this but I haven't been able to figure it out.
I need to reduce a daily rate based on calendar days. From days 1 to 20, the daily rate is 100% of a given rate, at day 21 the rate is reduced by 2%, day 28 an additional 2% and so on every 7 days until day 100. I want to avoid using VBA and I can already accomplish this by using multiple cells to calculate the different values and sum them. But I'm hoping there's a more elegant formula, regardless of how complex, that I can put in a single cell to solve the problem such that A1 contains the full daily rate, A2 contains the total number of days, and A3 gives me the total dollar amount based on the reduction formula. I suspect there's a way to use MOD for this but I haven't been able to figure it out.
|