 # Microsoft Excel - Updates to Calculation

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

6 Replies

# Re: Microsoft Excel - Updates to Calculation

=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))

# Re: Microsoft Excel - Updates to Calculation

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?

# Re: Microsoft Excel - Updates to Calculation

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))

# Re: Microsoft Excel - Updates to Calculation

I need help updating one last piece of my existing formula to account for an additional logic change. I need to add this logic below to the existing formula. The original results from the formula should not change, only this condition below.

=IF(AND(R2<>"Set Delivery Date",Q2="As-Is (No Changes)",S2="Override"),0.2,IF(OR(R2="Set Delivery Date",R2="Group ID",S2="Order"),"-",A2+E2+H2+I2+L2+M2+O2+T2))

Updated Logic needed:

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

# Re: Microsoft Excel - Updates to Calculation

I don't understand what should go where.

# Re: Microsoft Excel - Updates to Calculation

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"
))``````