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.

=IF(A2<>"South Bank",IF(C2<>"Secondary",DAYS360(F8,H8),"-"))

<tbody>
</tbody>

Then I've tried this for #2 above.

=IF(A2<>"South Bank",IF(C2<>"Secondary",IF(B2<>"Green",DAYS360(F8,H8),"-")))

<tbody>
</tbody>

Here's the example - hope this makes sense...and thanks in advance for any help...apologies if it's really simple too.

BANK	REASON	TYPE	Start Date	Finish Date
ABC Bank	Green	Primary	1/1/2015	1/28/2015
123 Bank	Yellow	Primary	1/5/2015	1/8/2015
Victory Bank	Green	Secondary	12/15/2014	12/31/2014
South Bank	Green	Secondary	1/19/2015	2/20/2015
First National Bank	Blue	Primary	12/1/2014	2/12/2015
South Bank	Brown	Primary	12/19/2014	1/4/2015
South Bank	Red	Secondary	1/1/2015	2/1/2015
Garden Bank	Green	Primary	2/1/2015	2/2/2015

<tbody>
</tbody>

 

[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
1	BANK	REASON	TYPE	Start Date	Finish Date			CRITERIA
2	ABC Bank	Green	Primary	01-01-2015	28-01-2015	27		South Bank
3	123 Bank	Yellow	Primary	05-01-2015	08-01-2015	3		Secondary
4	Victory Bank	Green	Secondary	15-12-2014	31-12-2015	381		Green
5	South Bank	Green	Secondary	19-01-2015	20-02-2015	32
6	First National Bank	Blue	Primary	01-12-2014	12-02-2015	73
7	South Bank	Brown	Primary	19-12-2014	04-01-2015	16			26
8	South Bank	Red	Secondary	01-01-2015	01-02-2015	31			38
9	Garden Bank	Green	Primary	01-02-2015	02-02-2015	1

<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>
</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!

BANK	REASON	TYPE	Start Date	Finish Date	# Days To Close (ALL)	# Days To Close (ONLY South Bank - Excluding Secondary)	# Days To Close (NOT South Bank - Excluding Green OR Secondary)
ABC Bank	Green	Primary	1/1/2015	1/28/2015	27	N/A	N/A
123 Bank	Yellow	Primary	1/5/2015	1/8/2015	3	N/A	3
Victory Bank	Green	Secondary	12/15/2014	12/31/2014	16	N/A	N/A
South Bank	Green	Secondary	1/19/2015	2/20/2015	31	N/A	N/A
First National Bank	Blue	Primary	12/1/2014	2/12/2015	71	N/A	71
South Bank	Brown	Primary	12/19/2014	1/4/2015	15	15	N/A
South Bank	Red	Secondary	1/1/2015	2/1/2015	30	N/A	N/A
Garden Bank	Green	Primary	2/1/2015	2/2/2015	1	N/A	N/A
				TOTAL AVERAGE	24.25	15	37
* # Days to Close (ALL) in Column Q uses formula: DAYS360(O2,P2)
* Need to know formula for R2:R9 to come up with those values, for each row.
* Need to know formula for S2:S9 to come up with those values, for each row.
* Nested IF statement but using DAYS360 for formula, if true.

<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>
</tbody>

 

[ljdmitchell]

SOLVED! Thank you!