Hello...I'm stuck again!
I have a spreadsheet with target dates for task completion in column AH and actual completion dates in column AL. I need to count how many times the actual date (in AL) was less than or equal to the target date(in AH) and present this as a percentage to demonstrate what percentage of the time we hit target. To make this even more complex, Column V lists which department the task belonged to and I ideally need to be able to just perform the above calculation for those entries where column V = "Factory Quality"
Thank you very much for your help.
If it makes it easier, it would be really useful to have a solution to the first part of the query, ignoring the conditions relating to column V....
Assuming your data is in rows 2:100 try =COUNTIFS(V2:V100,"Factory Quality",AH2:AH100,"<=" & AL2:AL100)/COUNTIF(V2:V100,"Factory Quality")
Hello,
what would make it easier is a sample sheet with the expected result.
Try this formula (adjust ranges to suit)
=SUMPRODUCT(--(AL2:AL11<=AH2:AH11),--(V2:V11="Factory Quality"))/COUNTIF(V2:V11,"Factory Quality")
Last edited by teylyn; 10-25-2011 at 06:12 AM.
This "array formula" will give you the percentage of dates in AL <= dates in AH for all rows that have dates in AL and have "Factory Quality" in column V - I assumed data from row 2 to row 1000, change as required
=AVERAGE(IF(V2:V1000="Factory Quality",IF(ISNUMBER(AL2:AL1000),IF(AL2:AL1000<=AH2:AH1000,1,0))))
confirmed with CTRL+SHIFT+ENTER
Audere est facere
Andrew, this bit:
=COUNTIFS(V2:V100,"Factory Quality",AH2:AH100,"<=" & AL2:AL100)
always returns 0
That is FANTASTIC! Thank you so much. It works!.....the reply from daddylonglegs, I mean
No need for the SUMPRODUCT voodoo if the OP is using Excel 2007, Teylyn
Edited to add: Sorry, work Internet connection borked as I posted this. I'm obviously behind the trend.
Not in my test workbook it doesn't.Originally Posted by teylyn
It's actually going to return a 99 element array - element 1 being the result of =COUNTIFS(V2:V100,"Factory Quality",AH2:AH100,"<=" & AL2), element 2 being =COUNTIFS(V2:V100,"Factory Quality",AH2:AH100,"<=" & AL3).....etc......i.e. it's comparing every entry in AH against each entry in AL in turn....
That can be useful in some circumstances but not here where you want to compare each entry in AH2:AH100 with the entry from the same row in AL2:AL100....which is why you need a straight AL2:AL100<=AH2:AH100 within a function other than COUNTIFS.......
Audere est facere
Andrew,
I'll show you mine and you show me yours....Not in my test workbook it doesn't.
It returns 0 in the attached.
I'd like to see a file where it returns something else.
As I said in my last post - the formula actually returns an array. If you put it in a single cell then the value displayed can differ depending on where you put it, as you have it in Z3 it will show the second element of the array, in this case the result of this formula
=COUNTIFS(V2:V100,"Factory Quality",AH2:AH100,"<=" & AL3)
That could, of course, return any value between 0 and 99 depending on the data
If you put the same formula in Z4 it will possibly display a different result.....
Audere est facere
Daddylonglegs is, of course, correct and I obviously haven't got my brain in gear this morning.
Thanks DLL & Teylyn.
In the attached I still have that formula in Z3 - it returns 9 because I changed the data. Same formula in Z4 shows 1 because it's picking up a different value from the array.
In AC3 proof that the formula returns a 99 element array - this formula
=COUNTA(COUNTIFS(V2:V100,"Factory Quality",AH2:AH100,"<=" & AL2:AL100))
"array entered"
returns 99
Audere est facere
Oh my goodness....I don;t understand much of what you're talking about! Please can you confirm what the correct formula is that I should be using? Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks