SOLVED

no funciona fórmula de suma para un rango de datos

%3CLINGO-SUB%20id%3D%22lingo-sub-1746999%22%20slang%3D%22es-ES%22%3Edoesn't%20work%20sum%20formula%20for%20a%20range%20of%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1746999%22%20slang%3D%22es-ES%22%3E%3CP%3Ewhen%20I%20sum%20a%20range%20of%20data%20and%20modify%20any%20of%20this%20data%2C%20the%20result%20is%20not%20changed.%20It%20is%20in%20automatic%20calculation%20and%20the%20cells%20are%20formatted%20as%20numeric.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1746999%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EFormulas%20and%20functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747110%22%20slang%3D%22en-US%22%3ERe%3A%20no%20funciona%20f%C3%B3rmula%20de%20suma%20para%20un%20rango%20de%20datos%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F820972%22%20target%3D%22_blank%22%3E%40letlira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20Formulas%20tab%20of%20the%20ribbon%2C%20make%20sure%20that%20Calculation%20Options%20is%20set%20to%20Automatic.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEn%20la%20pesta%C3%B1a%20F%C3%B3rmulas%20de%20la%20cinta%2C%20aseg%C3%BArese%20de%20que%20'Opciones%20para%20el%20c%C3%A1lculo'%20est%C3%A9%20configurado%20en%20Autom%C3%A1tico.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747131%22%20slang%3D%22en-US%22%3ERe%3A%20no%20funciona%20f%C3%B3rmula%20de%20suma%20para%20un%20rango%20de%20datos%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!!%20It's%20set%20to%20Automatic%2C%20but%20it%20still%20doesn't%20work.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747211%22%20slang%3D%22en-US%22%3ERe%3A%20no%20funciona%20f%C3%B3rmula%20de%20suma%20para%20un%20rango%20de%20datos%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747211%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F820972%22%20target%3D%22_blank%22%3E%40letlira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20small%20sample%20workbook%20without%20sensitive%20information%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747311%22%20slang%3D%22en-US%22%3ERe%3A%20no%20funciona%20f%C3%B3rmula%20de%20suma%20para%20un%20rango%20de%20datos%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Thank%20you!!%20Here%20it%20is%2C%20if%20you%20change%20the%20cells%20highlighted%20with%20yellow%2C%20there%20is%20no%20change%20in%20the%20green%20cell%20value.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747313%22%20slang%3D%22en-US%22%3ERe%3A%20no%20funciona%20f%C3%B3rmula%20de%20suma%20para%20un%20rango%20de%20datos%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F820972%22%20target%3D%22_blank%22%3E%40letlira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20P68%20is%3C%2FP%3E%0A%3CP%3E%3DL68-M68-N68-O68%3C%2FP%3E%0A%3CP%3Eand%20that%20in%20Q68%20is%3C%2FP%3E%0A%3CP%3E%3DSUM(M68%3AP68)%3C%2FP%3E%0A%3CP%3Ewhich%20is%20equivalent%20to%3C%2FP%3E%0A%3CP%3E%3DM68%2BN68%2BO68%2BP68.%3C%2FP%3E%0A%3CP%3ESo%20the%20result%20is%3C%2FP%3E%0A%3CP%3E%3DM68%2BN68%2BO68%2BL68-M68-N68-O68%3C%2FP%3E%0A%3CP%3Eand%20that%20is%20equivalent%20to%3C%2FP%3E%0A%3CP%3E%3DL68%3C%2FP%3E%0A%3CP%3ESo%20the%20value%20in%20Q68%20is%20equal%20to%20that%20of%20L68%2C%20and%20it%20not%20dependent%20on%20M68%2C%20N68%20and%20O68!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

cuando realizo una suma de un rango de datos y modifico alguno de estos datos, no se cambia el resultado. Está en cálculo automático y las celdas tienen formato numérico. 

 

8 Replies

@letlira 

On the Formulas tab of the ribbon, make sure that Calculation Options is set to Automatic.

 

En la pestaña Fórmulas de la cinta, asegúrese de que 'Opciones para el cálculo' esté configurado en Automático.

Highlighted

@Hans Vogelaar 

Thank you!! It's set to Automatic, but it still doesn't work. 

Highlighted

@letlira 

Could you attach a small sample workbook without sensitive information? Thanks in advance.

Highlighted

@Hans Vogelaar  Thank you!! Here it is, if you change the cells highlighted with yellow, there is no change in the green cell value. 

Highlighted

@letlira 

The formula in P68 is

=L68-M68-N68-O68

and that in Q68 is

=SUM(M68:P68)

which is equivalent to

=M68+N68+O68+P68.

So the result is

=M68+N68+O68+L68-M68-N68-O68

and that is equivalent to

=L68

So the value in Q68 is equal to that of L68, and it not dependent on M68, N68 and O68!

Highlighted

@Hans Vogelaar  the value that doesn´t  change is S68, that cell is dependent on N68, O68 and P68. If I change O68, the value of S68 does not change. 

Highlighted
Best Response confirmed by letlira (Occasional Contributor)
Solution

@letlira 

The formula in S68 is =SUM(N68:P68) or =N68+O68+P68

The formula in P68 is =L68-M68-N68-O68

So the formula in S68 is equivalent to

=N68+O68+L68-M68-N68-O68

and that is equivalent to

=L68-M68

So S68 depends on L68 and M68, but not on N68 or O68.

Highlighted
Ohhh of course!! Sorry!!! Thank you!! I got it!!!