Picking Values from variable range

[Elainesmith]

Hello,

I have a problem. From the attachment (same in message body in case attachment is not there) you can see I have a data. This data is for ~10 years. It is daily data. I need to pull week's opening value, week's closing value and maximum and minimum value of the week. Week is from Monday to Sunday. But due to holidays, number of days in a week and first day of the week itself are not consistent. I need to do the same for calendar month, pull out monthly values. How can I do it fast and using a formula?

By using =IF(C7="Friday",IF(C6="Thursday",IF(C5="Wednesday",IF(C4="Tuesday",IF(C3="Monday",1,0))))) I was able to identify full working weeks and copy paste my calculation formula for these weeks. But for balance weeks and months I can't.

Please advice.

Thanks,
Elaine

Day Date Open High Low Close
Monday 3-Nov-08 582.3 624.7 580.05 602.2
Tuesday 4-Nov-08 600 648.9 582.4 640.45
Wednesday 5-Nov-08 651.9 674.8 592.35 604
Thursday 6-Nov-08 590 610.9 552.5 582.7
Friday 7-Nov-08 580 599 547.15 581.6
Monday 10-Nov-08 594.45 613 578.3 608.9
Tuesday 11-Nov-08 600 603 560 564.05
Wednesday 12-Nov-08 559 569.7 523.15 525.3
Friday 14-Nov-08 540 554.4 487 492.95
Monday 17-Nov-08 587.2 587.2 431.3 456.65
Tuesday 18-Nov-08 452 452 425 437.75
Wednesday 19-Nov-08 425 464.3 421.1 427.55
Thursday 20-Nov-08 418 418 394 405.45
Friday 21-Nov-08 419 422.5 383.6 411.5
Monday 24-Nov-08 409 420 391.85 397.55
Tuesday 25-Nov-08 414.8 418.7 367 376.55
Wednesday 26-Nov-08 379.9 418.2 362.4 404.45
Friday 28-Nov-08 382 416 352.5 408.5
Monday 1-Dec-08 412.5 436.7 408.1 413.05
Tuesday 2-Dec-08 408 434.7 388.25 429.1
Wednesday 3-Dec-08 435 439.9 422.4 433.7
Thursday 4-Dec-08 439.5 464.8 433 455.2
Friday 5-Dec-08 457 475 440 443.05
Monday 8-Dec-08 460 468 448 451.95
Wednesday 10-Dec-08 457.4 466 438.15 462.8
Thursday 11-Dec-08 465.5 479 444.4 457
Friday 12-Dec-08 449.7 473.4 435.6 468.8
Monday 15-Dec-08 478.2 492.4 475 486.2
Tuesday 16-Dec-08 485 506.3 478.25 500.8
Wednesday 17-Dec-08 504 520 492.05 497.6
Thursday 18-Dec-08 495 543 495 537.2
Friday 19-Dec-08 539.7 562 531.25 546
Monday 22-Dec-08 549.7 556.8 528.55 537.5
Tuesday 23-Dec-08 533.1 542.9 490.75 499.5
Wednesday 24-Dec-08 495 498 481 490.75
Friday 26-Dec-08 491.45 511 477.25 482.8
Monday 29-Dec-08 480 501.8 468 494.95
Tuesday 30-Dec-08 496.5 519.4 494 510.25
Wednesday 31-Dec-08 519.7 521 499.6 504.7
Thursday 1-Jan-09 508.5 522.7 508 516.85
Friday 2-Jan-09 522 547.5 517.3 541.9
Monday 5-Jan-09 549.5 564.8 549.5 560.1
Tuesday 6-Jan-09 561 577 551.4 569.75
Wednesday 7-Jan-09 574 575.4 515 522.1
Friday 9-Jan-09 508 540.3 475 486.6
Monday 12-Jan-09 490 494.8 445 451.7
Tuesday 13-Jan-09 442 465 433 440.3
Wednesday 14-Jan-09 450 456.95 438 449.4
Thursday 15-Jan-09 430 438.4 421 434.05
Friday 16-Jan-09 448.7 451 436 443.55
Monday 19-Jan-09 448.9 450 431.55 438.3
Tuesday 20-Jan-09 429.7 429.7 416.6 422.4
Wednesday 21-Jan-09 405.55 428 404 407.7
Thursday 22-Jan-09 410.5 421 403.3 416.05
Friday 23-Jan-09 410 418.9 380.1 384.3
Tuesday 27-Jan-09 394.95 404.7 383.25 394.75
Wednesday 28-Jan-09 399 429.5 397 422.85

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Elainesmith]

Thank you for the points. Excel file attached. Column K is my shortcut / working. Need a better solution for it. Deliverable is columns under Weekly (G-J)and Monthly (L-O)Data. Column A - F is raw data.

Thanks,
Eliane

[alansidman]

With a Pivot Table you can quickly draw out the max and mins for each week and month. See attached for demonstration.

v	A	B	C	D
1	Row Labels+D23D4A1:D22A1:D22A1:D21	Max of High	Min of Low
2	2008
3	Nov	674.8	352.5
4	45	674.8	547.15	The numbers in Column A are the week numbers
5	46	613	487
6	47	587.2	383.6
7	48	420	352.5
8	Dec	562	388.25
9	49	464.8	388.25
10	49	475	440
11	50	479	435.6
12	51	562	475
13	52	556.8	477.25
14	53	521	468
15	2009
16	Jan	577	380.1
17	1	547.5	508
18	2	577	475
19	3	494.8	421
20	4	450	380.1
21	5	445	383.25
22
23

[Elainesmith]

Thx. And for open and close values? How can I list / pick them?

[alansidman]

Still thinking about that. Will post when I have a solution. In the meantime, hopefully, someone else will be able to supply that.

[alansidman]

in H3 =IF(C3=MIN($C$3:$C$7),D3,"") and copy down for each week. Similar formula for Close only use Max instead of Min. I don't see a way to do this globally, it will require you to change the range of the Min and Max for each week. Seems a little laborious. Will continue to think on it.

[Fluff13]

If your interested in VBA, how about

Please Login or Register  to view this content.

[Elainesmith]

thx Fluff13 but VBA is totally unknown area for me. I don't know even A of it.

[alansidman]

Here is how to install and run Fluff's code.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[Glenn Kennedy]

Formula based. Is this the sort of thing you had in mind for the weekly data? The start date chosen MUST be a Monday. Happy to explain - but won't do so now, in case this is NOT what you want!!

[Glenn Kennedy]

Now with months added, and the formulae tidied up a bit.

[Elainesmith]

Sorry for the delay. But yes I am keen to know how you did it? Your solution works beautifully

[Elainesmith]

thanks. This will be of great help