Help with array formulas

  • Thread starter Kathryn J Bittman
  • Start date
K

Kathryn J Bittman

Hello,
I am using some array formulas and need to narrow the outcome.

The first set is for average, minimum and maximum number for days between
service request dates and completion dates.

I need to have the average, minimum and maximum number of days between
service request dates and completion dates that were emergency repairs.
How do I add this criteria to my formula?

Current formulas read:
{=average( if('data entry'!p2:p120)>0,'Data entry'!P2:p120))}
{=MIN( if('data entry'!p2:p120)>0,'Data entry'!P2:p120))}
{=MAX('data entry'!p2:p120)}

All help is appreciated! Thanks in advance.
 
B

Biff

Hi!

How do you denote or record emergency repairs?

Use this as a guide:

A1:A10 = numeric values to avg
B1:B10 = E (Emergency repair)

Array entered:

=AVERAGE(IF((A1:A10>0)*(B1:B10="E"),A1:A10))

Use the same logic for MIN and MAX.

Biff
 
K

Kathryn J Bittman

Thanks for the help. Formula is working for average and minimum, but failing
on the maximum calculation.

Average = 2
Minimum = 0
Max reporting = 0

Ideas?
 
K

Kathryn J Bittman

Emergency repairs are listed F2:F120 with Y or N
I tried for following formulas:

{=max(if(('data entry'!p2:p120>0)*(F2:f120="Y"),'data entry'!p2:p120))}

{=max(if(('data entry'!p2:p120)*(F2:f120="Y"),'data entry'!p2:p120))}

{=max(if((F2:f120="Y"),'data entry'!p2:p120))}

The last one worked. Thanks for helping out...
 
Top