Sobre este ejercicio
Un conjunto de datos de 81 filas en una hoja, una celda de respuesta, unos 6 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
- F3
=IF(OR(E3>=$B$1,D3="Member"),"Free","Standard")
OR is the mirror image of AND: OR(E3>=$B$1,D3="Member") is TRUE when at least one of the two comparisons holds, and FALSE only when both fail. IF turns that into the Free / Standard label. The dollar signs in $B$1 matter because the formula is copied down: the row numbers in E3 and D3 should follow each order, but the threshold must keep pointing at B1 on every row.
Pitfall 1: =IF(OR(E3>=B1,…) without the $ signs looks right on row 3 and breaks from row 4 on, because B1 slides to B2 (a text caption) and then to empty cells — every comparison against them is TRUE or FALSE for the wrong reason. Pitfall 2: hard-coding the threshold (E3>=75) gives correct flags today and silently wrong ones after the next promotion; the grader changes B1 and expects the flags to follow. Pitfall 3: text comparisons are case-insensitive but not space-insensitive — "Member " with a trailing space from an export is not "Member"; TRIM the column first when the data is dirty. Alternatives: =IF((E3>=$B$1)+(D3="Member")>0,…) adds the booleans (any 1 means at least one TRUE); a lookup table of customer types with their own thresholds scales better when there are more than two tiers. Reading the result: COUNTIF(F3:F82,"Free") on top of this column is the first question marketing will ask — that is the next topic in this track.
Otras formas válidas
- =IF(OR(D3="Member",E3>=$B$1),"Free","Standard") — same conditions in the other order
- =IF(AND(E3<$B$1,D3<>"Member"),"Standard","Free") — the rule expressed through its only failing case
- =IF(OR(E3>=B$1,D3="Member"),"Free","Standard") — a mixed reference (B$1) is enough when you only fill down
Errores frecuentes
Leaving B1 relative: after the fill, F4 compares against B2 and F5 against B3, so most rows read Standard. Lock the threshold as $B$1 (F4 on Windows, ⌘T on Mac) before filling down.
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.