Microsoft Office Tutorials and References
In Depth Information
Figure 2.29
Create custom functions
manage rates on
equipment or sales prices.
Figure 2.30
Custom functions
rates can provide
accuracy throughout
the company.
Producing a Line Item Milestone Management Chart
On many occasions you’ll have to view multiple events on the same timeline. This can be achieved
via one simple IF AND statement nested several times. Notice the example in Figure 2.31, the
original formula that would return one result for a date is: =IF(AND(X\$7>=\$B9,X\$7<\$C9),\$P9,””)) .
However, what if you need several results on the same line item? All you have to do is nest the
function and refer the start and stop dates to a table of dates. The nested function begins after the
first return results \$P9. It is
=IF(AND(X\$7>=\$B9,X\$7<\$C9),\$P9,IF(AND(X\$7>=\$D9,X\$7<\$E9),\$Q9,””)))
All seven nested functions would be
=IF(AND(X\$7>=\$B9,X\$7<\$C9),\$P9,IF(AND(X\$7>=\$D9,X\$7<\$E9),\$Q9,
IF(AND(X\$7>=\$F9,X\$7<\$G9),\$R9,IF(AND(X\$7>=\$H9,X\$7=\$J9,X\$7<\$K9),\$T9,IF(AND(X\$7>=\$
L9,X\$7<\$M9),\$U9,
IF(AND(X\$7>=\$N9,X\$7<\$O9),\$V9,””)))))))
Search JabSto ::

Custom Search