# Urgent Excel quiery for Phd project!!!

#### arnolju

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
ABCDEF
1Amount USDConctAmount useNo.attemptsConct overall
2
335START1
4
5
6400
7
8
9100END117531
10
11
12200START1
13
14500
15
16
171000
18
19
20250END160041
Sheet4

query from PHd student - THANK YOU.

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

Replies
14
Views
716
Replies
1
Views
349
Replies
2
Views
460
Replies
2
Views
241
Replies
9
Views
717

1,203,069
Messages
6,053,347
Members
444,654
Latest member
Rich Cohen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back