Microsoft Excel - Updates to Calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-2262000%22%20slang%3D%22en-US%22%3EMicrosoft%20Excel%20-%20Updates%20to%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2262000%22%20slang%3D%22en-US%22%3E%3CP%3ECurrent%20formula%20is%20just%20adding%20up%20different%20numeric%20values%2C%20but%20I%20need%20to%20add%20some%20additional%20logic%20and%20can%20use%20some%20assistance.%3C%2FP%3E%3CP%3E%3DB2%2BF2%2BH2%2BI2%2BJ2%2BM2%2BN2%2BP2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20If%20Column%20K%20%3D%20%22Set%20Date%22%2C%20I%20would%20like%20the%20above%20formula%20to%20be%20updated%20to%20output%20a%20'-'.%3C%2FP%3E%3CP%3E2)%20If%20Column%20BA%20%3D%20%22Group%22%20or%20%22Order%22%2C%20I%20would%20like%20the%20above%20formula%20to%20be%20updated%20to%20output%20a%20'-'.%3C%2FP%3E%3CP%3E3)%20If%20Column%20G%20%3D%20%22As-Is(No%20Changes)%22%20AND%20Column%20BA%20%3D%20%22Override%22%2C%20I%20would%20like%20the%20above%20formula%20to%20be%20updated%20to%20output%20'0.2'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100.03009066254654%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3EColumn%20G%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2256px%22%3EColumn%20K%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2256px%22%3EColumn%20BA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2256px%22%3ECurrent%20Calculation%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2256px%22%3EUpdated%20Calculation%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3ESet%20Date%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E5.1%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E-%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EGroup%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E3.8%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E-%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EOrder%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E31.2%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E-%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3EAs-Is(No%20Changes)%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EOverride%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E31.4%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E0.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3EModified%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3ENormal%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E22.2%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%20height%3D%2229px%22%3E22.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EModified%3C%2FTD%3E%3CTD%3ESet%20Date%3C%2FTD%3E%3CTD%3EOverride%3C%2FTD%3E%3CTD%3E21.0%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENew%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EGroup%3C%2FTD%3E%3CTD%3E31.9%3C%2FTD%3E%3CTD%3E-%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EModified%3C%2FTD%3E%3CTD%3ENormal%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E18.2%3C%2FTD%3E%3CTD%3E18.2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2262000%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2262040%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20-%20Updates%20to%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2262040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F975426%22%20target%3D%22_blank%22%3E%40cubd8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(OR(K2%3D%22Set%20Date%22%2CBA2%3D%22Group%22%2CBA2%3D%22Order%22)%2C%22-%22%2CIF(AND(G2%3D%22As-Is(No%20Changes)%22%2CBA2%3D%22Override%22)%2C0.2%2CB2%2BF2%2BH2%2BI2%2BJ2%2BM2%2BN2%2BP2))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2263058%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20-%20Updates%20to%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20using%20the%20updated%20formula%2C%20there%20is%20one%20update.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20cases%20where%26nbsp%3BColumn%20G%20%3D%20%22As-Is(No%20Changes)%22%20AND%20Column%20BA%20%3D%20%22Override%22%2C%20regardless%20of%20Column%20K%2C%20or%20Column%20BA%2C%20I%20would%20like%20that%20to%20result%20in%20'0.2'%20before%20the%20other%20logic.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20this%20be%20updated%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2263277%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20-%20Updates%20to%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F975426%22%20target%3D%22_blank%22%3E%40cubd8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESimply%20change%20the%20order%20of%20the%20conditions%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(AND(G2%3D%22As-Is(No%20Changes)%22%2CBA2%3D%22Override%22)%2C0.2%2CIF(OR(K2%3D%22Set%20Date%22%2CBA2%3D%22Group%22%2CBA2%3D%22Order%22)%2C%22-%22%2CB2%2BF2%2BH2%2BI2%2BJ2%2BM2%2BN2%2BP2))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 GColumn KColumn BACurrent CalculationUpdated Calculation
 Set Date 5.1-
  Group3.8-
  Order31.2-
As-Is(No Changes) Override31.40.2
ModifiedNormal 22.222.2
ModifiedSet DateOverride21.0-
New Group31.9-
ModifiedNormal 18.218.2

 

 

6 Replies

@cubd8 

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

@Hans Vogelaar 

 

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?

@cubd8 

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

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

@cubd8 

I don't understand what should go where.

@cubd8 

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