Microsoft Office Tutorials and References

In Depth Information

**Important Tools for Any Business**

Figure 2.29

Create custom functions

for your company to

manage rates on

equipment or sales prices.

Figure 2.30

Custom functions

using your company’s

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,””)))))))