=mode(if(and help

[chrischilton01]

I am trying to calculate the mode of our turn-around times for cases we handle as follows:

In column N, which contains whole numbers, which I want to obtain the mode values of, if:

• Column A equals the name of a person in my team reference by cell J2
• if the date in column I was greater than 31/12/12 (referenced J1) and less than 01/01/2013 (referenced by K1)

So far I can get the below to work based only on using greater than 31/12/12 and not with the less than option as follows:

{=MODE(IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$A$2:$A$56366=J2,IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$I$2:$I$56366<J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$M$56366)))}

I have tried adding in a AND statement but cannot seem to get this to work, any ideas?

 

[Dreadknight Nasus]

you didn't open the second code bracket, and you need a "/" after you open it. like this [/code]

 

[chrischilton01]

Hi there, I have shared the formula via dropbox, if you look at cell P4 that is the formula I used. Any suggestions welcome.

https://dl.dropboxusercontent.com/u/4159070/example%20(1).xlsx

 

[Dreadknight Nasus]

I'm at work so I'm not able to view that link. Just use the code tags like I described.

 

[chrischilton01]

 =MODE(IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$A$2:$A$56366=J2,IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$I$2:$I$56366<J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$M$56366)))

 

[chrischilton01]

Part 2

 <J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$M$56366)))

<J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$m$56366)))} [="" code]
<J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$m$56366)))} [="" code]
<J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$m$56366)))}< html=""></J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$m$56366)))}<></J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$m$56366)))}></J1,[Jan12_May14_Closed_Cases.xlsx]report1401715985583!$M$2:$m$56366)))}>

 

[Dreadknight Nasus]

 =MODE(IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$A$2:$A$56366=J2,IF([Jan12_May14_Closed_Cases.xlsx]report1401715985583!$I$2:$I$56366

<j1,[jan12_may14_closed_cases.xlsx]report1401715985583!$m$2:$m$56366))) [="" code][="" quote]
This post was correct, but you still didn't paste the entire formula.</j1,[jan12_may14_closed_cases.xlsx]report1401715985583!$m$2:$m$56366)))>

 

[chrischilton01]

{=MODE(IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$A$2:$A$56366=J2,IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$I$2:$I$56366<J1,'N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$M$2:$M$56366)))}

 

[chrischilton01]

{=MODE(IF(Jan12_May14_Closed_Cases.xlsxreport1401715985583'!$A$2:$A$56366=J2,IF(Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$I$2:$I$56366<J1, Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$M$2:$M$56366)))}

 

[Dreadknight Nasus]

I don't understand why you're double posting. You should put your entire line of code between the two code tags.

I just found out a simpler way to post code without using the tags. Paste your line of code into the reply form, highlight the code, and click the # button in the toolbar above the form. This will correctly wrap your code.

 

[chrischilton01]

Done exactly that now...just left out the { and } at the start of the formula before = and at the end, hope it works or I will top myelf.

=MODE(IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$A$2:$A$56366=J2,IF('N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$I$2:$I$56366<J1,'N:\Hotel Contracts\Public\Dynamic Inventory\Chris\Cases\[Jan12_May14_Closed_Cases.xlsx]report1401715985583'!$M$2:$M$56366)))