Apr 08 2021 11:33 AM
Current formula is just adding up different numeric values, but I need to add some additional logic and can use some assistance.
=B2+F2+H2+I2+J2+M2+N2+P2
1) If Column K = "Set Date", I would like the above formula to be updated to output a '-'.
2) If Column BA = "Group" or "Order", I would like the above formula to be updated to output a '-'.
3) If Column G = "As-Is(No Changes)" AND Column BA = "Override", I would like the above formula to be updated to output '0.2'.
Column G | Column K | Column BA | Current Calculation | Updated Calculation |
Set Date | 5.1 | - | ||
Group | 3.8 | - | ||
Order | 31.2 | - | ||
As-Is(No Changes) | Override | 31.4 | 0.2 | |
Modified | Normal | 22.2 | 22.2 | |
Modified | Set Date | Override | 21.0 | - |
New | Group | 31.9 | - | |
Modified | Normal | 18.2 | 18.2 |
Apr 08 2021 11:55 AM
=IF(OR(K2="Set Date",BA2="Group",BA2="Order"),"-",IF(AND(G2="As-Is(No Changes)",BA2="Override"),0.2,B2+F2+H2+I2+J2+M2+N2+P2))
Apr 08 2021 08:55 PM
When using the updated formula, there is one update.
In cases where Column G = "As-Is(No Changes)" AND Column BA = "Override", regardless of Column K, or Column BA, I would like that to result in '0.2' before the other logic.
Can this be updated?
Apr 09 2021 12:41 AM
Simply change the order of the conditions:
=IF(AND(G2="As-Is(No Changes)",BA2="Override"),0.2,IF(OR(K2="Set Date",BA2="Group",BA2="Order"),"-",B2+F2+H2+I2+J2+M2+N2+P2))
Apr 11 2021 05:24 PM
Apr 12 2021 03:46 AM
I don't understand what should go where.
Apr 12 2021 09:21 AM
If translate this
IF
Q2 <> "As-Is (No Changes)" AND
S2="Override" AND
G2 GT U2
THEN ((G2-U2) + A2+E2+H2+I2+L2+M2+O2))
ELSE
IF G2 LE U2
THEN G2+A2+E2+H2+I2+L2+M2+O2
literally then
=IF(
(Q2 <> "As-Is (No Changes)")*
(S2="Override")*
(G2 >= U2),
G2-U2 + A2+E2+H2+I2+L2+M2+O2,
IF(
G2 <= U2,
G2+A2+E2+H2+I2+L2+M2+O2,
"something else"
))