Need to shorten the formula

[neeraj_logani]

Hi, I have an excel file which has a formula of 2 pages. would like to check if there is a more efficient way of writing this formula



=IF($E3>EOMONTH(AD$2,0),"Agreement Not Started",IF($J3<AD$2,"Agreement Ended",
IF( $G3>EOMONTH(AD$2,--($S3=1)*-1),"Paid till "&TEXT($G3,"mmm-yy"),IF( $G3=EOMONTH(AD$2,0),"Paid start next month",
IF( AND( EOMONTH($E3,0)<AD$2, OR( AND($S3=3,MOD(MONTH(AD$2),$S3)<>1), AND($S3>=6,MOD(MONTH(AD$2),$S3)<>MOD(MONTH($E3),$S3)) ) ), "Paid "&$R3,
IF( EOMONTH($E3,0)>=AD$2,
SUM(
( $K3 / DAY(DATE($X$1,MONTH($E3)+1,0)) * (DAY(DATE($X$1,MONTH($E3)+1,0))+1-DAY($E3)) ),
( IF($S3=3, ( $K3 * DATEDIF(DATE($X$1,MONTH($E3),DAY($E3)),DATE($X$1,MROUND(MONTH($E3)+ROUNDUP($S3/2,0)-1,$S3)+1,0),"m") ), $K3 * ($S3-1) ) ),
( IF($S3=3, ( $I3 * DATEDIF(DATE($X$1,MONTH($E3),DAY($E3)),DATE($X$1,MROUND(MONTH($E3)+ROUNDUP($S3/2,0)-1,$S3)+1,0),"m") ), $I3 * $S3 ) )
),
IF( $S3=3,
IF( AND(DATE($X$1,MROUND(MONTH(AD$2)+1,3)-2,1)<=$X3,$X3<=DATE($X$1,MROUND(MONTH(AD$2)+1,3)+1,0)),
SUM(
( $Y3 * DATEDIF(AD$2,$X3,"m") ),
( $Y3 / DAY(DATE($X$1,MONTH($E3)+1,0))*(DAY($E3)-1) ),
( IF( $J3<=$X3, 0, $Z3 / DAY(DATE($X$1,MONTH($E3)+1,0))*(DAY(DATE($X$1,MONTH($E3)+1,0))+1-DAY($E3)) ) ),
( IF( $J3<=$X3, 0, $Z3 * DATEDIF(DATE($X$1,MONTH($E3),1),EDATE(AD$2,$S3-1),"m") ) ),
( $I3 * IF( $X3-1=$J3, DATEDIF(AD$2,DATE($X$1,MONTH($E3),1),"ym")+1, $S3) )
),
SUM( ($S3*$Y3), ($S3*$I3) )
),
IF( $S3=1,
SUM(
( IF( EOMONTH(AD$2,-1)<$X3, $Y3, $Z3) / DAY(EOMONTH(AD$2,-1)) * IF(AND(EOMONTH($E3,-1)+1<=EDATE(AD$2,-1),EDATE(DATE($X$1,MONTH(AD$2),DAY($E3-1)),-1)<=$J3),DAY($E3)-1,0) ),
( IF( $J3<=DATE($X$1,MONTH(AD$2),DAY($E3)), 0, IF( EOMONTH(AD$2,0)<$X3, $Y3, $Z3) / DAY(EOMONTH(AD$2,-1)) * IF(AND(EOMONTH($E3,-1)+1<=EDATE(DATE($X$1,MONTH(AD$2),DAY($E3)),-1),EOMONTH(AD$2,-1)<=$J3), DAY(EOMONTH(AD$2,-1))+1-DAY($E3), 0) ) ),
( IF( EDATE(AD$2,-1)<=$J3, $S3*$I3, $I3) )
),
SUM(
( $Y3 / DAY(EOMONTH(AD$2,0)) * IF(AND(EOMONTH($E3,-1)+1<=AD$2,DATE($X$1,MONTH(AD$2),DAY($E3-1))<=$J3),DAY($E3)-1,0) ),
( $Z3 / DAY(EOMONTH(AD$2,0)) * IF(AND(EOMONTH($E3,-1)+1<=DATE($X$1,MONTH(AD$2),DAY($E3)),EOMONTH(AD$2,0)<=$J3),DAY(EOMONTH(AD$2,0))+1-DAY($E3),0) ),
( IF( AND($S3<>1,EDATE(AD$2,1)<=$J3),$Z3*($S3-1),0) ),
( IF( EDATE(AD$2,1)<=$J3,$S3*$I3,$I3) )
)
)
)
) * (1+$P3)
)
))
))

 

[6StringJazzer]

Instead of asking us to reverse engineer the formula, please give a description of what you want the formula to do, and what data is in each referenced cell and column.

 

[neeraj_logani]

Instead of asking us to reverse engineer the formula, please give a description of what you want the formula to do, and what data is in each referenced cell and column.

Copy of Copy of Master MOF_ formula_2021

Sheet2 7800,17,7800,7800 8/17/2019,7800,4025.806452,31-Jan-19,16,15,4025.806452,3774.193548,7800,4025.806452,3774.193548,7800,7800 7800,28-Feb-19,16,12,4457.142857,3342.857143,7800,4457.142857,3342.857143,7800,7800 7800,31-Mar-19,16,15,4025.806452,3774.193548,7800,4025.806452,3774.193548,7800,78...

docs.google.com

here is the link to the file , please refer to Tab Main 2018, I am referring to data points by using multiple conditions to get status if amount is due for a month then how much. since the formula is long there are multiple points.

Any help will be appreciate