User:Oleg Kobchenko/Array Spreadsheet

Array Spreadsheet Example

Frequency table D is calculated from data B and buckets C. It is also simulated with direct calculation in E.

Formulas

#!CSV ;
B;C;D;E;F
=FLOOR(RAND()*8,1);2;={FREQUENCY(B3:B19,C3:C6)};=COUNTIF($B$3:$B$19,"<=2")-F3;0
=FLOOR(RAND()*8,1);4;={FREQUENCY(B3:B19,C3:C6)};=COUNTIF($B$3:$B$19,"<=4")-F4;=E3+F3
=FLOOR(RAND()*8,1);6;={FREQUENCY(B3:B19,C3:C6)};=COUNTIF($B$3:$B$19,"<=6")-F5;=E4+F4
=FLOOR(RAND()*8,1);8;={FREQUENCY(B3:B19,C3:C6)};=COUNTIF($B$3:$B$19,"<=8")-F6;=E5+F5
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;=SUM(D3:D9);;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
=FLOOR(RAND()*8,1);;;;
;;;;
=COUNT(B3:B20);;;;

;;Result;;

6;2;10;10;0
4;4;5;5;10
5;6;2;2;15
4;8;0;0;17
4;;;;
4;;;;
1;;;;
2;;17;;
1;;;;
0;;;;
1;;;;
2;;;;
0;;;;
3;;;;
2;;;;
1;;;;
2;;;;

17;;;;