@RajPersia
First of all, it's a classic example of a formula that should never have been written, for exactly what you're discovering: it's virtually unintelligible.
I'm going to assume you didn't write it, that somebody else did, somebody else created the spreadsheet and you're now assigned to manage it, use it, keep it functioning.... and the worst part is that "somebody else" was probably really proud of himself/herself when he/she got the function to work, rather than being ashamed for having created a monster.
OK, rant ended.
What follows is a first pass at interpreting it, but you will need to fill in some blanks
=IF(AF36="Defaulted",100% this is easy: the result is 100% if AF36 is "Defaulted"
MIN( otherwise the result is the lesser of.....
(($BC$35-DATE(YEAR($BC$35),1,1)+1)/365) all of the calculations in red,
* the first of which creates a fraction: how much of year in cell BC35 has passed
SUMPRODUCT(($BI$33:$BI$142=AF36) and then multiplies that fraction
* by whatever is in these other cells and so on and on and on
($BJ$33:$BJ$142=YEAR($BC$35)+1) ad infinitum, or ad nauseam,
* whichever you prefer
$BL$33:$BL$142)
+ Where YOU can help is in providing info on what's
(1-($BC$35-DATE(YEAR($BC$35),1,1)+1)/365) in the cells and
* cell ranges referenced by this ridiculously complicated formula
SUMPRODUCT(($BI$33:$BI$142=AF36)*($BJ$33:$BJ$142=YEAR($BC$35))
*
$BL$33:$BL$142)
, AM36) ...the lesser of all of that convoluted multiplication
and addition OR whatever is in cell AM36.
) the end!
For the record: it would have been far more intelligent, and less show-offy, to have created several "helper columns" to do the various calculations that are embedded in this monstrosity, and then a single IF that pulls those intermediate (and now intelligible) results into a simple
IF this THEN that1, ELSE the MIN of that2, that3
Do you have access to the person who wrote it in the first place? It actually might be suitable, uh, reward for their accomplishment to have them stand before a jury of their peers and give a defense for having written in that way, after first saying in simple English what it does.
