Nested IF statements/single formula needed conditional on three separate values - HELP! :)

[ljdmitchell]

I've tried to look at every example of multiple/nested IF statements to see if I could figure this out - as I know it's staring me right in the face. I've tried AND, OR - and just can't get it to work. Here's the basic issue - simply trying to find out the average number of days that a loan takes to close - based on whether it's a specific bank or not, and then filter out two other possible fields to narrow it down further - taking out one or the other, the loan type and loan reason. I've created a mock example below - can you please help?

1. Need formula for the average # of days to close IF BANK NOT EQUAL to South Bank....and excluding TYPE=Secondary.
2. Need formula for the average # of days to close IF BANK NOT EQUAL to South Bank...and exclude IF TYPE=Secondary and/or IF REASON=GREEN.
3. Need any false values to either be N/a or "-".
4. F Column would be value/formula for #1 example above.
5. G Column would be value/formula for #2 example above.

I've tried nested IF statements, with DAYS360 to calculate...with this for #1 above.
[TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl67, width: 83, bgcolor: #FFFF99"]=IF(A2<>"South Bank",IF(C2<>"Secondary",DAYS360(F8,H8),"-"))
[/TD]
[/TR]
</tbody>[/TABLE]


Then I've tried this for #2 above.
[TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl67, width: 102, bgcolor: #FFFF99"]=IF(A2<>"South Bank",IF(C2<>"Secondary",IF(B2<>"Green",DAYS360(F8,H8),"-")))
[/TD]
[/TR]
</tbody>[/TABLE]

Here's the example - hope this makes sense...and thanks in advance for any help...apologies if it's really simple too.
[TABLE="width: 304"]
<tbody>[TR]
[TD="class: xl67, width: 109, bgcolor: #FFFF99"]BANK
[/TD]
[TD="class: xl67, width: 85, bgcolor: #FFFF99"]REASON
[/TD]
[TD="class: xl68, width: 64, bgcolor: #FFFF99"]TYPE
[/TD]
[TD="class: xl67, width: 73, bgcolor: #FFFF99"]Start Date
[/TD]
[TD="class: xl67, width: 73, bgcolor: #FFFF99"]Finish Date
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]ABC Bank
[/TD]
[TD="class: xl63, bgcolor: transparent"]Green
[/TD]
[TD="class: xl64, bgcolor: transparent"]Primary
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/1/2015
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/28/2015
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]123 Bank
[/TD]
[TD="class: xl63, bgcolor: transparent"]Yellow
[/TD]
[TD="class: xl64, bgcolor: transparent"]Primary
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/5/2015
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/8/2015
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Victory Bank
[/TD]
[TD="class: xl66, bgcolor: transparent"]Green
[/TD]
[TD="class: xl64, bgcolor: transparent"]Secondary
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12/15/2014
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12/31/2014
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]South Bank
[/TD]
[TD="class: xl66, bgcolor: transparent"]Green
[/TD]
[TD="class: xl64, bgcolor: transparent"]Secondary
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/19/2015
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2/20/2015
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]First National Bank
[/TD]
[TD="class: xl63, bgcolor: transparent"]Blue
[/TD]
[TD="class: xl64, bgcolor: transparent"]Primary
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12/1/2014
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2/12/2015
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]South Bank
[/TD]
[TD="class: xl63, bgcolor: transparent"]Brown
[/TD]
[TD="class: xl64, bgcolor: transparent"]Primary
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12/19/2014
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/4/2015
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]South Bank
[/TD]
[TD="class: xl63, bgcolor: transparent"]Red
[/TD]
[TD="class: xl64, bgcolor: transparent"]Secondary
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1/1/2015
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2/1/2015
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Garden Bank
[/TD]
[TD="class: xl63, bgcolor: transparent"]Green
[/TD]
[TD="class: xl64, bgcolor: transparent"]Primary
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2/1/2015
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2/2/2015
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SteveO59L]

have you considered averageifs?

 

[Caribeiro77]

Hi ljdmitchell, i'm not shure if this is what you want, even if it is i'm also shure that someone arround here has a much elegant way of doing it but this is what i came up with:

<title>Excel Jeanie HTML</title>


<!-- ######### Start Erzeugter Html Code zum Kopieren ########## -->


Sheet1

	A	B	C	D	E	F	G	H	I
BANK	REASON	TYPE	Start Date	Finish Date			CRITERIA
ABC Bank	Green	Primary		South Bank
123 Bank	Yellow	Primary		Secondary
Victory Bank	Green	Secondary		Green
South Bank	Green	Secondary
First National Bank	Blue	Primary
South Bank	Brown	Primary
South Bank	Red	Secondary
Garden Bank	Green	Primary

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:125px;"><col style="width:65px;"><col style="width:71px;"><col style="width:75px;"><col style="width:76px;"><col style="width:65px;"><col style="width:65px;"><col style="width:73px;"><col style="width:73px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]01-01-2015[/TD]
[TD="align: right"]28-01-2015[/TD]
[TD="align: right"]27[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]05-01-2015[/TD]
[TD="align: right"]08-01-2015[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]15-12-2014[/TD]
[TD="align: right"]31-12-2015[/TD]
[TD="align: right"]381[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]19-01-2015[/TD]
[TD="align: right"]20-02-2015[/TD]
[TD="align: right"]32[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]01-12-2014[/TD]
[TD="align: right"]12-02-2015[/TD]
[TD="align: right"]73[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]19-12-2014[/TD]
[TD="align: right"]04-01-2015[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]26[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]01-01-2015[/TD]
[TD="align: right"]01-02-2015[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]38[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]01-02-2015[/TD]
[TD="align: right"]02-02-2015[/TD]
[TD="align: right"]1[/TD]

</tbody>

Formulas

Cell Formula I7 =SUMPRODUCT(--(A2:A9<>H2),--(C2:C9<>H3),E2:E9-D2:D9)/COUNTIFS(A2:A9,"<>"&H2,C2:C9,"<>"&H3) I8 =SUMPRODUCT(--(A2:A9<>H2),--(C2:C9<>H3),--(B2:B9<>H4),E2:E9-D2:D9)/COUNTIFS(A2:A9,"<>"&H2,C2:C9,"<>"&H3,B2:B9,"<>"&H4) <tbody> </tbody>

<tbody>
</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8






<!-- ######### Ende Erzeugter Html Code zum Kopieren ########## -->

 

[ljdmitchell]

Thanks SteveO59L and Caribeiro77 for the responses....much appreciated. On the AVERAGEIFS - yes, but I need it by row for each line....which I didn't explain very well. Same thing Caribeiro77 - that's the result I want, however I want each row to have the value, if it meets the criteria. I've edited my example to show the two additional columns of data I'm looking for, where I need the formulas by row to give me the value, if applicable and it meets the critera...or come back with a N/A....so the total average doesn't get skewed. I entered "numbers" in the two new columns to show the result of what the formula would come back with...just need help figuring out what formula will calculate the number of days, based on if it doesn't equal a specific bank - and then if it meets one or both of the other two conditions - not green and not primary.

See if this new edited example makes more sense. Definitely appreciate your input!

[TABLE="width: 469"]
<colgroup><col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;" width="109"> <col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;" width="85"> <col style="width: 48pt;" width="64"> <col style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" span="2" width="73"> <col style="width: 48pt;" width="64"> <col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;" width="85"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;" width="70"> <tbody>[TR]
[TD="class: xl68, width: 109, bgcolor: #FFFF99"]BANK[/TD]
[TD="class: xl68, width: 85, bgcolor: #FFFF99"]REASON[/TD]
[TD="class: xl69, width: 64, bgcolor: #FFFF99"]TYPE[/TD]
[TD="class: xl68, width: 73, bgcolor: #FFFF99"]Start Date[/TD]
[TD="class: xl68, width: 73, bgcolor: #FFFF99"]Finish Date[/TD]
[TD="class: xl78, width: 64, bgcolor: #FFFF99"]# Days To Close (ALL)[/TD]
[TD="class: xl78, width: 85, bgcolor: #FFFF99"]# Days To Close (ONLY South Bank - Excluding Secondary)[/TD]
[TD="class: xl78, width: 70, bgcolor: #FFFF99"]# Days To Close (NOT South Bank - Excluding Green OR Secondary)[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]ABC Bank[/TD]
[TD="class: xl64, bgcolor: transparent"]Green[/TD]
[TD="class: xl65, bgcolor: transparent"]Primary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/1/2015[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/28/2015[/TD]
[TD="class: xl72, bgcolor: transparent"]27[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]123 Bank[/TD]
[TD="class: xl64, bgcolor: transparent"]Yellow[/TD]
[TD="class: xl65, bgcolor: transparent"]Primary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/5/2015[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/8/2015[/TD]
[TD="class: xl72, bgcolor: transparent"]3[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Victory Bank[/TD]
[TD="class: xl67, bgcolor: transparent"]Green[/TD]
[TD="class: xl65, bgcolor: transparent"]Secondary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/15/2014[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/31/2014[/TD]
[TD="class: xl72, bgcolor: transparent"]16[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]South Bank[/TD]
[TD="class: xl67, bgcolor: transparent"]Green[/TD]
[TD="class: xl65, bgcolor: transparent"]Secondary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/19/2015[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2/20/2015[/TD]
[TD="class: xl72, bgcolor: transparent"]31[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]First National Bank[/TD]
[TD="class: xl64, bgcolor: transparent"]Blue[/TD]
[TD="class: xl65, bgcolor: transparent"]Primary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/1/2014[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2/12/2015[/TD]
[TD="class: xl72, bgcolor: transparent"]71[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]71[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]South Bank[/TD]
[TD="class: xl64, bgcolor: transparent"]Brown[/TD]
[TD="class: xl65, bgcolor: transparent"]Primary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/19/2014[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/4/2015[/TD]
[TD="class: xl72, bgcolor: transparent"]15[/TD]
[TD="class: xl72, bgcolor: transparent"]15[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]South Bank[/TD]
[TD="class: xl64, bgcolor: transparent"]Red[/TD]
[TD="class: xl65, bgcolor: transparent"]Secondary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/1/2015[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2/1/2015[/TD]
[TD="class: xl72, bgcolor: transparent"]30[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Garden Bank[/TD]
[TD="class: xl64, bgcolor: transparent"]Green[/TD]
[TD="class: xl65, bgcolor: transparent"]Primary[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2/1/2015[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2/2/2015[/TD]
[TD="class: xl72, bgcolor: transparent"]1[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[TD="class: xl72, bgcolor: transparent"]N/A[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl76, width: 73, bgcolor: transparent"]TOTAL AVERAGE[/TD]
[TD="class: xl75, bgcolor: transparent"]24.25[/TD]
[TD="class: xl77, bgcolor: transparent"]15[/TD]
[TD="class: xl75, bgcolor: transparent"]37[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent, colspan: 5"]* # Days to Close (ALL) in Column Q uses formula: DAYS360(O2,P2)[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 6"]* Need to know formula for R2:R9 to come up with those values, for each row.[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 6"]* Need to know formula for S2:S9 to come up with those values, for each row.[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 5"]* Nested IF statement but using DAYS360 for formula, if true. [/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[ljdmitchell]

SOLVED! Thank you!