Sobre este ejercicio
Un conjunto de datos de 150 filas en una hoja, 3 celdas de respuesta, unos 8 minutos. Se corrige en tu navegador: el valor y la fórmula que lo produce, con comentarios que te dicen qué arreglar.
Cómo resolverlo
- I3
=PERCENTILE.INC($E$2:$E$151,1-$I$2) - F2
=AND(ISNUMBER(E2),E2>=$I$3) - I4
=COUNTIF(F2:F151,TRUE)
PERCENTILE.INC(range, 0.9) returns the value that 90% of the numbers in the range stay below, so every store at or above it is in the top 10%. Writing it as 1-I2 keeps the share in one cell: change I2 and the cut-off, the flags and the count all follow. The flag needs two tests: ISNUMBER(E2), because Excel ranks any text above every number and "closed" would otherwise pass, and E2>=$I$3, because a store exactly on the cut-off belongs to the group — here two stores tie there. Excel's built-in Top 10% rule makes the same decision for the colours: it ranks only the numbers and keeps ties at the cut-off.
Pitfall 1: text in a number column — a comparison such as "closed">=100000 is TRUE in Excel (text sorts after numbers), so a plain E2>=$I$3 flags 7 closed stores; PERCENTILE.INC, LARGE and the built-in rule simply skip text. Pitfall 2: > instead of >= — the cut-off is $200,285, the sales of both tied stores, so > leaves 13 stores instead of 15. Pitfall 3: PERCENTILE.EXC ranks at (n + 1)·p instead of (n − 1)·p + 1 and gives other cut-offs when the share changes; this task follows PERCENTILE.INC (the same as the old PERCENTILE). Alternatives: =IF(ISNUMBER(E2),RANK.EQ(E2,$E$2:$E$151)<=ROUNDDOWN(COUNT($E$2:$E$151)*$I$2,0),FALSE) flags by rank (both tied stores share rank 14) — the IF matters, because AND evaluates every argument and RANK.EQ of "closed" is #VALUE!, which AND passes on. =LARGE($E$2:$E$151,ROUNDDOWN(COUNT($E$2:$E$151)*$I$2,0)) is the cut-off the built-in rule uses — the 14th largest value; when the count of numbers is a multiple of 10 it selects the same stores as PERCENTILE.INC (here it is even the same value, because of the tie). In a formula-based rule the helper is optional: select E2:E151, New Rule → "Use a formula to determine which cells to format" and enter =AND(ISNUMBER($E2),$E2>=$I$3) (Windows: Alt, H, L, N), which also lets you colour the whole row (select A2:F151 instead).
Otras formas válidas
- =AND(ISNUMBER(E2),E2>=PERCENTILE.INC($E$2:$E$151,1-$I$2)) — the cut-off inside the flag instead of in I3
- =PERCENTILE($E$2:$E$151,1-$I$2) — the older name of PERCENTILE.INC, same result
- =IF(ISNUMBER(E2),E2>=$I$3,FALSE) — the same flag with IF
- =COUNTIF($E$2:$E$151,">="&$I$3) — counts the top group straight from the sales (COUNTIF skips text for a ">=" criterion)
Errores frecuentes
Writing =E2>=$I$3 without ISNUMBER: Excel treats the text "closed" as larger than any number, so rows 40, 42, 56… are flagged TRUE and the count comes out 22 instead of 15.
Cómo corregimos
Se acepta cualquier fórmula que produzca el resultado correcto, salvo que la tarea nombre una función: entonces otra función obtiene media puntuación. Comparamos los valores con una pequeña tolerancia y, en el texto, ignoramos mayúsculas y espacios. Un número correcto escrito a mano en lugar de una fórmula obtiene media puntuación. Las tareas de varias celdas obtienen la puntuación completa con el 100% de las celdas correctas y media puntuación desde el 50%. Cuando importa, también cambiamos una celda de entrada en una copia de tu hoja y comprobamos que tu fórmula se adapta.
Respuestas calculadas con las reglas de Excel 365 y comprobadas en nuestro corrector el 16 de noviembre de 2026. Autor: GridBelt team.