Breaking down a code......

Hello everyone,

This might seem abstract and tedious but could anyone help be breakdown this code into english? I need to understand what each bit of the code means.....any help would be great!

=IF(YEAR($AE9)<(BX$6+1),0,IF($F9<=BX$6,OFFSET(Projects!$B$2,MATCH($E9,Projects!$B$3:$B$54,0),BX$6-2005)*$AG9*$AH9,IF($F9=BX$6+1,(OFFSET(Projects!$B$2,MATCH($E9,Projects!$B$3:$B$54,0),BX$6-2005)*$AG9*$AH9)/2,0)))

I understand about 30% of it but i get lost easily. A lot of it is referencing and that complicates it even more.

Peter

Peter

See if this helps

=IF(YEAR($AE9)<(BX$6+1) – Year extracts the year component of a date. So if the year is < the value of BX6+1 then put in a result of
,0,

IF($F9<=BX$6, - if F9 < BX6

OFFSET(Projects!$B$2, - from the starting point of Projects!$B$2

MATCH($E9,Projects!$B$3:$B$54,0), go down the number of rows equal to the position of the value in E9 in the range Projects!$B$3:$B$54

BX$6-2005) – and across the number of columns returned by BX6 – 2005

*$AG9*$AH9 – multiply that result by these 2 numbers

IF($F9=BX$6+1 – if F9 = BX6 + 1

,(OFFSET(Projects!$B$2 – from the starting point of Projects!$B$2

,MATCH($E9,Projects!$B$3:$B$54,0) – go down the number of rows equal to the position of the value in E9 in the range ,Projects!$B$3:$B$54

,BX$6-2005) – and across the number of columns returned by BX6-2005
*$AG9*$AH9)/2 – and perform this maths

,0))) – when all else fails, give a 0.

One thing you can do, is go to the cell with the formula and press F2. This puts you into the formula edit mode. You can then highlight parts of the formula (say MATCH($E9,Projects!$B$3:$B$54,0)) and press F9. This will evaluate that part of the formula and you can see what result it returns. Sometimes this can help you to understand what the formula is doing / returning. Make sure you press ESC to finish, or the evaluated components will be embedded in the formula.

HTH

rylo

Thanks very much for the explanation rylo! That helps me alot!