Why is Sumproduct formula showing #Value error?
I receive a #value error while using the sumproduct formulas. The main idea behind this document is to track the actuals on a daily basis using the following conditions:
 If the date is equal to the one specified in cell E2
 If the team name found in ranges e18:e46 or e50:e52 are the same as listed in the range C4:C8
 Sum the values found for those specific columns
Added the document for reference Excel File
I can't figure out what's wrong. Also to be specified that if i manually fill up random values in the table, the formula works.
1 answer

Becuase you have empty values in your return matrix. So you are multiplying with "0".
=SUMPRODUCT((E50:E52=C5)*(G17:AH17=E2),(G50:AH52))
See also questions close to this topic

How can I call the fields of this Object Array with JavaScript?
I would like to call these Arrays in JavaScript:
In json_encode:
{ "result": "success", "resultaQuantidade": 5, "1": { "contador": 1, "id": "2", "imagem_capa": "\/images\/boruto.jpg", "titulo_en": "Boruto: Naruto Next Generations", "status": "ESPERANDO", "total_ep": "12", "numero_ep_cp": "??", "tipo": "NOVEL", "nota": "0.00" }, "2": { "contador": 2, "id": "1", "imagem_capa": "\/images\/sword.jpg", "titulo_en": "Sword Art Online", "status": "COMPLETO", "total_ep": "24", "numero_ep_cp": "25", "tipo": "TV", "nota": "8" } }
In var_dump:
array(4) { ["result"]=> string(7) "success" ["resultaQuantidade"]=> int(5) [1]=> array(9) { ["contador"]=> int(1) ["id"]=> string(1) "2" ["imagem_capa"]=> string(18) "/images/boruto.jpg" ["titulo_en"]=> string(31) "Boruto: Naruto Next Generations" ["status"]=> string(9) "ESPERANDO" ["total_ep"]=> string(2) "12" ["numero_ep_cp"]=> string(2) "??" ["tipo"]=> string(5) "NOVEL" ["nota"]=> string(4) "0.00" } [2]=> array(9) { ["contador"]=> int(2) ["id"]=> string(1) "1" ["imagem_capa"]=> string(17) "/images/sword.jpg" ["titulo_en"]=> string(16) "Sword Art Online" ["status"]=> string(8) "COMPLETO" ["total_ep"]=> string(2) "24" ["numero_ep_cp"]=> string(2) "25" ["tipo"]=> string(2) "TV" ["nota"]=> string(1) "8" } }
JavaScript code:
$.ajax({ type: "GET", url: "/ajax/CarregarAnimesListaT.php", data: { 'offset': flag, 'limit': 3, 'usuarioid': usuarioid }, dataType: 'json', beforeSend: function(){ document.getElementById("loadingspacejs").style.display = "block"; }, success: function (data) { var dados = data; $("tbody tr").last().remove(); for(var i = 0; i < data.resultaQuantidade; i++){ $("tbody").append('<tr><td>'+data.dados[i].id+'</td><td>'+data.dados[i].name+'</td><td>'+data.dados[i].email+'</td><td>'+data.dados[i].senha+'</td></tr>'); document.getElementById("loadingspacejs").style.display = "none"; flag += 3; } var conta = $("tbody tr").length; if(conta == data.resultaQuantidade){ $("button.carregarmais").hide(); } }, error: function(errorThrown2) { swal("Opps!", "Ocorreu algum erro no sistema de carregamento de lista! [" + errorThrown2 + "]", "error"); } });

How are these values calculated?
I came across some HTML/JS Code that includes a function which has an array, which has 3 variables, x and y, then z which consists of x and y. The code is below:
<html> <head> <script> var x = 20; function unknown(a, b) { var twoDArray = [ [a, b], [a * (2 + b), a  b / 2] ]; return twoDArray; } var x = 1; var y = 2; var z = unknown(x, y); var output1 = z[1][0] + " " + z[0][1]; var output2 = z[1][1] + " " + z[0][0]; document.write(output1); document.write("<br>"); document.write(output2); </script> </head> <body> Hello world! </body> </html>
When opening the page inside the browser, the following values appear.
4 2 0 1
My question is, how are these values calculated from the above code?

How to pass the nested struct array to the function as a parameter?
I have a 2 structs named Class and Student. I want to calculate the average grade of the students by accessing Students[30] array in the Class struct.
Here is my code:
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <Math.h> typedef struct Student{ char name [20] ; int grade ; int number ; } Student; typedef struct Class{ char className[20] ; Student Students[30] ; } Class; double calculateAverageGrade(/* I want to pass the Students[30] array here */ ) { int i ; int sum = 0 ; for(i=0;i<3;i++) { } return sum/3 ; } int main() { Student s1 ={"John",75,758} ; Student s2 = {"Jack",85,123} ; Student s3 = {"Lisandra",50,321} ; Class c1 ={'\0'} ; strcpy(c1.className,"Physics") ; c1.Students[0] = s1 ; c1.Students[1] = s2 ; c1.Students[2] = s3 ; calculateAverageGrade(/*needs to take Students[30] array*/); return 0 ; }
I have tried somethings
Class Students[30]
,Class.Student Students[30]
like that but they did not work. 
Cell value change to be photo after be summed
i'm working with downloaded excel sheet from the internet.
on it i have a cell that make normal sum to three other cells
Then, the sum value of that cell( on the same cell )changed to a photo based on the result ( the result sum number not showing at all , only the photo ).
The threshold ( the value each image change to another one , is dynamic and depend on another cells , so if i change any of that cells , the value the image change from one to another also change ) .
i want to modify that calculation , but i can't find where he is making it
i tried to check if there is any hidden formula or taps , but there is nothing
can any one help me to find how he make it ??

is there a way to edit dynamic filter code to include an editable "quantity" column
I'm a bit new to excel's VBA, so bare with me. I'm trying to work on a new material spreadsheet in excel, the old one is not user friendly at all.
I tried a small code I found on a youtube video using a Combobox, however i'm not sure if this will work to the full extent.
The main issue is that I need to be able to type in the cells but with this current method I've used I cannot, because the code gets deleted when entering any kind of numbers.
I've tried editing the code to work specifically between the combobox and the "quantity" column. I figured if I could just get the quantity column to sort with the search it would work the way I intended
helper 1:copied from column G cell 3 to column G cell 370
=ROWS($G$3:G3)
helper 2: copied from column H cell 3 to column H cell 370
=IF(ISNUMBER(SEARCH($N$1,C3)),G3,"")
helper 3: copied from column I cell 3 to column I cell 370
=IFERROR(SMALL($H$3:$H$370,G3),"")
New materials list created from code but not editable:
=IFERROR(INDEX($A$3:$F$367,$I3,COLUMNS($L$3:L3)),"")
this code is copied from colum L cell 3 all the way down to column p cell 370
wanting to be able to keep the way the material sheet filters when I type what i'm looking for but I need to be able to input a quantity once i've found what i'm looking for.

I have a data source table that may have or may not have certain columns
I have a table of information that I have used Sumifs to get the data i need, but sometimes a part of the data may drop out ie. apple may be there one month but not the next. When I get the table without the same number of columns as the month before the sumifs don't pull the correct data with out manipulating the source document or my formulas. I am sure that there is a way around this using an index match formula but I haven't had success. I have sample data if someone can help? Thank you.

"ValueError: Unknown layer: ... " when calling copy.deepcopy(network) using Tensorflow
I am currently designing a NoisyNet in Tensorflow, for which I need to define a custom layer. When copying a model containing that custom layer, python raises the error
ValueError: Unknown layer: NoisyLayer
. The implementation of the layer is provided here.The goal is to copy one network creating a second instance of it. For that purpose, I use the command
net_copy = copy.deepcopy(net_original)
, which works as long as I don't include the custom layer referred to above in the model to be copied. I saw that for saving and loading there exists a way of specifying custom attributes (such as custom layers), but yet I couldn't find a similar command that would work forcopy.deepcopy()
, where copy is imported viaimport copy
.I am using Tensorflow 1.12.0 in Python3.
Again, the custom layer is provided under the link above. The network that uses the custom layer looks as follows:
class Network: def __init__(self, actionspace_size, learning_rate, gradient_momentum, gradient_min): frames_input = keras.layers.Input((84, 84, 4)) actions_input = keras.layers.Input((actionspace_size,)) conv1 = keras.layers.Conv2D(16, (8, 8), strides=(4, 4), activation="relu")(frames_input) conv2 = keras.layers.Conv2D(32, (4, 4), strides=(2, 2), activation="relu")(conv1) flattened = keras.layers.Flatten()(conv2) # NoisyNet hidden = NoisyLayer(activation=tf.nn.relu)(inputs=flattened, resample_noise_flag=True) output = NoisyLayer(in_shape=(1,256), out_units=actionspace_size)(inputs=hidden, resample_noise_flag=True) filtered_output = keras.layers.merge.Multiply()([output, actions_input]) self.model = keras.models.Model(inputs=[frames_input, actions_input], outputs=filtered_output) self.model.compile(loss='mse', optimizer=keras.optimizers.RMSprop(lr=learning_rate, rho=gradient_momentum, epsilon=gradient_min))
When calling
q_net = Network(actionspace_size, learning_rate, gradient_momentum, gradient_min). target_net = copy.deepcopy(q_net)
the following error arises:
Traceback (most recent call last): File "DQN_tf_NoisyNet.py", line 315, in <module> main() File "DQN_tf_NoisyNet.py", line 252, in main target_net = copy.deepcopy(q_net) File "/usr/lib/python3.5/copy.py", line 182, in deepcopy y = _reconstruct(x, rv, 1, memo) File "/usr/lib/python3.5/copy.py", line 299, in _reconstruct y.__setstate__(state) File "/usr/local/lib/python3.5/distpackages/keras/engine/network.py", line 1266, in __setstate__ model = saving.unpickle_model(state) File "/usr/local/lib/python3.5/distpackages/keras/engine/saving.py", line 435, in unpickle_model return _deserialize_model(f) File "/usr/local/lib/python3.5/distpackages/keras/engine/saving.py", line 225, in _deserialize_model model = model_from_config(model_config, custom_objects=custom_objects) File "/usr/local/lib/python3.5/distpackages/keras/engine/saving.py", line 458, in model_from_config return deserialize(config, custom_objects=custom_objects) File "/usr/local/lib/python3.5/distpackages/keras/layers/__init__.py", line 55, in deserialize printable_module_name='layer') File "/usr/local/lib/python3.5/distpackages/keras/utils/generic_utils.py", line 145, in deserialize_keras_object list(custom_objects.items()))) File "/usr/local/lib/python3.5/distpackages/keras/engine/network.py", line 1022, in from_config process_layer(layer_data) File "/usr/local/lib/python3.5/distpackages/keras/engine/network.py", line 1008, in process_layer custom_objects=custom_objects) File "/usr/local/lib/python3.5/distpackages/keras/layers/__init__.py", line 55, in deserialize printable_module_name='layer') File "/usr/local/lib/python3.5/distpackages/keras/utils/generic_utils.py", line 138, in deserialize_keras_object ': ' + class_name) ValueError: Unknown layer: NoisyLayer
I know that the network itself is not the problem (neither the deepcopy approach), since both work fine again as soon as I replace the NoisyLayers (custom) by standard dense layers.
Does anyone know how to copy a Tensorflow model including custom layers? Thanks in advance!

How to fix "ValueError: Operands could not be broadcast together with shapes (2592,) (4,)" in Tensorflow?
I am currently designing a NoisyNet layer, as proposed here: "Noisy Networks for Exploration", in Tensorflow and get the dimensionality error as indicated in the title, while the dimensions of the two tensors to be multiplied elementwise in line
filtered_output = keras.layers.merge.Multiply()([output, actions_input])
should (in principle) be compatible with each other according to the printed output when printing the dimensions of both tensors involved,filtered_output
andactions_input
, where both tensors seem to be of dimensionshape=(1, 4)
.I am using Tensorflow 1.12.0 in Python3.
The relevant code looks as follows:
import numpy as np import tensorflow as tf import keras class NoisyLayer(keras.layers.Layer): def __init__(self, in_shape=(1,2592), out_units=256, activation=tf.identity): super(NoisyLayer, self).__init__() self.in_shape = in_shape self.out_units = out_units self.mu_interval = 1.0/np.sqrt(float(self.out_units)) self.sig_0 = 0.5 self.activation = activation self.assign_resampling() def build(self, input_shape): # Initializer self.mu_initializer = tf.initializers.random_uniform(minval=self.mu_interval, maxval=self.mu_interval) # Muinitializer self.si_initializer = tf.initializers.constant(self.sig_0/np.sqrt(float(self.out_units))) # Sigmainitializer # Weights self.w_mu = tf.Variable(initial_value=self.mu_initializer(shape=(self.in_shape[1], self.out_units), dtype='float32'), trainable=True) # (1,2592)x(2592,4) = (1,4) self.w_si = tf.Variable(initial_value=self.si_initializer(shape=(self.in_shape[1], self.out_units), dtype='float32'), trainable=True) # Biases self.b_mu = tf.Variable(initial_value=self.mu_initializer(shape=(self.in_shape[0], self.out_units), dtype='float32'), trainable=True) self.b_si = tf.Variable(initial_value=self.si_initializer(shape=(self.in_shape[0], self.out_units), dtype='float32'), trainable=True) def call(self, inputs, resample_noise_flag): if resample_noise_flag: self.assign_resampling() # Putting it all together self.w = tf.math.add(self.w_mu, tf.math.multiply(self.w_si, self.w_eps)) self.b = tf.math.add(self.b_mu, tf.math.multiply(self.b_si, self.q_eps)) return self.activation(tf.linalg.matmul(inputs, self.w) + self.b) def assign_resampling(self): self.p_eps = self.f(self.resample_noise([self.in_shape[1], 1])) self.q_eps = self.f(self.resample_noise([1, self.out_units])) self.w_eps = self.p_eps * self.q_eps # Cartesian product of input_noise x output_noise def resample_noise(self, shape): return tf.random.normal(shape, mean=0.0, stddev=1.0, seed=None, name=None) def f(self, x): return tf.math.multiply(tf.math.sign(x), tf.math.sqrt(tf.math.abs(x))) frames_input = tf.ones((1, 84, 84, 4)) # Toy input conv1 = keras.layers.Conv2D(16, (8, 8), strides=(4, 4), activation="relu")(frames_input) conv2 = keras.layers.Conv2D(32, (4, 4), strides=(2, 2), activation="relu")(conv1) flattened = keras.layers.Flatten()(conv2) actionspace_size = 4 # NoisyNet hidden = NoisyLayer(activation=tf.nn.relu)(inputs=flattened, resample_noise_flag=True) output = NoisyLayer(in_shape=(1,256), out_units=actionspace_size)(inputs=hidden, resample_noise_flag=True) actions_input = tf.ones((1,actionspace_size)) print('hidden:\n', hidden) print('output:\n', output) print('actions_input:\n', actions_input) filtered_output = keras.layers.merge.Multiply()([output, actions_input])
The output, when I run the code, looks as follows:
hidden: Tensor("noisy_layer_5/Relu:0", shape=(1, 256), dtype=float32) output: Tensor("noisy_layer_6/Identity:0", shape=(1, 4), dtype=float32) actions_input: Tensor("ones_5:0", shape=(1, 4), dtype=float32)  ValueError Traceback (most recent call last) <ipythoninput4f6df621eacab> in <module>() 68 print('actions_input:\n', actions_input) 69 > 70 filtered_output = keras.layers.merge.Multiply()([output, actions_input]) 2 frames /usr/local/lib/python3.6/distpackages/keras/layers/merge.py in _compute_elemwise_op_output_shape(self, shape1, shape2) 59 raise ValueError('Operands could not be broadcast ' 60 'together with shapes ' + > 61 str(shape1) + ' ' + str(shape2)) 62 output_shape.append(i) 63 return tuple(output_shape) ValueError: Operands could not be broadcast together with shapes (2592,) (4,)
Particularly, I am wondering where the number
2592
inOperands could not be broadcast together with shapes (2592,) (4,)
comes from, since the number coincides with the length of the flattened input tensorflattened
to the first noisy layer, but is as it seems to me not part of the output dimension of the second noisy layeroutput
anymore, which in turn serves as the input to the erroneous line indicated above.Does anyone know what's going wrong?
Thanks in advance, Daniel

pyquaternion: ValueError: Unexpected number of elements in sequence
Using the pyquaternion module I want to obtain a quaternion representation of a 3x3 rotation matrix. When executed, it returns
ValueError: Unexpected number of elements in sequence. Got: 3, Expected: 4.
. According to the documentation, instantiating the quaternion with a 3x3 matrix should work or maybe I'm misinterpreting something.To test this, I found this method for random rotation matrix generation. As stated above, I only receive this error message.
import numpy as np from pyquaternion import Quaternion def rand_rotation_matrix(deflection=1.0, randnums=None): """ Creates a random rotation matrix. deflection: the magnitude of the rotation. For 0, no rotation; for 1, competely random rotation. Small deflection => small perturbation. randnums: 3 random numbers in the range [0, 1]. If `None`, they will be autogenerated. """ # from http://www.realtimerendering.com/resources/GraphicsGems/gemsiii/rand_rotation.c if randnums is None: randnums = np.random.uniform(size=(3,)) theta, phi, z = randnums theta = theta * 2.0*deflection*np.pi # Rotation about the pole (Z). phi = phi * 2.0*np.pi # For direction of pole deflection. z = z * 2.0*deflection # For magnitude of pole deflection. # Compute a vector V used for distributing points over the sphere # via the reflection I  V Transpose(V). This formulation of V # will guarantee that if x[1] and x[2] are uniformly distributed, # the reflected points will be uniform on the sphere. Note that V # has length sqrt(2) to eliminate the 2 in the Householder matrix. r = np.sqrt(z) Vx, Vy, Vz = V = ( np.sin(phi) * r, np.cos(phi) * r, np.sqrt(2.0  z) ) st = np.sin(theta) ct = np.cos(theta) R = np.array(((ct, st, 0), (st, ct, 0), (0, 0, 1))) # Construct the rotation matrix ( V Transpose(V)  I ) R. M = (np.outer(V, V)  np.eye(3)).dot(R) return M rotation1 = rand_rotation_matrix() rotation2 = rand_rotation_matrix() print(Quaternion(rotation1.dot(rotation2.T)))