DAVERAGE isn't working

[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). I have tried using DAVERAGE, and it always
returns #VALUE! Need help, thanks

 

[Frank Kabel]

Hi
for which column do you want to get the average for?. You may try
something like the following array formula (to be entered with
CTRL+sHIFT+ENTER):
=AVERAGE(IF((E1:E100>40000)*(E1:E100<50000),F1:F100))

 

[desprateforhelp]

That array formula returns #Name! I for got to mention
that is the database i have left a column between ever
column, eg. Billed For isn't column E it is column H, (So
it isn't so crowded), should i get rid of the "spacer
columns"?Thanks

 

[Frank Kabel]

Hi
what is the exact formula you have used?. This should not return a
#NAME error (or are you using a non-English Excel version)

 

[Myrna Larson]

You will make your formula writing MUCH simpler if you get rid of the blank
columns. Just make them wider if the purpose is cosmetic.