Advanced nested if

[TBRoberts]

Hello all and thanks in advance for the help.

Hopefully some of you more seasoned excel users can give some suggestions for this one:


[TABLE="width: 100"]
<tbody>[TR]
[TD]DATE
[/TD]
[TD]AMT
[/TD]
[/TR]
[TR]
[TD]2/25/16
[/TD]
[TD]$50
[/TD]
[/TR]
[TR]
[TD]3/4/16
[/TD]
[TD]$100
[/TD]
[/TR]
[TR]
[TD]3/7/16
[/TD]
[TD]$300
[/TD]
[/TR]
[TR]
[TD]3/16/16
[/TD]
[TD]$500
[/TD]
[/TR]
[TR]
[TD]3/21/16
[/TD]
[TD]$300
[/TD]
[/TR]
[TR]
[TD]3/21/16
[/TD]
[TD]$300
[/TD]
[/TR]
[TR]
[TD]3/23/16
[/TD]
[TD]$300
[/TD]
[/TR]
</tbody>[/TABLE]


I need to evaluate this data and return true if the following criteria is met:

If the transaction occurs on a single, consecutive or alternating business day = TRUE, then sum the totals of the transactions that meet the criteria. If the sum is => $500, then return TRUE. For example, the transactions that occur on 3/4 and 3/7 should return true for consecutive business days given that they are Friday-Monday transactions. Since this is TRUE, then I would like to determine if the sum of the combined transactions is => $500. The transaction on 2/25 should return FALSE as this transaction does not aggregate over $500 on single, consecutive, or alternating business days. Any suggestions with the use of VBA or a formula would be nice but the end result will be coded in VBA as a macro. However, I would like to avoid performing a big loop as the data that I am working from can easily consist of thousands of transactions.

Thanks again for all of the suggestions, and please let me know if you need any further detail.

JR

 

[borkarrr]

Hi im unable to understand alternating buisness days--
do you mean monday wednesday friday?

and btw do you exactly want buisness days ( coz its gonna get a bit complicated if your working week is only from monday to friday, as it will result in additional formula coming in the nest)

 

[TBRoberts]

Yes, Monday - Friday business days (=Weekday,2).

Single business day transactions = Multiple transactions conducted on a single business day
Consecutive business day transactions = Transactions conducted on consecutive business days (Mon, Tues / Tues, Wed / Wed, Thur / Thurs, Fri / Fri, Mon)
Alternating business day transactions = Mon, Wed / Tues, Thurs / Wed, Fri / Thurs, Mon / Fri, Tues

I am assuming that the best way to approach this is to determine the difference in dates by use of the ABS formula, which would also be coupled with the weekday formula to determine the count of the weekday. Then all of this would have to be wrapped up into a sickening =IF(AND(ABS formula to determine if each value is a single, consecutive, or alternating business day as described above.

I am assuming that the formula would look something like the one below. However, I still have a ways to go to account for all of the possible outcomes.

*where E is Date and B is day of the week*

=IF(ABS(E2-E3)=0,"0",IF(ABS(E3-E5)=0,"0",IF(E2=E3,"0",IF(ABS(E2-E3)=1,"1",IF(ABS(E2-E3)=2,"2",IF(AND(ABS(E2-E3)=3,B2="FRI"),"1",IF(AND(ABS(E2-E3)=3,B2<>"FRI"),"3")))))))

I hope this helps, lmk if you need any further details.

 

[TBRoberts]

^^FYI^^

This formula returns the difference in days from the first transaction to the next. I can then use the difference to determine a if the transactions meet my specified criteria. For example, if the difference in days from the first transaction to the next is a 0, 1 or 2 then the condition is satisfied. (0 = same day transaction, 1 = consecutive business day transaction, 2 = alternating business day transaction).

I am very close, but i have a feeling that there are a thousand easier solutions.

 

[thisoldman]

Use the NETWORKDAYS function: =NETWORKDAYS(Start_date, End_Date, Optional_Holidays)

This function will give you the difference in days between two dates, excluding Saturday and Sunday, plus one. If you have entered holiday dates in a range of cells, NETWORKDAYS can also exclude them when calculating the days difference. Two identical dates return a result of 1, immediately consecutive dates return a result of 2, and so on.

The NETWORKDAYS between March 4, 2016 and March 4, 2016, is 1; between March 4, 2016, and March 7, 2016 is 2.

Excel 2012
	A	B	C	D	E	F	G
1	DATE		NETWORKDAYS		w/ Holidays		Holidays
2	02/25/2016						03/17/2016
3	03/04/2016		7		7		03/18/2016
4	03/07/2016		2		2
5	03/16/2016		8		8
6	03/21/2016		4		2
7	03/21/2016		1		1
8	03/23/2016		3		3
Sheet1

Cell Formulas
Range	Formula
C2	=IFERROR(NETWORKDAYS(A1, A2), "")
E2	=IFERROR(NETWORKDAYS(A1, A2, $G$2:$G$3), "")

 

[TBRoberts]

Thank you!