If criteria met, then determine the average time
Hi, I'm trying to sort column G for any "Approved" or "Denied" outcomes then find the time difference between columns E and F then divide that difference by the number of approved or denied in column G. I've tried a number of combinations of SUMIFS, IFS, COUNTIFS, and Ave formulas but can't come up with one that works. Can you help? Here's an example of what I'm trying to do:
Sum E-F if G= "Approved" or "Denied" / the number of "Approved" or "Denied" outc
Try
=SUMPRODUCT(--(G2:G7="Approved"),(F2:F7-E2:E7))/COUNTIF(G2:G7,"Approved")
format as [h]:mm
Change approved to rejected for likewise results
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks Ace_XL, for some reason I get 12.37 instead of 12.625 as a result. Is it possible to create one formula that adds all approved and denied together rather than separately? And may I ask what the (-- means?
Appreciate your help!
Hey, looks like I get 12:37 now so that looks right! Now if I can get both the approved and denied into one answer that would be great!
My best
12.37 indicates 12 hours and 37 minutes since its formatted as h:mm which equates to 12.625
If you want to return 12.625, use
=SUMPRODUCT(--(G2:G7="Approved"),(F2:F7-E2:E7))/COUNTIF(G2:G7,"Approved")*24
For the -- see here..
http://www.xldynamic.com/source/xld....ml#performance
For both approved and denied use..
=SUMPRODUCT(--(G2:G7="Approved")+(G2:G7="Denied"),(F2:F7-E2:E7))/SUM(COUNTIF(G2:G7,{"Approved","Denied"}))
Yahoo! It works, thanks so much as I've been working on this for hoursDo you mind if I ask why the final text portion of the formula needs to be in {}...what does this mean?
Thanks again!
You can list more than one criterion (as text) when you wrap a COUNTIF or SUMIF formula in SUM(). This creates an array and serves as a sum of COUNT of crietrion 1 or criterion 2.Do you mind if I ask why the final text portion of the formula needs to be in {}...what does this mean?
Ahhh, I will be using that a lot for this workbook, this is great! And thanks for link to explain how the (-- works at (http://www.xldynamic.com/source/xld....ml#performance
). Makes sense now and there's a lot of other useful information there!!
My best to you
Looks like I still have a problem. While the above formula works in my example, it doesn't take into account that the actual spreadsheet I'm using has blank cells for column G which is the diviser. This gives me a result of #DIV/0! I'm working with rows G2-G1000 and the rows are not always populated so somehow, I must account for the blank cells. Any suggestions?
this suggests that the divisor in the formula is zero i.e. there are no approved/denied entries in column G.This gives me a result of #DIV/0! I'm working with rows G2-G1000 and the rows are not always populated so somehow, I must account for the blank cells.
to remove the error use..
=IFERROR(SUMPRODUCT(--(G2:G7="Approved")+(G2:G7="Denied"),(F2:F7-E2:E7))/SUM(COUNTIF(G2:G7,{"Approved","Denied"})),0)
Now I get a response of 00:00 instead of the amount of actual time.
that is because there are no approved/denied entries, isnt it?Originally Posted by Verdant
if you want to return a blank cell in such a case replace the 0 at the end in the formula (in post#10) with ""
My apologies, I made a rookie mistake, I forgot that my response was on one sheet and I'm pulling the information from another. I've corrected the formula to:
=IFERROR(SUMPRODUCT(--('Detail Information'!G2:G1000="Approved")+('Detail Information'!G2:G1000="Denied"),('Detail Information'!F2:F1000-'Detail Information'!E2:E1000))/SUM(COUNTIF('Detail Information'!G2:G1000,{"Approved","Denied"})),0).
This works! Thanks for sticking with me!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks