Simplifying a formula for the Normalised Gibbs Entropy

[excel_lisa]

Hi,

I want to write a formula that computes the normalised Gibbs entropy, which is computed as follows:

The values that I want to test this for are located in column Z, AD, AH, AL, AP, AT, AX, ..... CL etc. so 3 columns are skipped every time.
I want to test for values 0 - 10.
I already wrote a code to test this formula for value '1', which is as follows:
=(COUNTIFS(Z3;1;AD3;1;AH3;1;AL3;1;AP3;1;AT3;1;AX3;1;BB3;1;BF3;1;BJ3;1;BN3;1;BR3;1;BV3;1;BZ3;1;CD3;1;CH3;1;CL3;1)/U3)*(IFERROR(LN(COUNTIFS(Z3;1;AD3;1;AH3;1;AL3;1;AP3;1;AT3;1;AX3;1;BB3;1;BF3;1;BJ3;1;BN3;1;BR3;1;BV3;1;BZ3;1;CD3;1;CH3;1;CL3;1)/U3);0))/LN(U3)
The only way I can think of computing the normalised Gibbs entropy is repeating this formula for each value from 0 to 10 as a criterion in the COUNTIFS function, and then compute the Sum of all outcomes and multiply by -1.
My Dataframe is 23000 rows so I think my Excel will also crash if I try to execute such a large formula.
Is there a smarter way to do this so that I won't have to write this formula 10 more times and so that the formula is more simple and compact?

Thank you in advance!
Lisa

P.S. If you know a way to do this in Python, please let me know as well

 

[JamesCanale]

My thermo is super rusty (30 years since I thought about Gibbs for maybe a week).
In your system there are as many as 17 states, and the probabilities of any state is in Z, AD, AH, ...CL?
And U3 has the N value which is some function of particles and Boltzmann's constant???
Wouldn't all the pi add up to 1? Not all be 1.
If you have 23,000 rows, this formula will run slow.

MrExcelPlayground2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	N	p1	p2	p3	p4	p5	p6	p7	p8	p9	p10	p11	p12	p13	p14	p15	p16	p17	P	G
2	3	0.015	0.014	0.089	0.088	0.049	0.032	0.058	0.023	0.079	0.055	0.099	0.025	0.082	0.041	0.027	0.090	0.133	1	2.4250313
3	4	0.037	0.031	0.076	0.097	0.027	0.002	0.092	0.086	0.003	0.082	0.087	0.079	0.060	0.066	0.040	0.088	0.048	1	1.91814862
4	5	0.300	0.042	0.036	0.020	0.086	0.073	0.013	0.006	0.065	0.042	0.092	0.030	0.007	0.018	0.085	0.049	0.036	1	1.49140582
5	6	0.011	0.200	0.067	0.053	0.037	0.072	0.015	0.021	0.094	0.073	0.065	0.093	0.070	0.026	0.067	0.030	0.008	1	1.43855603
Sheet17

Cell Formulas
Range	Formula
S2:S5	S2	=SUM(B2:R2)
T2:T5	T2	=-SUM((B2:R2)*LN((B2:R2))/LN(A2))

 

[excel_lisa]

Hi,

Thank you for your answer!
I actually use the Normalised Gibbs Entropy for something else, namely to measure the diversity of educational backgrounds in a team.
Columns Z, AD, AH, ...CL all have a number from 0 to 10, which represents an educational field. Column U has the team size (number of team members).
I need to compute the proportion pi of team members that have a certain educational background, and then multiply by ln(pi) and divide by ln(N), in which N is the team size.
I did did using the above mentioned countif formula. However, I will need to repeat this 10 times for each educational background to compute the normalised Gibbs Entropy per team.
Therefore I was wondering whether there is an easier way to do this.

Hope this is a clear explanation for you, thanks!

 

[JamesCanale]

I don't think I'll understand what you are trying to calculate, but you can use arrays of 1 to 10 and sum all the countifs up.

=SUM(COUNTIFS(Z3,{1,2,3,4,5,6,7,8,9,10},AD3,{1,2,3,4,5,6,7,8,9,10},....))