admin管理员组文章数量:1405335
I've been trying to create an excel analysis with the following statistics for each of the _count columns in my data set:
- MAX
- MIN
- MEAN
- MEDIAN
- MODE
Furthermore, I have additional requirements that need to be fulfilled which makes writing functions more complex:
Create the statistics for all users (INCLUDING users that have never logged a given item | value = 0)
Create the statistics for all user (EXCLUDING users that have never logged a given item | value > 0)
ALL FUNCTIONS have to dynamically adjust the result, based on the filter in the column 'Registration_Date'.
If one were to select only values of users that registered in March and February, the statistics for both scenarios (1. + 2.) should update automatically. The functions should not consider any hidden rows.
I have been experimenting with subtotal and array functions but I can't seem to solve the problem on how I can create my analysis all in one sheet. I want to change the registration filter once and have my entire worksheet adjust to the changes made.
Currently I create the statistics for ALL users ( 0 values included ) via:
Min: =SUBTOTAL(105,'MASTER DATA'!J:J)
Max: =SUBTOTAL(104,'MASTER DATA'!J:J)
Mean: =SUBTOTAL(101,'MASTER DATA'!J:J)
Median: =MEDIAN(FILTER('MASTER DATA'!J:J, SUBTOTAL(3, OFFSET('MASTER DATA'!J:J, 0, 0))))
Mode: =MODE.MULT(FILTER('MASTER DATA'!J:J, SUBTOTAL(3, OFFSET('MASTER DATA'!J:J, 0, 0))))
But I have not found a way to do the same only for rows within the selected timeframe where cell value > 0
Thanks for the help!
| ACCOUNT_ID | REGISTRATION_DATE | REFLECTION_COUNT | MEAL_COUNT |
|---|---|---|---|
| 1 | 1/25/24 | 0 | 0 |
| 9 | 1/26/24 | 0 | 0 |
| 10 | 1/26/24 | 0 | 0 |
| 11 | 1/26/24 | 0 | 0 |
| 12 | 1/29/24 | 0 | 0 |
| 14 | 1/29/24 | 0 | 2 |
| 15 | 1/29/24 | 0 | 3 |
| 18 | 2/8/24 | 1 | 0 |
| 20 | 2/9/24 | 0 | 0 |
| 21 | 2/12/24 | 0 | 0 |
| 23 | 2/13/24 | 0 | 0 |
| 24 | 2/14/24 | 0 | 0 |
| 25 | 2/15/24 | 0 | 0 |
| 27 | 3/1/24 | 0 | 0 |
| 28 | 3/1/24 | 0 | 0 |
| 31 | 3/6/24 | 0 | 0 |
| 33 | 3/18/24 | 0 | 1 |
| 36 | 4/8/24 | 0 | 9 |
| 38 | 4/8/24 | 1 | 1 |
| 41 | 4/9/24 | 0 | 0 |
| 46 | 4/11/24 | 0 | 0 |
| 48 | 4/30/24 | 2 | 9 |
| 49 | 5/1/24 | 0 | 2 |
| 50 | 5/3/24 | 5 | 30 |
| 54 | 5/22/24 | 3 | 4 |
| 61 | 7/24/24 | 2 | 6 |
| 63 | 7/26/24 | 0 | 0 |
| 77 | 8/8/24 | 1 | 5 |
| 78 | 8/8/24 | 1 | 26 |
| 85 | 8/9/24 | 0 | 0 |
| 89 | 8/9/24 | 1 | 0 |
| 92 | 8/10/24 | 1 | 6 |
| 99 | 8/11/24 | 0 | 0 |
| 115 | 8/12/24 | 0 | 2 |
| 124 | 8/13/24 | 0 | 0 |
| 128 | 8/14/24 | 1 | 3 |
| 140 | 8/15/24 | 0 | 0 |
| 142 | 8/16/24 | 1 | 4 |
| 153 | 8/16/24 | 1 | 0 |
| 164 | 8/17/24 | 1 | 0 |
| 167 | 8/17/24 | 1 | 1 |
| 171 | 8/18/24 | 1 | 50 |
| 172 | 8/18/24 | 0 | 0 |
| 180 | 8/18/24 | 0 | 0 |
| 185 | 8/19/24 | 0 | 0 |
| 188 | 8/19/24 | 0 | 0 |
| 195 | 8/19/24 | 1 | 0 |
| 204 | 8/20/24 | 1 | 1 |
| 210 | 8/20/24 | 0 | 0 |
| 220 | 8/22/24 | 0 | 0 |
| 231 | 8/23/24 | 0 | 0 |
| 241 | 8/24/24 | 0 | 0 |
| 244 | 8/24/24 | 0 | 0 |
| 248 | 8/24/24 | 0 | 0 |
| 261 | 8/26/24 | 1 | 11 |
| 265 | 8/26/24 | 0 | 0 |
| 267 | 8/26/24 | 0 | 4 |
| 274 | 8/27/24 | 0 | 0 |
| 276 | 8/28/24 | 0 | 0 |
| 277 | 8/28/24 | 0 | 2 |
| 283 | 8/29/24 | 0 | 4 |
| 286 | 8/29/24 | 1 | 2 |
| 287 | 8/29/24 | 0 | 0 |
| 288 | 8/29/24 | 2 | 20 |
| 295 | 8/30/24 | 0 | 0 |
| 303 | 8/31/24 | 0 | 0 |
| 305 | 8/31/24 | 0 | 0 |
| 307 | 8/31/24 | 0 | 0 |
| 310 | 8/31/24 | 0 | 0 |
| 311 | 8/31/24 | 1 | 11 |
| 312 | 8/31/24 | 1 | 2 |
| 317 | 8/31/24 | 0 | 1 |
| 322 | 9/1/24 | 0 | 1 |
| 323 | 9/1/24 | 1 | 0 |
| 332 | 9/2/24 | 0 | 0 |
| 333 | 9/2/24 | 1 | 1 |
| 336 | 9/2/24 | 1 | 2 |
| 338 | 9/2/24 | 0 | 0 |
| 340 | 9/2/24 | 0 | 0 |
| 344 | 9/3/24 | 0 | 0 |
| 353 | 9/4/24 | 0 | 0 |
| 354 | 9/4/24 | 0 | 0 |
| 355 | 9/4/24 | 0 | 0 |
| 362 | 9/5/24 | 0 | 0 |
| 363 | 9/5/24 | 0 | 0 |
| 365 | 9/5/24 | 0 | 0 |
| 377 | 9/6/24 | 5 | 59 |
| 381 | 9/7/24 | 0 | 0 |
| 390 | 9/8/24 | 0 | 0 |
| 391 | 9/8/24 | 0 | 0 |
| 392 | 9/8/24 | 1 | 1 |
| 397 | 9/8/24 | 1 | 5 |
| 398 | 9/8/24 | 9 | 13 |
| 403 | 9/8/24 | 1 | 1 |
| 404 | 9/8/24 | 0 | 0 |
| 407 | 9/9/24 | 0 | 0 |
| 411 | 9/10/24 | 1 | 14 |
| 412 | 9/10/24 | 1 | 1 |
| 413 | 9/10/24 | 2 | 3 |
| 415 | 9/11/24 | 0 | 0 |
| 421 | 9/11/24 | 0 | 0 |
| 427 | 9/12/24 | 0 | 0 |
| 428 | 9/12/24 | 1 | 3 |
| 436 | 9/13/24 | 0 | 1 |
| 437 | 9/13/24 | 0 | 0 |
| 440 | 9/13/24 | 0 | 0 |
| 446 | 9/14/24 | 0 | 0 |
| 451 | 9/14/24 | 1 | 26 |
| 456 | 9/15/24 | 1 | 5 |
| 471 | 9/16/24 | 1 | 4 |
| 482 | 9/17/24 | 1 | 1 |
| 484 | 9/17/24 | 0 | 0 |
| 489 | 9/18/24 | 0 | 0 |
| 493 | 9/18/24 | 0 | 0 |
| 494 | 9/18/24 | 1 | 2 |
| 497 | 9/18/24 | 0 | 0 |
| 501 | 9/19/24 | 1 | 1 |
| 506 | 9/19/24 | 0 | 0 |
| 513 | 9/20/24 | 0 | 0 |
| 515 | 9/20/24 | 1 | 14 |
| 516 | 9/20/24 | 0 | 0 |
| 519 | 9/21/24 | 1 | 2 |
| 520 | 9/21/24 | 1 | 0 |
| 521 | 9/21/24 | 0 | 0 |
| 523 | 9/22/24 | 1 | 5 |
| 524 | 9/22/24 | 0 | 0 |
| 529 | 9/22/24 | 1 | 9 |
| 532 | 9/22/24 | 8 | 105 |
| 533 | 9/22/24 | 0 | 0 |
| 536 | 9/23/24 | 1 | 11 |
| 541 | 9/23/24 | 2 | 13 |
| 548 | 9/24/24 | 0 | 0 |
| 553 | 9/24/24 | 0 | 4 |
| 557 | 9/25/24 | 0 | 0 |
| 558 | 9/25/24 | 0 | 0 |
| 560 | 9/25/24 | 0 | 1 |
| 564 | 9/26/24 | 0 | 0 |
| 570 | 9/27/24 | 0 | 0 |
| 574 | 9/28/24 | 0 | 0 |
| 576 | 9/29/24 | 0 | 0 |
| 580 | 9/29/24 | 1 | 14 |
| 581 | 9/29/24 | 0 | 0 |
| 584 | 9/29/24 | 1 | 0 |
| 588 | 9/30/24 | 0 | 0 |
| 589 | 9/30/24 | 1 | 0 |
| 590 | 10/1/24 | 0 | 0 |
| 594 | 10/1/24 | 1 | 0 |
| 597 | 10/2/24 | 1 | 1 |
| 598 | 10/2/24 | 1 | 4 |
| 605 | 10/2/24 | 0 | 0 |
| 607 | 10/2/24 | 0 | 0 |
| 610 | 10/3/24 | 0 | 2 |
| 612 | 10/3/24 | 1 | 1 |
| 613 | 10/3/24 | 0 | 0 |
| 614 | 10/3/24 | 1 | 0 |
| 615 | 10/3/24 | 1 | 2 |
| 617 | 10/3/24 | 1 | 1 |
| 619 | 10/4/24 | 0 | 0 |
| 621 | 10/4/24 | 1 | 2 |
| 625 | 10/4/24 | 0 | 0 |
| 626 | 10/4/24 | 0 | 0 |
| 629 | 10/6/24 | 0 | 0 |
| 630 | 10/6/24 | 0 | 0 |
| 631 | 10/6/24 | 0 | 0 |
| 638 | 10/9/24 | 0 | 0 |
| 639 | 10/9/24 | 0 | 0 |
| 640 | 10/9/24 | 0 | 0 |
| 642 | 10/9/24 | 0 | 1 |
| 644 | 10/9/24 | 1 | 1 |
| 645 | 10/9/24 | 1 | 1 |
| 647 | 10/10/24 | 0 | 0 |
| 648 | 10/10/24 | 0 | 0 |
| 652 | 10/10/24 | 0 | 0 |
| 655 | 10/10/24 | 1 | 0 |
| 656 | 10/10/24 | 0 | 0 |
| 658 | 10/10/24 | 0 | 0 |
| 667 | 10/11/24 | 0 | 0 |
| 671 | 10/12/24 | 0 | 0 |
| 672 | 10/12/24 | 2 | 2 |
| 677 | 10/12/24 | 0 | 0 |
| 679 | 10/12/24 | 0 | 0 |
| 685 | 10/12/24 | 1 | 2 |
| 693 | 10/12/24 | 0 | 0 |
| 696 | 10/13/24 | 0 | 0 |
| 702 | 10/13/24 | 1 | 0 |
| 711 | 10/14/24 | 0 | 0 |
| 715 | 10/15/24 | 0 | 0 |
| 719 | 10/15/24 | 0 | 0 |
| 720 | 10/15/24 | 0 | 0 |
| 726 | 10/16/24 | 11 | 36 |
| 730 | 10/16/24 | 0 | 0 |
| 731 | 10/16/24 | 0 | 0 |
| 733 | 10/16/24 | 0 | 0 |
| 734 | 10/16/24 | 0 | 0 |
| 736 | 10/16/24 | 1 | 0 |
| 740 | 10/17/24 | 1 | 0 |
| 746 | 10/18/24 | 0 | 0 |
| 748 | 10/20/24 | 1 | 18 |
| 751 | 10/29/24 | 0 | 0 |
| 754 | 11/7/24 | 1 | 0 |
| 762 | 12/13/24 | 0 | 0 |
| 784 | 1/11/25 | 1 | 3 |
| 813 | 1/20/25 | 0 | 0 |
| 824 | 1/22/25 | 0 | 0 |
| 835 | 1/27/25 | 0 | 0 |
| 843 | 2/2/25 | 0 | 0 |
| 845 | 2/2/25 | 0 | 0 |
| 847 | 2/2/25 | 0 | 0 |
| 848 | 2/2/25 | 1 | 14 |
| 849 | 2/3/25 | 1 | 2 |
| 850 | 2/3/25 | 1 | 1 |
| 851 | 2/4/25 | 0 | 0 |
| 854 | 2/5/25 | 0 | 0 |
| 855 | 2/6/25 | 0 | 8 |
| 859 | 2/7/25 | 0 | 0 |
| 860 | 2/10/25 | 0 | 0 |
| 861 | 2/11/25 | 0 | 0 |
| 863 | 2/12/25 | 1 | 58 |
| 864 | 2/13/25 | 0 | 1 |
| 867 | 2/14/25 | 1 | 5 |
| 870 | 2/15/25 | 0 | 0 |
| 871 | 2/16/25 | 0 | 0 |
| 872 | 2/16/25 | 0 | 0 |
| 873 | 2/16/25 | 0 | 0 |
| 874 | 2/16/25 | 0 | 0 |
| 875 | 2/17/25 | 0 | 0 |
| 876 | 2/17/25 | 0 | 2 |
| 877 | 2/18/25 | 0 | 1 |
| 878 | 2/18/25 | 0 | 0 |
| 879 | 2/18/25 | 1 | 18 |
| 880 | 2/18/25 | 0 | 0 |
| 881 | 2/18/25 | 0 | 0 |
| 882 | 2/19/25 | 0 | 0 |
| 883 | 2/19/25 | 0 | 0 |
| 884 | 2/19/25 | 0 | 0 |
| 885 | 2/19/25 | 0 | 0 |
| 886 | 2/20/25 | 0 | 0 |
| 887 | 2/20/25 | 1 | 9 |
| 888 | 2/20/25 | 1 | 13 |
| 889 | 2/20/25 | 0 | 0 |
| 890 | 2/20/25 | 0 | 0 |
| 891 | 2/20/25 | 0 | 0 |
| 892 | 2/20/25 | 0 | 0 |
| 893 | 2/20/25 | 1 | 3 |
| 894 | 2/20/25 | 0 | 0 |
| 895 | 2/20/25 | 0 | 0 |
| 896 | 2/20/25 | 0 | 0 |
| 897 | 2/20/25 | 0 | 0 |
| 898 | 2/20/25 | 0 | 0 |
| 899 | 2/20/25 | 1 | 16 |
| 900 | 2/20/25 | 0 | 0 |
| 901 | 2/20/25 | 1 | 10 |
| 902 | 2/20/25 | 0 | 0 |
| 903 | 2/20/25 | 0 | 0 |
| 904 | 2/20/25 | 0 | 0 |
| 905 | 2/20/25 | 0 | 0 |
| 906 | 2/20/25 | 0 | 0 |
| 907 | 2/20/25 | 0 | 0 |
| 908 | 2/20/25 | 0 | 0 |
| 909 | 2/20/25 | 0 | 0 |
| 910 | 2/21/25 | 0 | 0 |
| 911 | 2/21/25 | 0 | 0 |
| 912 | 2/21/25 | 0 | 0 |
| 913 | 2/21/25 | 0 | 0 |
| 914 | 2/21/25 | 0 | 5 |
| 915 | 2/21/25 | 1 | 18 |
| 916 | 2/21/25 | 0 | 0 |
| 917 | 2/21/25 | 0 | 0 |
| 918 | 2/21/25 | 0 | 0 |
| 919 | 2/21/25 | 0 | 0 |
| 920 | 2/21/25 | 1 | 0 |
| 921 | 2/21/25 | 1 | 3 |
| 922 | 2/21/25 | 0 | 0 |
| 923 | 2/21/25 | 0 | 0 |
| 924 | 2/21/25 | 0 | 0 |
| 925 | 2/21/25 | 0 | 0 |
| 926 | 2/21/25 | 0 | 0 |
| 927 | 2/21/25 | 0 | 0 |
| 928 | 2/21/25 | 0 | 0 |
| 929 | 2/21/25 | 0 | 7 |
| 930 | 2/21/25 | 1 | 0 |
| 931 | 2/21/25 | 0 | 0 |
| 932 | 2/21/25 | 0 | 0 |
| 933 | 2/21/25 | 0 | 0 |
| 934 | 2/21/25 | 0 | 0 |
| 935 | 2/21/25 | 1 | 17 |
| 936 | 2/21/25 | 0 | 0 |
| 937 | 2/21/25 | 1 | 3 |
| 938 | 2/21/25 | 1 | 4 |
| 939 | 2/21/25 | 0 | 0 |
| 940 | 2/22/25 | 0 | 0 |
| 941 | 2/22/25 | 0 | 0 |
| 942 | 2/22/25 | 0 | 0 |
| 943 | 2/22/25 | 0 | 0 |
| 944 | 2/22/25 | 0 | 1 |
| 945 | 2/22/25 | 0 | 0 |
| 946 | 2/22/25 | 0 | 0 |
| 947 | 2/22/25 | 0 | 0 |
| 948 | 2/22/25 | 1 | 20 |
| 949 | 2/22/25 | 0 | 2 |
| 950 | 2/22/25 | 0 | 0 |
| 951 | 2/22/25 | 0 | 0 |
| 952 | 2/22/25 | 1 | 5 |
| 953 | 2/23/25 | 0 | 3 |
| 954 | 2/23/25 | 0 | 0 |
| 955 | 2/23/25 | 0 | 2 |
| 956 | 2/23/25 | 0 | 0 |
| 957 | 2/23/25 | 0 | 0 |
| 958 | 2/23/25 | 0 | 4 |
| 959 | 2/23/25 | 0 | 0 |
| 960 | 2/23/25 | 0 | 0 |
| 961 | 2/23/25 | 1 | 6 |
| 962 | 2/23/25 | 0 | 0 |
| 963 | 2/23/25 | 0 | 0 |
| 964 | 2/23/25 | 0 | 0 |
| 965 | 2/23/25 | 1 | 33 |
| 966 | 2/23/25 | 0 | 6 |
| 967 | 2/23/25 | 0 | 0 |
| 968 | 2/23/25 | 0 | 0 |
| 969 | 2/23/25 | 0 | 0 |
| 970 | 2/23/25 | 0 | 0 |
| 971 | 2/23/25 | 1 | 3 |
| 972 | 2/23/25 | 0 | 2 |
| 973 | 2/23/25 | 0 | 0 |
| 974 | 2/23/25 | 0 | 0 |
| 975 | 2/23/25 | 0 | 0 |
| 976 | 2/23/25 | 0 | 0 |
| 977 | 2/23/25 | 0 | 0 |
| 978 | 2/23/25 | 0 | 0 |
| 979 | 2/23/25 | 0 | 0 |
| 980 | 2/23/25 | 0 | 0 |
| 981 | 2/23/25 | 0 | 0 |
| 982 | 2/23/25 | 0 | 0 |
| 983 | 2/23/25 | 0 | 0 |
| 984 | 2/23/25 | 0 | 0 |
| 985 | 2/23/25 | 0 | 0 |
| 986 | 2/23/25 | 0 | 9 |
| 987 | 2/24/25 | 0 | 0 |
| 988 | 2/24/25 | 0 | 0 |
| 989 | 2/24/25 | 0 | 1 |
| 990 | 2/24/25 | 0 | 0 |
| 991 | 2/24/25 | 1 | 5 |
| 992 | 2/24/25 | 0 | 0 |
| 993 | 2/24/25 | 0 | 0 |
| 994 | 2/24/25 | 0 | 0 |
| 995 | 2/24/25 | 1 | 49 |
| 996 | 2/24/25 | 0 | 0 |
| 997 | 2/24/25 | 0 | 0 |
| 998 | 2/24/25 | 0 | 0 |
| 999 | 2/24/25 | 1 | 5 |
| 1000 | 2/24/25 | 0 | 0 |
| 1001 | 2/24/25 | 0 | 0 |
| 1002 | 2/24/25 | 0 | 0 |
| 1003 | 2/24/25 | 1 | 3 |
| 1004 | 2/24/25 | 0 | 0 |
| 1005 | 2/24/25 | 0 | 0 |
| 1006 | 2/24/25 | 0 | 2 |
| 1007 | 2/24/25 | 1 | 0 |
| 1008 | 2/24/25 | 0 | 4 |
| 1009 | 2/24/25 | 0 | 0 |
| 1010 | 2/24/25 | 0 | 0 |
| 1011 | 2/24/25 | 0 | 4 |
| 1012 | 2/24/25 | 0 | 0 |
| 1013 | 2/24/25 | 0 | 0 |
| 1014 | 2/24/25 | 0 | 0 |
| 1015 | 2/25/25 | 0 | 0 |
| 1016 | 2/25/25 | 0 | 0 |
| 1017 | 2/25/25 | 0 | 0 |
| 1018 | 2/25/25 | 0 | 0 |
| 1019 | 2/25/25 | 0 | 0 |
| 1020 | 2/25/25 | 0 | 0 |
| 1021 | 2/25/25 | 0 | 0 |
| 1022 | 2/25/25 | 0 | 0 |
| 1023 | 2/25/25 | 0 | 0 |
| 1024 | 2/25/25 | 0 | 0 |
| 1025 | 2/25/25 | 0 | 1 |
| 1026 | 2/25/25 | 0 | 0 |
| 1027 | 2/25/25 | 0 | 0 |
| 1028 | 2/25/25 | 0 | 0 |
| 1029 | 2/25/25 | 1 | 5 |
| 1030 | 2/25/25 | 0 | 0 |
| 1031 | 2/25/25 | 0 | 0 |
| 1032 | 2/25/25 | 0 | 0 |
| 1033 | 2/25/25 | 0 | 0 |
| 1034 | 2/25/25 | 0 | 0 |
| 1035 | 2/25/25 | 0 | 0 |
| 1036 | 2/25/25 | 0 | 0 |
| 1037 | 2/25/25 | 0 | 0 |
| 1038 | 2/26/25 | 0 | 1 |
| 1039 | 2/26/25 | 0 | 0 |
| 1040 | 2/26/25 | 0 | 1 |
| 1041 | 2/26/25 | 0 | 0 |
| 1042 | 2/26/25 | 0 | 0 |
| 1043 | 2/26/25 | 0 | 0 |
| 1044 | 2/26/25 | 0 | 0 |
| 1045 | 2/26/25 | 0 | 0 |
| 1046 | 2/26/25 | 0 | 0 |
| 1047 | 2/27/25 | 0 | 0 |
| 1048 | 2/27/25 | 0 | 0 |
| 1049 | 2/27/25 | 0 | 0 |
| 1050 | 3/6/25 | 0 | 0 |
| 1051 | 3/8/25 | 0 | 0 |
| 1052 | 3/8/25 | 0 | 0 |
| 1053 | 3/8/25 | 0 | 0 |
| 1054 | 3/8/25 | 0 | 0 |
| 1055 | 3/8/25 | 0 | 0 |
| 1056 | 3/8/25 | 0 | 0 |
| 1057 | 3/8/25 | 0 | 1 |
| 1058 | 3/8/25 | 0 | 0 |
| 1059 | 3/8/25 | 0 | 0 |
| 1060 | 3/8/25 | 0 | 0 |
| 1061 | 3/8/25 | 0 | 0 |
| 1062 | 3/8/25 | 0 | 0 |
| 1063 | 3/8/25 | 0 | 0 |
| 1064 | 3/8/25 | 0 | 0 |
I've been trying to create an excel analysis with the following statistics for each of the _count columns in my data set:
- MAX
- MIN
- MEAN
- MEDIAN
- MODE
Furthermore, I have additional requirements that need to be fulfilled which makes writing functions more complex:
Create the statistics for all users (INCLUDING users that have never logged a given item | value = 0)
Create the statistics for all user (EXCLUDING users that have never logged a given item | value > 0)
ALL FUNCTIONS have to dynamically adjust the result, based on the filter in the column 'Registration_Date'.
If one were to select only values of users that registered in March and February, the statistics for both scenarios (1. + 2.) should update automatically. The functions should not consider any hidden rows.
I have been experimenting with subtotal and array functions but I can't seem to solve the problem on how I can create my analysis all in one sheet. I want to change the registration filter once and have my entire worksheet adjust to the changes made.
Currently I create the statistics for ALL users ( 0 values included ) via:
Min: =SUBTOTAL(105,'MASTER DATA'!J:J)
Max: =SUBTOTAL(104,'MASTER DATA'!J:J)
Mean: =SUBTOTAL(101,'MASTER DATA'!J:J)
Median: =MEDIAN(FILTER('MASTER DATA'!J:J, SUBTOTAL(3, OFFSET('MASTER DATA'!J:J, 0, 0))))
Mode: =MODE.MULT(FILTER('MASTER DATA'!J:J, SUBTOTAL(3, OFFSET('MASTER DATA'!J:J, 0, 0))))
But I have not found a way to do the same only for rows within the selected timeframe where cell value > 0
Thanks for the help!
| ACCOUNT_ID | REGISTRATION_DATE | REFLECTION_COUNT | MEAL_COUNT |
|---|---|---|---|
| 1 | 1/25/24 | 0 | 0 |
| 9 | 1/26/24 | 0 | 0 |
| 10 | 1/26/24 | 0 | 0 |
| 11 | 1/26/24 | 0 | 0 |
| 12 | 1/29/24 | 0 | 0 |
| 14 | 1/29/24 | 0 | 2 |
| 15 | 1/29/24 | 0 | 3 |
| 18 | 2/8/24 | 1 | 0 |
| 20 | 2/9/24 | 0 | 0 |
| 21 | 2/12/24 | 0 | 0 |
| 23 | 2/13/24 | 0 | 0 |
| 24 | 2/14/24 | 0 | 0 |
| 25 | 2/15/24 | 0 | 0 |
| 27 | 3/1/24 | 0 | 0 |
| 28 | 3/1/24 | 0 | 0 |
| 31 | 3/6/24 | 0 | 0 |
| 33 | 3/18/24 | 0 | 1 |
| 36 | 4/8/24 | 0 | 9 |
| 38 | 4/8/24 | 1 | 1 |
| 41 | 4/9/24 | 0 | 0 |
| 46 | 4/11/24 | 0 | 0 |
| 48 | 4/30/24 | 2 | 9 |
| 49 | 5/1/24 | 0 | 2 |
| 50 | 5/3/24 | 5 | 30 |
| 54 | 5/22/24 | 3 | 4 |
| 61 | 7/24/24 | 2 | 6 |
| 63 | 7/26/24 | 0 | 0 |
| 77 | 8/8/24 | 1 | 5 |
| 78 | 8/8/24 | 1 | 26 |
| 85 | 8/9/24 | 0 | 0 |
| 89 | 8/9/24 | 1 | 0 |
| 92 | 8/10/24 | 1 | 6 |
| 99 | 8/11/24 | 0 | 0 |
| 115 | 8/12/24 | 0 | 2 |
| 124 | 8/13/24 | 0 | 0 |
| 128 | 8/14/24 | 1 | 3 |
| 140 | 8/15/24 | 0 | 0 |
| 142 | 8/16/24 | 1 | 4 |
| 153 | 8/16/24 | 1 | 0 |
| 164 | 8/17/24 | 1 | 0 |
| 167 | 8/17/24 | 1 | 1 |
| 171 | 8/18/24 | 1 | 50 |
| 172 | 8/18/24 | 0 | 0 |
| 180 | 8/18/24 | 0 | 0 |
| 185 | 8/19/24 | 0 | 0 |
| 188 | 8/19/24 | 0 | 0 |
| 195 | 8/19/24 | 1 | 0 |
| 204 | 8/20/24 | 1 | 1 |
| 210 | 8/20/24 | 0 | 0 |
| 220 | 8/22/24 | 0 | 0 |
| 231 | 8/23/24 | 0 | 0 |
| 241 | 8/24/24 | 0 | 0 |
| 244 | 8/24/24 | 0 | 0 |
| 248 | 8/24/24 | 0 | 0 |
| 261 | 8/26/24 | 1 | 11 |
| 265 | 8/26/24 | 0 | 0 |
| 267 | 8/26/24 | 0 | 4 |
| 274 | 8/27/24 | 0 | 0 |
| 276 | 8/28/24 | 0 | 0 |
| 277 | 8/28/24 | 0 | 2 |
| 283 | 8/29/24 | 0 | 4 |
| 286 | 8/29/24 | 1 | 2 |
| 287 | 8/29/24 | 0 | 0 |
| 288 | 8/29/24 | 2 | 20 |
| 295 | 8/30/24 | 0 | 0 |
| 303 | 8/31/24 | 0 | 0 |
| 305 | 8/31/24 | 0 | 0 |
| 307 | 8/31/24 | 0 | 0 |
| 310 | 8/31/24 | 0 | 0 |
| 311 | 8/31/24 | 1 | 11 |
| 312 | 8/31/24 | 1 | 2 |
| 317 | 8/31/24 | 0 | 1 |
| 322 | 9/1/24 | 0 | 1 |
| 323 | 9/1/24 | 1 | 0 |
| 332 | 9/2/24 | 0 | 0 |
| 333 | 9/2/24 | 1 | 1 |
| 336 | 9/2/24 | 1 | 2 |
| 338 | 9/2/24 | 0 | 0 |
| 340 | 9/2/24 | 0 | 0 |
| 344 | 9/3/24 | 0 | 0 |
| 353 | 9/4/24 | 0 | 0 |
| 354 | 9/4/24 | 0 | 0 |
| 355 | 9/4/24 | 0 | 0 |
| 362 | 9/5/24 | 0 | 0 |
| 363 | 9/5/24 | 0 | 0 |
| 365 | 9/5/24 | 0 | 0 |
| 377 | 9/6/24 | 5 | 59 |
| 381 | 9/7/24 | 0 | 0 |
| 390 | 9/8/24 | 0 | 0 |
| 391 | 9/8/24 | 0 | 0 |
| 392 | 9/8/24 | 1 | 1 |
| 397 | 9/8/24 | 1 | 5 |
| 398 | 9/8/24 | 9 | 13 |
| 403 | 9/8/24 | 1 | 1 |
| 404 | 9/8/24 | 0 | 0 |
| 407 | 9/9/24 | 0 | 0 |
| 411 | 9/10/24 | 1 | 14 |
| 412 | 9/10/24 | 1 | 1 |
| 413 | 9/10/24 | 2 | 3 |
| 415 | 9/11/24 | 0 | 0 |
| 421 | 9/11/24 | 0 | 0 |
| 427 | 9/12/24 | 0 | 0 |
| 428 | 9/12/24 | 1 | 3 |
| 436 | 9/13/24 | 0 | 1 |
| 437 | 9/13/24 | 0 | 0 |
| 440 | 9/13/24 | 0 | 0 |
| 446 | 9/14/24 | 0 | 0 |
| 451 | 9/14/24 | 1 | 26 |
| 456 | 9/15/24 | 1 | 5 |
| 471 | 9/16/24 | 1 | 4 |
| 482 | 9/17/24 | 1 | 1 |
| 484 | 9/17/24 | 0 | 0 |
| 489 | 9/18/24 | 0 | 0 |
| 493 | 9/18/24 | 0 | 0 |
| 494 | 9/18/24 | 1 | 2 |
| 497 | 9/18/24 | 0 | 0 |
| 501 | 9/19/24 | 1 | 1 |
| 506 | 9/19/24 | 0 | 0 |
| 513 | 9/20/24 | 0 | 0 |
| 515 | 9/20/24 | 1 | 14 |
| 516 | 9/20/24 | 0 | 0 |
| 519 | 9/21/24 | 1 | 2 |
| 520 | 9/21/24 | 1 | 0 |
| 521 | 9/21/24 | 0 | 0 |
| 523 | 9/22/24 | 1 | 5 |
| 524 | 9/22/24 | 0 | 0 |
| 529 | 9/22/24 | 1 | 9 |
| 532 | 9/22/24 | 8 | 105 |
| 533 | 9/22/24 | 0 | 0 |
| 536 | 9/23/24 | 1 | 11 |
| 541 | 9/23/24 | 2 | 13 |
| 548 | 9/24/24 | 0 | 0 |
| 553 | 9/24/24 | 0 | 4 |
| 557 | 9/25/24 | 0 | 0 |
| 558 | 9/25/24 | 0 | 0 |
| 560 | 9/25/24 | 0 | 1 |
| 564 | 9/26/24 | 0 | 0 |
| 570 | 9/27/24 | 0 | 0 |
| 574 | 9/28/24 | 0 | 0 |
| 576 | 9/29/24 | 0 | 0 |
| 580 | 9/29/24 | 1 | 14 |
| 581 | 9/29/24 | 0 | 0 |
| 584 | 9/29/24 | 1 | 0 |
| 588 | 9/30/24 | 0 | 0 |
| 589 | 9/30/24 | 1 | 0 |
| 590 | 10/1/24 | 0 | 0 |
| 594 | 10/1/24 | 1 | 0 |
| 597 | 10/2/24 | 1 | 1 |
| 598 | 10/2/24 | 1 | 4 |
| 605 | 10/2/24 | 0 | 0 |
| 607 | 10/2/24 | 0 | 0 |
| 610 | 10/3/24 | 0 | 2 |
| 612 | 10/3/24 | 1 | 1 |
| 613 | 10/3/24 | 0 | 0 |
| 614 | 10/3/24 | 1 | 0 |
| 615 | 10/3/24 | 1 | 2 |
| 617 | 10/3/24 | 1 | 1 |
| 619 | 10/4/24 | 0 | 0 |
| 621 | 10/4/24 | 1 | 2 |
| 625 | 10/4/24 | 0 | 0 |
| 626 | 10/4/24 | 0 | 0 |
| 629 | 10/6/24 | 0 | 0 |
| 630 | 10/6/24 | 0 | 0 |
| 631 | 10/6/24 | 0 | 0 |
| 638 | 10/9/24 | 0 | 0 |
| 639 | 10/9/24 | 0 | 0 |
| 640 | 10/9/24 | 0 | 0 |
| 642 | 10/9/24 | 0 | 1 |
| 644 | 10/9/24 | 1 | 1 |
| 645 | 10/9/24 | 1 | 1 |
| 647 | 10/10/24 | 0 | 0 |
| 648 | 10/10/24 | 0 | 0 |
| 652 | 10/10/24 | 0 | 0 |
| 655 | 10/10/24 | 1 | 0 |
| 656 | 10/10/24 | 0 | 0 |
| 658 | 10/10/24 | 0 | 0 |
| 667 | 10/11/24 | 0 | 0 |
| 671 | 10/12/24 | 0 | 0 |
| 672 | 10/12/24 | 2 | 2 |
| 677 | 10/12/24 | 0 | 0 |
| 679 | 10/12/24 | 0 | 0 |
| 685 | 10/12/24 | 1 | 2 |
| 693 | 10/12/24 | 0 | 0 |
| 696 | 10/13/24 | 0 | 0 |
| 702 | 10/13/24 | 1 | 0 |
| 711 | 10/14/24 | 0 | 0 |
| 715 | 10/15/24 | 0 | 0 |
| 719 | 10/15/24 | 0 | 0 |
| 720 | 10/15/24 | 0 | 0 |
| 726 | 10/16/24 | 11 | 36 |
| 730 | 10/16/24 | 0 | 0 |
| 731 | 10/16/24 | 0 | 0 |
| 733 | 10/16/24 | 0 | 0 |
| 734 | 10/16/24 | 0 | 0 |
| 736 | 10/16/24 | 1 | 0 |
| 740 | 10/17/24 | 1 | 0 |
| 746 | 10/18/24 | 0 | 0 |
| 748 | 10/20/24 | 1 | 18 |
| 751 | 10/29/24 | 0 | 0 |
| 754 | 11/7/24 | 1 | 0 |
| 762 | 12/13/24 | 0 | 0 |
| 784 | 1/11/25 | 1 | 3 |
| 813 | 1/20/25 | 0 | 0 |
| 824 | 1/22/25 | 0 | 0 |
| 835 | 1/27/25 | 0 | 0 |
| 843 | 2/2/25 | 0 | 0 |
| 845 | 2/2/25 | 0 | 0 |
| 847 | 2/2/25 | 0 | 0 |
| 848 | 2/2/25 | 1 | 14 |
| 849 | 2/3/25 | 1 | 2 |
| 850 | 2/3/25 | 1 | 1 |
| 851 | 2/4/25 | 0 | 0 |
| 854 | 2/5/25 | 0 | 0 |
| 855 | 2/6/25 | 0 | 8 |
| 859 | 2/7/25 | 0 | 0 |
| 860 | 2/10/25 | 0 | 0 |
| 861 | 2/11/25 | 0 | 0 |
| 863 | 2/12/25 | 1 | 58 |
| 864 | 2/13/25 | 0 | 1 |
| 867 | 2/14/25 | 1 | 5 |
| 870 | 2/15/25 | 0 | 0 |
| 871 | 2/16/25 | 0 | 0 |
| 872 | 2/16/25 | 0 | 0 |
| 873 | 2/16/25 | 0 | 0 |
| 874 | 2/16/25 | 0 | 0 |
| 875 | 2/17/25 | 0 | 0 |
| 876 | 2/17/25 | 0 | 2 |
| 877 | 2/18/25 | 0 | 1 |
| 878 | 2/18/25 | 0 | 0 |
| 879 | 2/18/25 | 1 | 18 |
| 880 | 2/18/25 | 0 | 0 |
| 881 | 2/18/25 | 0 | 0 |
| 882 | 2/19/25 | 0 | 0 |
| 883 | 2/19/25 | 0 | 0 |
| 884 | 2/19/25 | 0 | 0 |
| 885 | 2/19/25 | 0 | 0 |
| 886 | 2/20/25 | 0 | 0 |
| 887 | 2/20/25 | 1 | 9 |
| 888 | 2/20/25 | 1 | 13 |
| 889 | 2/20/25 | 0 | 0 |
| 890 | 2/20/25 | 0 | 0 |
| 891 | 2/20/25 | 0 | 0 |
| 892 | 2/20/25 | 0 | 0 |
| 893 | 2/20/25 | 1 | 3 |
| 894 | 2/20/25 | 0 | 0 |
| 895 | 2/20/25 | 0 | 0 |
| 896 | 2/20/25 | 0 | 0 |
| 897 | 2/20/25 | 0 | 0 |
| 898 | 2/20/25 | 0 | 0 |
| 899 | 2/20/25 | 1 | 16 |
| 900 | 2/20/25 | 0 | 0 |
| 901 | 2/20/25 | 1 | 10 |
| 902 | 2/20/25 | 0 | 0 |
| 903 | 2/20/25 | 0 | 0 |
| 904 | 2/20/25 | 0 | 0 |
| 905 | 2/20/25 | 0 | 0 |
| 906 | 2/20/25 | 0 | 0 |
| 907 | 2/20/25 | 0 | 0 |
| 908 | 2/20/25 | 0 | 0 |
| 909 | 2/20/25 | 0 | 0 |
| 910 | 2/21/25 | 0 | 0 |
| 911 | 2/21/25 | 0 | 0 |
| 912 | 2/21/25 | 0 | 0 |
| 913 | 2/21/25 | 0 | 0 |
| 914 | 2/21/25 | 0 | 5 |
| 915 | 2/21/25 | 1 | 18 |
| 916 | 2/21/25 | 0 | 0 |
| 917 | 2/21/25 | 0 | 0 |
| 918 | 2/21/25 | 0 | 0 |
| 919 | 2/21/25 | 0 | 0 |
| 920 | 2/21/25 | 1 | 0 |
| 921 | 2/21/25 | 1 | 3 |
| 922 | 2/21/25 | 0 | 0 |
| 923 | 2/21/25 | 0 | 0 |
| 924 | 2/21/25 | 0 | 0 |
| 925 | 2/21/25 | 0 | 0 |
| 926 | 2/21/25 | 0 | 0 |
| 927 | 2/21/25 | 0 | 0 |
| 928 | 2/21/25 | 0 | 0 |
| 929 | 2/21/25 | 0 | 7 |
| 930 | 2/21/25 | 1 | 0 |
| 931 | 2/21/25 | 0 | 0 |
| 932 | 2/21/25 | 0 | 0 |
| 933 | 2/21/25 | 0 | 0 |
| 934 | 2/21/25 | 0 | 0 |
| 935 | 2/21/25 | 1 | 17 |
| 936 | 2/21/25 | 0 | 0 |
| 937 | 2/21/25 | 1 | 3 |
| 938 | 2/21/25 | 1 | 4 |
| 939 | 2/21/25 | 0 | 0 |
| 940 | 2/22/25 | 0 | 0 |
| 941 | 2/22/25 | 0 | 0 |
| 942 | 2/22/25 | 0 | 0 |
| 943 | 2/22/25 | 0 | 0 |
| 944 | 2/22/25 | 0 | 1 |
| 945 | 2/22/25 | 0 | 0 |
| 946 | 2/22/25 | 0 | 0 |
| 947 | 2/22/25 | 0 | 0 |
| 948 | 2/22/25 | 1 | 20 |
| 949 | 2/22/25 | 0 | 2 |
| 950 | 2/22/25 | 0 | 0 |
| 951 | 2/22/25 | 0 | 0 |
| 952 | 2/22/25 | 1 | 5 |
| 953 | 2/23/25 | 0 | 3 |
| 954 | 2/23/25 | 0 | 0 |
| 955 | 2/23/25 | 0 | 2 |
| 956 | 2/23/25 | 0 | 0 |
| 957 | 2/23/25 | 0 | 0 |
| 958 | 2/23/25 | 0 | 4 |
| 959 | 2/23/25 | 0 | 0 |
| 960 | 2/23/25 | 0 | 0 |
| 961 | 2/23/25 | 1 | 6 |
| 962 | 2/23/25 | 0 | 0 |
| 963 | 2/23/25 | 0 | 0 |
| 964 | 2/23/25 | 0 | 0 |
| 965 | 2/23/25 | 1 | 33 |
| 966 | 2/23/25 | 0 | 6 |
| 967 | 2/23/25 | 0 | 0 |
| 968 | 2/23/25 | 0 | 0 |
| 969 | 2/23/25 | 0 | 0 |
| 970 | 2/23/25 | 0 | 0 |
| 971 | 2/23/25 | 1 | 3 |
| 972 | 2/23/25 | 0 | 2 |
| 973 | 2/23/25 | 0 | 0 |
| 974 | 2/23/25 | 0 | 0 |
| 975 | 2/23/25 | 0 | 0 |
| 976 | 2/23/25 | 0 | 0 |
| 977 | 2/23/25 | 0 | 0 |
| 978 | 2/23/25 | 0 | 0 |
| 979 | 2/23/25 | 0 | 0 |
| 980 | 2/23/25 | 0 | 0 |
| 981 | 2/23/25 | 0 | 0 |
| 982 | 2/23/25 | 0 | 0 |
| 983 | 2/23/25 | 0 | 0 |
| 984 | 2/23/25 | 0 | 0 |
| 985 | 2/23/25 | 0 | 0 |
| 986 | 2/23/25 | 0 | 9 |
| 987 | 2/24/25 | 0 | 0 |
| 988 | 2/24/25 | 0 | 0 |
| 989 | 2/24/25 | 0 | 1 |
| 990 | 2/24/25 | 0 | 0 |
| 991 | 2/24/25 | 1 | 5 |
| 992 | 2/24/25 | 0 | 0 |
| 993 | 2/24/25 | 0 | 0 |
| 994 | 2/24/25 | 0 | 0 |
| 995 | 2/24/25 | 1 | 49 |
| 996 | 2/24/25 | 0 | 0 |
| 997 | 2/24/25 | 0 | 0 |
| 998 | 2/24/25 | 0 | 0 |
| 999 | 2/24/25 | 1 | 5 |
| 1000 | 2/24/25 | 0 | 0 |
| 1001 | 2/24/25 | 0 | 0 |
| 1002 | 2/24/25 | 0 | 0 |
| 1003 | 2/24/25 | 1 | 3 |
| 1004 | 2/24/25 | 0 | 0 |
| 1005 | 2/24/25 | 0 | 0 |
| 1006 | 2/24/25 | 0 | 2 |
| 1007 | 2/24/25 | 1 | 0 |
| 1008 | 2/24/25 | 0 | 4 |
| 1009 | 2/24/25 | 0 | 0 |
| 1010 | 2/24/25 | 0 | 0 |
| 1011 | 2/24/25 | 0 | 4 |
| 1012 | 2/24/25 | 0 | 0 |
| 1013 | 2/24/25 | 0 | 0 |
| 1014 | 2/24/25 | 0 | 0 |
| 1015 | 2/25/25 | 0 | 0 |
| 1016 | 2/25/25 | 0 | 0 |
| 1017 | 2/25/25 | 0 | 0 |
| 1018 | 2/25/25 | 0 | 0 |
| 1019 | 2/25/25 | 0 | 0 |
| 1020 | 2/25/25 | 0 | 0 |
| 1021 | 2/25/25 | 0 | 0 |
| 1022 | 2/25/25 | 0 | 0 |
| 1023 | 2/25/25 | 0 | 0 |
| 1024 | 2/25/25 | 0 | 0 |
| 1025 | 2/25/25 | 0 | 1 |
| 1026 | 2/25/25 | 0 | 0 |
| 1027 | 2/25/25 | 0 | 0 |
| 1028 | 2/25/25 | 0 | 0 |
| 1029 | 2/25/25 | 1 | 5 |
| 1030 | 2/25/25 | 0 | 0 |
| 1031 | 2/25/25 | 0 | 0 |
| 1032 | 2/25/25 | 0 | 0 |
| 1033 | 2/25/25 | 0 | 0 |
| 1034 | 2/25/25 | 0 | 0 |
| 1035 | 2/25/25 | 0 | 0 |
| 1036 | 2/25/25 | 0 | 0 |
| 1037 | 2/25/25 | 0 | 0 |
| 1038 | 2/26/25 | 0 | 1 |
| 1039 | 2/26/25 | 0 | 0 |
| 1040 | 2/26/25 | 0 | 1 |
| 1041 | 2/26/25 | 0 | 0 |
| 1042 | 2/26/25 | 0 | 0 |
| 1043 | 2/26/25 | 0 | 0 |
| 1044 | 2/26/25 | 0 | 0 |
| 1045 | 2/26/25 | 0 | 0 |
| 1046 | 2/26/25 | 0 | 0 |
| 1047 | 2/27/25 | 0 | 0 |
| 1048 | 2/27/25 | 0 | 0 |
| 1049 | 2/27/25 | 0 | 0 |
| 1050 | 3/6/25 | 0 | 0 |
| 1051 | 3/8/25 | 0 | 0 |
| 1052 | 3/8/25 | 0 | 0 |
| 1053 | 3/8/25 | 0 | 0 |
| 1054 | 3/8/25 | 0 | 0 |
| 1055 | 3/8/25 | 0 | 0 |
| 1056 | 3/8/25 | 0 | 0 |
| 1057 | 3/8/25 | 0 | 1 |
| 1058 | 3/8/25 | 0 | 0 |
| 1059 | 3/8/25 | 0 | 0 |
| 1060 | 3/8/25 | 0 | 0 |
| 1061 | 3/8/25 | 0 | 0 |
| 1062 | 3/8/25 | 0 | 0 |
| 1063 | 3/8/25 | 0 | 0 |
| 1064 | 3/8/25 | 0 | 0 |
- What problems do you encounter when experimenting with subtotal and array functions? – Coco Q. Commented Mar 9 at 12:04
- I have not found a way to do combine all requirements at once aka write a function that runs the descriptive statistics only on values > 0 as well as the function dynamically adjust to hidden rows. Meaning the combination of 2 + 3 in my initial post. – Paul Commented Mar 9 at 13:49
- So the statistics are on a different sheet I presume? – ServerS Commented Mar 9 at 13:59
- It would help focussing the question to the problem you encounter and share some sample data to demonstrate what you're trying, what it does and what you expect. You can share data as markdown table: tablesgenerator/markdown_tables#google_vignette so that not all inviduals willing to help you need to retype the data from an image to help you. – P.b Commented Mar 9 at 14:08
- @Paul Do you want two formulas? One for values > 0 and the other dynamic? Or do you want this in one formula? If one formula, do you have a checkbox or something to tell it which ( > 0 or dynamic) that you want at any given time? – ServerS Commented Mar 9 at 14:14
2 Answers
Reset to default 1Recommend shedding the SUBTOTAL function for the AGGREGATE function. You can program it to filter out all hidden rows. So, if you filter your dates, then filter out the zeros, it should give you what you want. Just be careful when you filter out zeroes for either meals or reflections, the filter may hide some non-zero rows for the other. I recommend building two separate sheets for both data. Picture shows the stats with dates filtered to 2025 and reflection count with zeroes filtered out.
=AGGREGATE(VSTACK(4,5,1,12,13), 5, C9:C423)
=AGGREGATE(VSTACK(4,5,1,12,13), 5, D9:D423)
You don't show what you want for an output, but here is one way using a single formula (uses 365 functions) which outputs the results in an array:
=LET(
d, A2:D416,
dates, CHOOSECOLS(d, 2),
filter, FILTER(d, (dates >= Start_Date) * (dates <= End_Date)),
RC, CHOOSECOLS(filter, 3),
MC, CHOOSECOLS(filter, 4),
RC_0, FILTER(RC, RC <> 0),
MC_0, FILTER(MC, MC <> 0),
stats, MAKEARRAY(
5,
4,
LAMBDA(r, c,
IF(
c = 1,
IFS(
r = 1,
MIN(RC),
r = 2,
MAX(RC),
r = 3,
AVERAGE(RC),
r = 4,
MEDIAN(RC),
r = 5,
MODE(RC)
),
IF(
c = 2,
IFS(
r = 1,
MIN(MC),
r = 2,
MAX(MC),
r = 3,
AVERAGE(MC),
r = 4,
MEDIAN(MC),
r = 5,
MODE(MC)
),
IF(
c = 3,
IFS(
r = 1,
MIN(RC_0),
r = 2,
MAX(RC_0),
r = 3,
AVERAGE(RC_0),
r = 4,
MEDIAN(RC_0),
r = 5,
MODE(RC_0)
),
IF(
c = 4,
IFS(
r = 1,
MIN(MC_0),
r = 2,
MAX(MC_0),
r = 3,
AVERAGE(MC_0),
r = 4,
MEDIAN(MC_0),
r = 5,
MODE(MC_0)
)
)
)
)
)
)
),
rwHdrs, {"MIN"; "MAX"; "MEAN"; "MEDIAN"; "MODE"},
colHdrs, {"Stat", "RC", "MC", "RC~0", "MC~0"},
VSTACK(colHdrs, HSTACK(rwHdrs, stats))
)
Given the data you posted, and the filter parameters seen in the screenshot, this is the output:
(The first pair of columns includes all the data; the second pair excludes zero's)
本文标签： Excel statistics for subtotal table valuesStack Overflow
版权声明：本文标题：Excel statistics for subtotal table values - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1744872790a2629730.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论