How to calculate cell values after a certain days range in excel?

user_8730437 0
Viewed 30

Here is the table and i need to calculate the amount based upon the days. Reference table

cell For days 8 to 14 cell A shows 10000, for days 15 to 21 cell A shows B+C and after 21 days cell A shows B+C+D. After 28 days with every passing week it should add cell D values additionally. for example after 28 days calculation should be B+C+D+D and so on.

Here is the formula I'm using.

=IF(AND($E$10>7,$E$10<=14),$B$10,IF(AND($E$10>14,$E$10<=21),SUM($B$10:$C$10),IF($E$10>21,SUM($B$10:$D$10),"")))

need help to calculate values after 4 weeks, on every passing week value of cell D should be added.

calculation formula excel
1 Answers

Try:

=IF($E$10>21, $B$10+$C$10+(INT(($E$10-1)/7)-2)*$D$10, IF($E$10>14,$B$10+$C$10,IF($E$10>7,$B$10,0)))

In Excel, IF statement execution stops at the first logical test that returns TRUE, so by reversing the order of testing, we can eliminate all those AND functions you have in your original.

Then it's just a matter of computing the correct number of D's to add onto B and C

user_28729220
Related