Help Understanding Nested IF Statements in Attached

1. Help Understanding Nested IF Statements in Attached

Hello,

I need help understanding the nested IF Statement below in the attached XLS documents. Can anyone advise?

=IF(\$E3=0,0,IF(IF(((Z\$2-\$A3)>=365),IF((((Z\$2-\$A3)-365)<=(Z\$2-Y\$2)),((140/12)*(((Z\$2-\$A3)-365)/(Z\$2-Y\$2))),IF(((Z\$2-\$A3)>=1096),((140/12)*((1096-(Y\$2-\$A3))/((Z\$2-Y\$2)+1))),(140/12))),IF((Y\$2<\$A3),IF((\$A3<=Z\$2),(\$E3-280),0),0))<0,0,IF(((Z\$2-\$A3)>=365),IF((((Z\$2-\$A3)-365)<=(Z\$2-Y\$2)),((140/12)*(((Z\$2-\$A3)-365)/(Z\$2-Y\$2))),IF(((Z\$2-\$A3)>=1096),((140/12)*((1096-(Y\$2-\$A3))/((Z\$2-Y\$2)+1))),(140/12))),IF((Y\$2<\$A3),IF((\$A3<=Z\$2),(\$E3-280),0),0))*\$B3))

2. Re: Help Understanding Nested IF Statements in Attached

Hi,

The nested IFS are to determine the correct aging and place a revenue amount deducted with 260,- per item in the correct month; next part of the formula will take the "amount of revenue check" and amortize it over a period.

3. Re: Help Understanding Nested IF Statements in Attached

If the formula is not performing as expected then an explanation of what you need it to do would make it easier for us to help you with it.

That formula is badly written with a lot of unnecessary repeated calculations. While not impossible, trying to follow it accurately is no easy task.

Also, the formula as based on 30 day months, which is why there is a discrepancy between the figures in columns D and F.

edit:-

More accurate version, if that is what you need.

Enter the formula into Y3 then copy right and down to fill the table.

=CHOOSE(MATCH(COLUMNS(\$Y\$2:Y\$2)-MATCH(EOMONTH(\$A3,0),\$Y\$2:\$BP\$2),{-1000,0,1,12,13,36,37}),0,(\$E3-280)*\$B3,0,(1-(DAY(\$A3)/DAY(Y\$2)))*(280/24)*\$B3,(280/24)*\$B3,(DAY(\$A3)/DAY(Y\$2))*(280/24)*\$B3,0)

I had extended the dates in the top row while testing the formula, the range shown in red above should be adjusted to cover all of the dates in the top row of your actual data.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1