Notifications
Clear all
Topic starter
| A | B | C | D | E | ANSWER | |
| DATE OF JOIN | CURRENT DATE | BASIC | DA | |||
| 1 | 01-01-2010 | 22-12-2018 | 8,000.00 | 2,340.00 | 517 | IF(AND(DATEDIF(B4,C4,"y")<5,(DATEDIF(B4,C4,"y")<10)),(D4+E4)*0%,(D4+E4)*5%) |
| DATEDIF(B4,C4,"y") = 8 | ||||||
| 2 | 01-01-2015 | 22-12-2018 | 8,000.00 | 2,340.00 | 0 | IF(AND(DATEDIF(B6,C6,"y")<5,(DATEDIF(B6,C6,"y")<10)),(D6+E6)*0%,(D6+E6)*5%) |
| DATEDIF(B4,C4,"y") = 3 | ||||||
| 3 | 01-01-2007 | 22-12-2018 | 8,000.00 | 2,340.00 | 1034 | IF(DATEDIF(B8,C8,"y")>10,(D8+E8)*10%,(D8+E8)*5% ) |
| DATEDIF(B4,C4,"y") = 18 | ||||||
| I WANT TO USE THESE 3 CONDITIONS IN ONE EQUATION | ||||||
| IF C5<5 =0% | ||||||
| IF C5 IS IN BETWEEN 5 & 10 =5% | ||||||
| IF C5 > 10 =10% | ||||||
Posted : 25/12/2018 6:24 am
Hello Sunil,
I hope below formula is what you are looking for.
=(D4+E4)*IF(DATEDIF(B4,C4,"y")<5,0%,IF(AND(DATEDIF(B4,C4,"y")>=5,DATEDIF(B4,C4,"y")<=10),5%,10%))
Merry Christmas
Posted : 25/12/2018 12:01 pm
You could also use a lookup:
=(D4+E4)*LOOKUP(DATEDIF(B4,C4,"y"),{0,5,10},{0,0.05,0.1})
or, since the bands are also the percentage values:
=(D4+E4)*FLOOR(DATEDIF(B4,C4,"y"),5)%
Posted : 28/12/2018 6:45 am
