SOLVED

No funciona una fórmula

%3CLINGO-SUB%20id%3D%22lingo-sub-2386750%22%20slang%3D%22es-ES%22%3EA%20formula%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2386750%22%20slang%3D%22es-ES%22%3E%3CP%3EGood%20morning.%3C%2FP%3E%3CP%3EIn%20column%20C%20I%20have%20a%20formula%20to%20calculate%20an%20average%20over%20the%20cells%20where%20there%20are%20numbers.%20Since%20I'm%20in%20those%20cells%20I'm%20setting%20a%20formula%2C%20it%20doesn't%20calculate%20the%20average.%20When%20I%20take%20the%20formula%20off%20and%20put%20the%20numbers%20on%20it%2C%20it%20does%20calculate%20the%20average.%3C%2FP%3E%3CP%3ECan%20it%20be%20fixed%3F%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2386750%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-2386911%22%20slang%3D%22en-US%22%3ERe%3A%20No%20funciona%20una%20f%C3%B3rmula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2386911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063540%22%20target%3D%22_blank%22%3E%40MAIFG3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2387577%22%20slang%3D%22es-ES%22%3ERe%3A%20A%20formula%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2387577%22%20slang%3D%22es-ES%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%3EIn%20column%20C%20I%20have%20an%20average%20formula.%20In%20the%20other%20three%20I%20have%20a%20formula%20of%20'SI(%2C%20relative%20to%20column%20D.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2387800%22%20slang%3D%22en-US%22%3ERe%3A%20No%20funciona%20una%20f%C3%B3rmula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2387800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063540%22%20target%3D%22_blank%22%3E%40MAIFG3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOK%2C%20but%20what%20is%20the%20formula%20in%20column%20C%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388034%22%20slang%3D%22es-ES%22%3ERe%3A%20A%20formula%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388034%22%20slang%3D%22es-ES%22%3EYES.%20ERROR(AVERAGE(SUM(E7%3AG7%3BI7%3AK7%3B%20M7%3AO7%3B%20Q7%3AS7%3B%20U7%3AW7%3B%20Y7%3AAA7%3B%20AC7%3AAE7%3B%20AG7%3AAI7))%3B%22%20-%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388085%22%20slang%3D%22en-US%22%3ERe%3A%20No%20funciona%20una%20f%C3%B3rmula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063540%22%20target%3D%22_blank%22%3E%40MAIFG3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESUMA(E7%3AG7%3BI7%3AK7%3BM7%3AO7%3BQ7%3AS7%3BU7%3AW7%3BY7%3AAA7%3BAC7%3AAE7%3BAG7%3AAI7)%20returns%20a%20single%20value.%20There%20is%20no%20point%20in%20using%20PROMEDIO%20on%20a%20single%20number.%3C%2FP%3E%0A%3CP%3EDoes%20this%20do%20what%20you%20want%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSI.ERROR(PROMEDIO(E7%3AG7%3BI7%3AK7%3BM7%3AO7%3BQ7%3AS7%3BU7%3AW7%3BY7%3AAA7%3BAC7%3AAE7%3BAG7%3AAI7)%3B%22-%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Buenos días. 

En la columna C tengo una fórmula para calcular un promedio sobre las celdas donde hay números. Como en esas celdas tengo puesta una fórmula, no me calcula el promedio. Cuando le quito la fórmula y pongo yo los números, sí calcula el promedio.

¿Se puede solucionar? 

Gracias.

14 Replies

@MAIFG3 

What is the formula?

@Hans Vogelaar 

En la columna C tengo una fórmula de promedio. En la otras tres tengo una fórmula de =SI(, respecto a la columna D.

@MAIFG3 

OK, but what is the formula in column C?

=SI.ERROR(PROMEDIO(SUMA(E7:G7;I7:K7;M7:O7;Q7:S7;U7:W7;Y7:AA7;AC7:AE7;AG7:AI7));"-")

@MAIFG3 

SUMA(E7:G7;I7:K7;M7:O7;Q7:S7;U7:W7;Y7:AA7;AC7:AE7;AG7:AI7) returns a single value. There is no point in using PROMEDIO on a single number.

Does this do what you want?

 

=SI.ERROR(PROMEDIO(E7:G7;I7:K7;M7:O7;Q7:S7;U7:W7;Y7:AA7;AC7:AE7;AG7:AI7);"-")

No quiero que me sume los números, sino que saque el promedio de una serie de celdas. Además, no me funciona ninguna fórmula cuando se realiza sobre celdas donde hay fórmula.

@MAIFG3 

Have you tried

 

=SI.ERROR(PROMEDIO(E7:G7;I7:K7;M7:O7;Q7:S7;U7:W7;Y7:AA7;AC7:AE7;AG7:AI7);"-")

 

Make sure that C7 (the cell with the formula) is not formatted as text.

Sí, está en formato de número. Puede que no se pueda realizar una fórmula sobre celdas que contengan fórmulas.
Gracias.

@MAIFG3 

It should be possible. Could you attach a sample workbook?

Quieres que envíe el la hoja de excel?

@MAIFG3 

Yes please, you can attach it to a reply in this topic.

@Hans Vogelaar 

Buenos días.

Aquí la tienes.

best response confirmed by MAIFG3 (Occasional Contributor)
Solution

@MAIFG3 

Thank you! The problem is caused by the formulas in columns E:F, I:K and M:O.

For example in E7:

 

=SI.ERROR(SI(D7="MB";"10";SI(D7="B";"7,5";SI(D7="R";"5";SI(D7="M";"2,5";" "))));"")

 

The return values "10", "7,5" etc. are text because of the " ", not numbers! PROMEDIO ignores text values. It should be

 

=SI.ERROR(SI(D7="MB";10;SI(D7="B";7,5;SI(D7="R";5;SI(D7="M";2,5;""))));"")

 

See the attached version.

Muchas gracias. Llevaba tiempo intentando solucionar este problema y no encontraba la solución. Muy agradecido.