Hi.
I have this scenario:
Wrong.jpg
E3: =IF($K3="—", F3-1, IF(HLOOKUP($K3, $B$2:$F$2, 1, 0) = E$2, $G3, IF(HLOOKUP($K3, $B$2:$F$2, 1, 0) > E$2, F3-1, "—")))
D3: =IF($K3="—", E3-1, IF(HLOOKUP($K3, $B$2:$F$2, 1, 0) = D$2, $G3, IF(HLOOKUP($K3, $B$2:$F$2, 1, 0) > D$2, E3-1, "—")))
C2: =IF($K3="—", D3-1, IF(HLOOKUP($K3, $B$2:$F$2, 1, 0) = C$2, $G3, IF(HLOOKUP($K3, $B$2:$F$2, 1, 0) > C$2, D3-1, "—")))
B3: =IF($K3="—", C3-1, IF(HLOOKUP($K3, $B$2:$F$2, 1, 0) = B$2, $G3, IF(HLOOKUP($K3, $B$2:$F$2, 1, 0) > B$2, C3-1, "—")))
I need to nest another IF() function inside these so that all cells to the left of the cell with "1" show "0" instead of negative numbers, like this:
Right.jpg
I can' figure out the logic on these IF() functions - they are way over my head.
Please, help.
PR
P.S.: Big thanks to @FDibbins for helping out in the construction of the IF()s functions - I just forgot about the negative numbers.
Bookmarks