find the mins between two times in to different columns AND only if they meet the criteria of a third column

[Timothy.w.Hayes]

I need a formula that will find the mins between two times in to different columns AND only if they meet the criteria of a third column. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
In Column C, I have Different types of Changes to my environment. Emergency, Standard, ect… . In Column A I have the start time of that the Change in that row. In Column B I have the time that the change ended. I need to find the Average time for all the Emergency changes in one cell and an average time for all Standard Changes in another cell. <o></o>
Some of the Start times are days apart and some are only mins apart. I have a couple that the start and end times are over a week apart. <o></o>
Any Ideas? <o></o>

 

[Scott Huish]

I'm assuming you mean minutes (mins) not minimum like the MIN function.

=AVERAGE(IF(C1:C100="Emergency",IF(ISNUMBER((B1:B100-A1:A100)),(B1:B100-A1:A100)*1440)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[Timothy.w.Hayes]

HotPepper... I think you got it! Thank you VERY much... this one had me stumbed for a couple of days. Thank you.

 

[Timothy.w.Hayes]

Below formula works Great ... but I need to add one more thing. In another column I have different Software. Below I have it shown in Column A. My Syntax is all wrong.

This works...
=AVERAGE(IF(CRs!K3:K76="Emergency"),IF(ISNUMBER((CRs!H3:H76-CRs!G3:G76)),(CRs!H3:H76-CRs!G3:G76)*1440)))

Now I need to add a second qualifyer. (CRs!A3:A76,"Software Name")),

=AVERAGE(IF(CRs!K3:K76="Emergency")and(CRs!A3:A76,"Software Name")),IF(ISNUMBER((CRs!H3:H76-CRs!G3:G76)),(CRs!H3:H76-CRs!G3:G76)*1440)))

How should that read?

 

[Scott Huish]

=AVERAGE(IF(D1:D100="Emergency",IF(A1:A100="Software Name",IF(ISNUMBER((C1:C100-B1:B100)),(C1:C100-B1:B100)*1440))))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[Timothy.w.Hayes]

I am getting a #VALUE! message. I have verified the spelling Of the searched entry and the range of the search and the Name of the sheet.
Checked with Ctrl+Shift+Enter.

Any Ideas?

 

[Timothy.w.Hayes]

Entry reads....

=AVERAGE(IF(CRs!K3:K76="Emergency",IF(CRs!A3:A76,"Software name",IF(ISNUMBER((CRs!H3:H76-CRs!G3:G76)),(CRs!H3:H76-CRs!G3:G76)*1440))))

I know we are close...

 

[Timothy.w.Hayes]

=AVERAGE(IF(CRs!K3:K76="Emergency",IF(CRs!A3:A76="Software Name",IF(ISNUMBER((CRs!H3:H76-CRs!G3:G76)),(CRs!H3:H76-CRs!G3:G76)*1440))))

It was an "=" instead of a "," that was needed!

Thank you so much for your help. You have helped more than you will EVER know.

 

[Scott Huish]

Glad to have helped!