vba - Calculate average values for rows with different ids in MS Excel -

-

file contains information products per day, , need calculate average values month each product.

source data looks this:

    b           c       d     id date        rating price 1  1  2014/01/01  2       20 2  1  2014/01/02  2       20 3  1  2014/01/03  2       20 4  1  2014/01/04  1       20 5  1  2014/01/05  1       20 6  1  2014/01/06  1       20 7  1  2014/01/07  1       20  8  3  2014/01/01  5       99 9  3  2014/01/02  5       99 10 3  2014/01/03  5       99 11 3  2014/01/04  5       99 12 3  2014/01/05  5       120 13 3  2014/01/06  5       120 14 3  2014/01/07  5       120

need get:

    b           c       d     id date        rating price 1  1              1.42    20 2  3              5       108

how that? need advanced formula or vb script.

update: have data long period - 2 years. need calculate average values each product each week, , after each month.

source data example: 

id date         rating 4   2013-09-01  445 4   2013-09-02  446 4   2013-09-03  447 4   2013-09-04  448 4   2013-09-05  449 4   2013-09-06  450 4   2013-09-07  451 4   2013-09-08  452 4   2013-09-09  453 4   2013-09-10  454 4   2013-09-11  455 4   2013-09-12  456 4   2013-09-13  457 4   2013-09-14  458 4   2013-09-15  459 4   2013-09-16  460 4   2013-09-17  461 4   2013-09-18  462 4   2013-09-19  463 4   2013-09-20  464 4   2013-09-21  465 4   2013-09-22  466 4   2013-09-23  467 4   2013-09-24  468 4   2013-09-25  469 4   2013-09-26  470 4   2013-09-27  471 4   2013-09-28  472 4   2013-09-29  473 4   2013-09-30  474 4   2013-10-01  475 4   2013-10-02  476 4   2013-10-03  477 4   2013-10-04  478 4   2013-10-05  479 4   2013-10-06  480 4   2013-10-07  481 4   2013-10-08  482 4   2013-10-09  483 4   2013-10-10  484 4   2013-10-11  485 4   2013-10-12  486 4   2013-10-13  487 4   2013-10-14  488 4   2013-10-15  489 4   2013-10-16  490 4   2013-10-17  491 4   2013-10-18  492 4   2013-10-19  493 4   2013-10-20  494 4   2013-10-21  495 4   2013-10-22  496 4   2013-10-23  497 4   2013-10-24  498 4   2013-10-25  499 4   2013-10-26  500 4   2013-10-27  501 4   2013-10-28  502 4   2013-10-29  503 4   2013-10-30  504 4   2013-10-31  505  7   2013-09-01  1445 7   2013-09-02  1446 7   2013-09-03  1447 7   2013-09-04  1448 7   2013-09-05  1449 7   2013-09-06  1450 7   2013-09-07  1451 7   2013-09-08  1452 7   2013-09-09  1453 7   2013-09-10  1454 7   2013-09-11  1455 7   2013-09-12  1456 7   2013-09-13  1457 7   2013-09-14  1458 7   2013-09-15  1459 7   2013-09-16  1460 7   2013-09-17  1461 7   2013-09-18  1462 7   2013-09-19  1463 7   2013-09-20  1464 7   2013-09-21  1465 7   2013-09-22  1466 7   2013-09-23  1467 7   2013-09-24  1468 7   2013-09-25  1469 7   2013-09-26  1470 7   2013-09-27  1471 7   2013-09-28  1472 7   2013-09-29  1473 7   2013-09-30  1474 7   2013-10-01  1475 7   2013-10-02  1476 7   2013-10-03  1477 7   2013-10-04  1478 7   2013-10-05  1479 7   2013-10-06  1480 7   2013-10-07  1481 7   2013-10-08  1482 7   2013-10-09  1483 7   2013-10-10  1484 7   2013-10-11  1485 7   2013-10-12  1486 7   2013-10-13  1487 7   2013-10-14  1488 7   2013-10-15  1489 7   2013-10-16  1490 7   2013-10-17  1491 7   2013-10-18  1492 7   2013-10-19  1493 7   2013-10-20  1494 7   2013-10-21  1495 7   2013-10-22  1496 7   2013-10-23  1497 7   2013-10-24  1498 7   2013-10-25  1499 7   2013-10-26  1500 7   2013-10-27  1501 7   2013-10-28  1502 7   2013-10-29  1503 7   2013-10-30  1504 7   2013-10-31  1505

this job of pivot table, , takes 30secs it

enter image description here

update: per update, put date report filter , modify suit


Comments

Post a Comment

Popular posts from this blog

c++ - Delete matches in OpenCV (Keypoints and descriptors) -

-
i want check scene image against 2 train images. that, detect features , compute descriptors of both training images. before detecting, computing , matching scene image, delete matches of train1 , train2. because these matches won't facilitate matching of scene image train1 , train2. so, match train1 train2 , vector of matches trainidx , queryidx. how can delete these matches in keypoints-vector , descriptor matrix of train1 , train2? best regards, dwi i have done below: std::vector<cv::keypoint> keypoints[2]; cv::mat descriptor[2]; std::vector< cv::dmatch > matches; /* write code generate keypoints, descriptors , matches here... keypoint[0] -> train image 1 keypoints keypoint[1] -> train image 2 keypoints descriptor[0] -> train image 1 descriptors descriptor[1] -> train image 2 descriptors matches -> matched between train image 1 , 2 */ // logic keep unmatched keypoints , corresponding descriptor
Read more

java - Could not locate OpenAL library -

-
i writing game using slick2d library, uses lwjgl. lwjgl version 2.9.3. using netbeans 8. i tried adding sounds game, example music track on main menu. problem is, exception when run game, despite game still compiling , running except sound of course: sat mar 21 01:23:57 edt 2015 error:could not locate openal library. org.lwjgl.lwjglexception: not locate openal library. @ org.lwjgl.openal.al.create(al.java:156) @ org.lwjgl.openal.al.create(al.java:156) @ org.lwjgl.openal.al.create(al.java:102) @ org.lwjgl.openal.al.create(al.java:206) @ org.newdawn.slick.openal.soundstore$1.run(soundstore.java:295) @ java.security.accesscontroller.doprivileged(native method) @ org.newdawn.slick.openal.soundstore.init(soundstore.java:292) @ org.newdawn.slick.music.<init>(music.java:156) @ org.newdawn.slick.music.<init>(music.java:75) @ view.mainmenu.init(mainmenu.java:58) i didn't include rest of error states error happens in game code. the
Read more

sorting - opencl Bitonic sort with 64 bits keys -

-
i used nvidia sdk bitonic sort , works great me. 32 bits (uint) need ulong keys. have typically 2^14 keys @ time , power of 2. searched on not find kernel designed ulong. i tried modified nvidia sdk bitonic sort using ulong keys not work. kernel not crash after de call clenqueuendrangekernel got error : cl_invalid_command_queue can tell me how modify bitonic sort or radixsort, or can sort ulong keys? i running nvidia quadro 4000 opencl 1.1 cuda 6.5.20 full_profile used original nvidia sdk bitonicsort.cl i used"ulong" keys , value input , ouput instead of "uint" thanks helping i made work-around suit need. used 30 bits of key , 18 high bits of value sorting. changed comparators (2 lines): '#define mask 0xffffc000 // suit need from this: if( (*keya > *keyb) == dir ) this: if(((*keya > *keyb)||((*keya == *keyb)&&((*vala & mask)> (*valb & mask)))) == dir ) it works fine. but, still, it's work-a
Read more