I need help with a probably complex IF statements.
I have data such as:
From this i need to return the results based on quite a few criteria. In the BH column I need the criteria to match this:
If it's due 6 or more days and it's >40% result is red box and a number 0.
If it's due 6 or more days and it's <40%, but the majority (80%) of it is Due >30 days, the result is a red box and a number 0.
If it's due 6 or more days and it's <5% result is a yellow box and a number 10.
If it's not due at all (Not due = 100%) the result is a green box and a number 15.
All the other possibilities is the orange category and the returnable number should be 5.
Edited. My solution:
=IF(BA4=1;15;IF(SUM(BC4:BE4)<0,05;10;IF(SUM(BC4:BE4)>0,4;0;IF(AND(SUM(BC4:BE4)<0,4;BE4>0,8);0;5))))
It seems that I managed to do the formula like this, but is the sequence correct?