Urgent Excel quiery for Phd project!!!

[arnolju]

Amount USD Conct Amount use No.attempts Conct overall

35 START 1


40 0



100 END 1 175 3 1








200 START 1

50 0



100
0

250 END 1 600 4 1


This is what the spreadsheet looks like: Column A has amount of intervention. I defined and already automated excel to understand that if there is no more than 5 business days of inactivity between the intervention then this needs to be classified as one event. This way i got "START" and "END" of the intervention in column B. Column C is given data. In column D headed "Amount used" I need excel to sum total amount of intervention used during the whole EVENT from column A. Similarly in column E headed "No. attepts" I need excel to count the number of interventions by the central banks during each event. In the last column i need excel to deiced whether overall intervention was concerted or not (1 for concerted) i.e. if number of "1" is the same or greater than number of "0" then overall intervention is concerted. Basically i need excelt to understand different EVENTS. Empty space between the events is empty cells during which time there was no intervention. This date all comes as time series, i just did not copy the "DATE" column which was originally column A.

If anyone has a solution i will be most grateful!!!

Julia

 

[fairwinds]

Welcome to the board!

Try:

D3: =IF(B3="END",SUM($A$2:A3)-SUM($D$2:D2),"")
E3: =IF(B3="END",COUNT($A$2:A3)-SUM($E$2:E2),"")
F3: =IF(ISNUMBER(E3),IF(COUNTIF(INDEX($C$2:C2,IF(ISNUMBER(MATCH(2,$F$2:F2)),MATCH(2,$F$2:F2),1)):C3,1)>=COUNTIF(INDEX($C$2:C2,IF(ISNUMBER(MATCH(2,$F$2:F2)),MATCH(2,$F$2:F2),1)):C3,0),1,0),"")

All these dragged down as far as needed.

Book1
	A	B	C	D	E	F
1	Amount USD	Conct		Amount use	No.attempts	Conct overall
2
3	35	START	1
4
5
6	40		0
7
8
9	100	END	1	175	3	1
10
11
12	200	START	1
13
14	50		0
15
16
17	100		0
18
19
20	250	END	1	600	4	1
Sheet4

 

[arnolju]

query from PHd student - THANK YOU.

Thank you, thank you, thank you!!! I am extremely grateful to you fairwinds!!! You are a genius!!!