excel - How does the SUMPRODUCT command works in this example? -

-

the following code allows me determine distinct values in pivot table in excel:

=sumproduct(($a$a:$a2=a2)*($b$2:$b2=b2))

see also: simple pivot table count unique values

the code runs fine. however, can me understand how code works?

you write: following code allows me determine distinct values in pivot table in excel

no. formula alone not that. read on explanation of does.

there's typo in formula. should be

=sumproduct(($a$2:$a2=a2)*($b$2:$b2=b2))

see difference?

the formula starts in row 2 , copied down. in each row, $a$2 reference , $b$2 reference stay same. $ signs make them absolute references. relative references $a2 , a2 change row numbers when copied down, in row 3 a2 change a3 , b2 change b3. in next row a4 , b4, , on.

you may want create sample scenario data similar in thread link to. use "evaluate formula" tool on formulas ribbon see step step calculated. formula evaluates inside out. let's assume formula has been copied down row 5 , looking at

=sumproduct(($a$2:$a5=a5)*($b$2:$b5=b5))

($a$2:$a5=a5) bit compares cells a2 a5 value in a5. result array of 4 values, either true or false. next bit ($b$2:$b5=b5) returns array of true or false values.

enter image description here

these 2 arrays multiplied , result array of 1 or 0 values. each array has same number of values.

the first value of first array multiplied first value of second array. (see red arrows)

the second value of first array multiplied second value of second array. (see blue arrows)

and on.

true * true return 1, else return 0. result of multiplication is:

enter image description here

the nature of sumproduct function sum result of multiplications (the product), does.

this function alone not @ establish distinct values in excel. in thread link to, sumproduct wrapped in if statement , distinct values identified.

=if(sumproduct(($a$2:$a2=a2)*($b$2:$b2=b2))>1,0,1)

in plain words: if combination of value in column of current row , column b of current row has appeared above, return zero, otherwise, return 1.

this marks distinct values of combined columns , b.


Comments

Post a Comment

Popular posts from this blog

python - argument must be rect style object - Pygame -

-
disclaimer: know other people on site have had error it's been in different contexts , fixes don't seem work here. hey stackoverflow, i've been making sideways space invaders game, ship follows mouse's y position stays on left, , clicking shoots bullet. enemies spawn randomly right , fly left, , objects removed once each side of screen. works fine, when tried implement collision detection see when shot hits enemy, error: typeerror: argument must rect style object. it points line "return hitbox.colliderect(target.rect)". try , work out causing made 'print' lines below see 'target.rect' (and whether proper rect object), , seemed same style shot's own rect, i'm lost why colliderect doesn't accept it. i've annotated pretty everything, sorry if it's insult intelligence should make things easier. class causes problems: class gameobject: def __init__(self, image, height, speed): self.speed = speed self.image...
Read more

c++ - Qt setGeometry: Unable to set geometry -

-
why? setgeometry: unable set geometry 22x22+320+145 on qwidgetwindow/'widgetclasswindow'. resulting geometry: 116x22+320+145 (frame: 8, 30, 8, 8, custom margin: 0, 0, 0, 0, minimum size: 22x22, maximum size: 16777215x16777215). the project is: project.pro qt += core gui greaterthan(qt_major_version, 4): qt += widgets target = untitled5 template = app sources += main.cpp\ widget.cpp headers += widget.h widget.h #ifndef widget_h #define widget_h #include <qwidget> class widget : public qwidget { q_object public: explicit widget(qwidget *parent = 0); ~widget(); private: }; #endif // widget_h widget.cpp #include "widget.h" #include <qvboxlayout> widget::widget(qwidget *parent) : qwidget(parent) { qvboxlayout *vlayout = new qvboxlayout(this); } widget::~widget() { } main.cpp #include "widget.h" #include <qapplication> int main(int argc, char *argv[]) { qapplication a...
Read more

webrtc - Which ICE candidate am I using and why? -

-
[questions in bold below] i have setup kurento media server 5.1.3 in datacenter behind firewall running os ubuntu 14.04. has 2 network cards: 222.222.222.222 (eth0 - private ip) 111.111.111.111 (eth1 - public ip) attached below sdp (setremotedescription) when browser connected kurento media server type: answer, sdp: v=0 o=- 5487318114793304426 0 in ip4 0.0.0.0 s=kurento media server c=in ip4 0.0.0.0 t=0 0 a=group:bundle audio video m=audio 59068 rtp/savpf 111 0 c=in ip4 111.111.111.111 a=rtpmap:111 opus/48000/2 a=rtpmap:0 pcmu/8000 a=sendrecv a=rtcp:59068 in ip4 111.111.111.111 a=rtcp-mux a=ssrc:669011897 cname:user39019747@host-6e83e4c2 a=extmap:3 http://www.webrtc.org/experiments/rtp-hdrext/abs-send-time a=mid:audio b=as:20 a=ice-ufrag:ymdk a=ice-pwd:lylifk5ueqzpwm91ddj37e a=fingerprint:sha-256 ff:0f:81:8c:41:4e:b4:b6:c6:d8:36:f3:d6:5f:09:fd:5f:af:13:b3:9d:fc:12:66:ac:f3:56:d6:5b:0a:73:5d a=candidate:1 1 udp 2013266431 111.111.111.111 55239 typ host a=candidate:2 1 udp...
Read more