Forum Discussion
Nested IF formula help needed
Sorry, but for some reason I'm not allowed to upload a screenshot to easily decipher this problem of mine...
The formula is not working in cell O4 (or O5). I realize it's probably due to the conditions all needing to relate to the first condition, but my brain is not working to figure this out.
If E[arned] PTO hours are present, then columns J/G formula; if U[sed] hours are present, then columns K/H formula; if neither of the prior two conditions, then columns L/I formula.
See Rate/Hr column O in red in rows 4 and 5. I have the following in row 4 for the cell:
=IF(J4/G4>0,J4/G4,IF(J4/G4=0,K4/H4,L4/I4))
Column names:
| A = ID | B = Employee | C = Hire Date | D = Code | E = Date | F = Type | G = Earned Hrs | H = Used Hrs | I = Balance Hrs | J = Earned $ | K = Used $ | L = Balance $ | M = border | N = Gained/(Lost) | O = Rate/Hr |
Data:
| ID | Employee | Hire Date | Code | Date | Type | Earned Hrs | Used Hrs | Balance Hrs | Earned $ | Used $ | Balance $ | Gained/(Lost) | Rate/Hr | |
| 139 | Smith, John | 08/25/1998 | PTOS | 09/15/2022 | E | 3.85 | - | 56.00 | $ 485.88 | $ - | $ 7,067.31 | $ 126.202 | ||
| 139 | Smith, John | 08/25/1998 | PTOS | 09/08/2022 | E | 3.85 | - | 52.15 | $ 485.88 | $ - | $ 6,581.43 | $ 126.202 | ||
| 139 | Smith, John | 08/25/1998 | PTOS | 09/08/2022 | C | - | - | 48.30 | $ - | $ - | $ 6,095.56 | $ - | #DIV/0! | |
| 139 | Smith, John | 08/25/1998 | PTOS | 09/08/2022 | U | - | (8.00) | 48.30 | $ - | $ (1,009.62) | $ 6,095.56 | #DIV/0! |
Thank you.
11 Replies
=IF(AND(G2<>0,J2<>""),J2/G2,IF(AND(H2<>0,K2<>""),K2/H2,IF(AND(I2<>0,L2<>""),L2/I2,"")))Does this formula return your expected result?
- Yes, thank you!
- Ok, after further review, it's not correct for some rows. How do I copy/paste a screenshot of my Excel spreadsheet? The system won't allow me and that's the easiest way to present and make sense of it.
