ljdmitchell
New Member
- Joined
- Feb 25, 2015
- Messages
- 5
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.
<tbody>
</tbody>
Then I've tried this for #2 above.
<tbody>
</tbody>
Here's the example - hope this makes sense...and thanks in advance for any help...apologies if it's really simple too.
<tbody>
</tbody>
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>