nesting in array formula

[Desprateforhelp]

I have a database of about 70 records, it looks like this:

Job Material Labor Total Billed Profit %
Name Cost Cost Cost For

Solomon 14,000 12,000 26,000 28,000 2,000 7.1%

I am doing a stats sheet on this database and have made
7 different categories according to the Billed For column
(>50,000;40,000<Billed For<50,000; ect.) I need to be
able to do this: look at the only the profitable jobs(or
unprofitable) in a certain category and get the average
in a certain field (profit, or material cost, or labor
cost). A couple days ago i was given this array formula:

=AVERAGE(IF((E1:E100>40000)*(E1:E100<50000),F1:F100))

it works except that i need a way to differentiate
between the profitable jobs and the unprofitable jobs. I
have tried nesting another IF function but can't get it
to work.
Thanks for your help.

 

[Frank Kabel]

Hi
try
=AVERAGE(IF((E1:E100>40000)*(E1:E100<50000)
(F1:F100>0),F1:F100))

 

[Nolongerdesprate]

It works great, Thanks Frank
Michael

-----Original Message-----
Hi
try
=AVERAGE(IF((E1:E100>40000)*(E1:E100<50000)
(F1:F100>0),F1:F100))

.