matrix multiplication and sum- single formula

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696
Hello all,

This problem would be difficult to explain in words, so please have a look at this sheet
Book1
ABCDEFGHIJKLMNOPQ
1V111.800V10-0.019Zin10.846Zout10.700W10.276
20.48V122.297V20-1.859Zin2-0.757Zout20.319W21.627W0-0.240Ypred0.452
3V131.992V30-1.948Zin3-0.991Zout30.271W3-0.078
4
5Zin1=0.48*1.800+(-0.019)Zout1=1/(1+exp(-Zin1)
6Zin2=0.48*2.297+(-1.859)Zout2=1/(1+exp(-Zin2)Ypred=Zout1*0.276+Zout2*1.327+Zout3*(-0.078)+(-0.240)
7Zin3=0.48*1.992+(-1.948)Zout3=1/(1+exp(-Zin3)
Sheet1


I'm interested in calculating the last value "Ypred". Therefore, I need a single formula which would calculate the products and sums as explained in the calculations. I want to eliminate intermediate steps.

Thanks for your help.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perhaps:

=SUMPRODUCT(I1:I3,K1:K3)+M2
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

=SUM(1/(1+EXP(-($A$2*C1:C3+E1:E3)))*K1:K3)+M2 array entered (ctrl, shift, enter)
or
=SUMPRODUCT(1/(1+EXP(-($A$2*C1:C3+E1:E3))),K1:K3)+M2

if you want to eliminate the formulas in column I.


Tony
 

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696
Thanks for your replies.

acw, both formuals work great, thanks. I was trying to accomplish the same thing but I wasn't able to put the 1/(1+exp(-x) thing properly. Man, you guys know some array formulas, which are hard to catch up.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,909
Messages
5,574,983
Members
412,630
Latest member
Eireangel
Top