Formula Translation?

[milwphil]

Good morning!

I'm attempting to understand a formula in a spreadsheet and I'm getting lost in an endless black hole of IF Statements/cell references and named ranges.

Can anyone help me translate this formula to logical English:

=IF(C37=0,"",IF(OV!AC8=5,"Contact",IF(AND(NRen="New",I37>VLOOKUP(OV!AC8&OV!T8,'ML Tables'!$AJ$3:$AN$26,IF(OV!Z8<0.85,5,4),0)),"Before",VLOOKUP(I37,OFFSET('ML Tables'!$AP$2,MATCH(OV!AC8,'ML Tables'!$AP$2:$AP$15,0)-1,1,COUNTIF('ML Tables'!$AP$2:$AP$15,OV!AC8),3),2,1))))

Thanks,
Phil

 

[mancemonster]

If C37=0 put blank ""
If Not...

Does OV!AC8=5 If Yes put Contract
If not

Does NRen="New" and is I37 larger than..... (OV!AC8&OV!T8 merges 2 cells values together I.E. Cell AC8 has value 1 Cell T8 has value 2 AC8&T8 = 12). The Vlookup then goes to 'ML Tables'!$AJ$3:$AN$26 to find AC8&T8.

It Returns either the 4th or 5th column value of this range (5th if OV!Z8<0.85, 4th otherwise)

If this value is larger than I37 and Nren="New" then it Returns "Before"
If not...

Look for I37

Starting cell is 'ML Tables'!$AP$2, but moved (MATCH(OV!AC8,'ML Tables'!$AP$2:$AP$15,0)-1) Rows up (If -tve) or down (If +tive), and 1 column right.

Then looking in an area (COUNTIF('ML Tables'!$AP$2:$AP$15,OV!AC8)) High to 3 columns wide.

Returning the third value...

Sorry this gets a bit messy towards the halfway - end of my explenation...


Hope this helps..

 

[milwphil]

Thank you! This helps a ton!!