Sobre este ejercicio
Un conjunto de datos de 45 filas en una hoja, una celda 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
- E2
=D2*IF(D2>=$G$5,$H$5,IF(D2>=$G$4,$H$4,IF(D2>=$G$3,$H$3,$H$2)))
A nested IF is a chain of questions: is the figure at least the top threshold? If yes, use the top rate; if not, ask the same about the next tier, and so on, with the lowest rate as the final else. Testing from the top down matters because a $236,400 rep also satisfies the lower tiers — the first test that passes decides. The thresholds and rates come from G2:H5 with absolute references, so one formula filled down prices all 45 reps and still works after finance edits the table.
Pitfall 1: testing from the bottom up with >= (IF(D2>=$G$2,$H$2,…)) always stops at the first tier, because every rep clears $0 — reverse the order or use < from the bottom. Pitfall 2: > instead of >= pays 4% to the rep with exactly $100,000.00; tier plans are written "from", so the boundary belongs to the higher tier. Pitfall 3: typing the thresholds and rates into the formula (IF(D2>=200000,8%,…)) breaks the moment the table changes; the grader edits G5 and H5 and expects the commission to follow. Alternatives: IFS(D2>=$G$5,$H$5,D2>=$G$4,$H$4,D2>=$G$3,$H$3,TRUE,$H$2) is the readable form in Excel 2019 and later; VLOOKUP(D2,$G$2:$H$5,2,TRUE) and XLOOKUP(D2,$G$2:$G$5,$H$2:$H$5,,-1) (365) scale to twenty tiers without nesting, but need the thresholds sorted ascending. Beyond four tiers a lookup is the maintainable choice; nested IF beyond three levels is where audit findings start.
Otras formas válidas
- =D2*IFS(D2>=$G$5,$H$5,D2>=$G$4,$H$4,D2>=$G$3,$H$3,TRUE,$H$2) — IFS, Excel 2019 and later
- =D2*VLOOKUP(D2,$G$2:$H$5,2,TRUE) — approximate match on the ascending thresholds
- =D2*XLOOKUP(D2,$G$2:$G$5,$H$2:$H$5,,-1) — Excel 365; match mode -1 picks the next smaller threshold
- =D2*IF(D2<$G$3,$H$2,IF(D2<$G$4,$H$3,IF(D2<$G$5,$H$4,$H$5))) — nested IF built from the bottom with <
Errores frecuentes
Filling the formula down with relative references into the tier table (IF(D2>=G5,H5,…)): E3 then compares against G6 and H6, which are empty, so from row 3 on the commission is wrong or zero. Lock the table as $G$5, $H$5 and so on.
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.