FORMULAS

%3CLINGO-SUB%20id%3D%22lingo-sub-805159%22%20slang%3D%22en-US%22%3EFORMULAS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805159%22%20slang%3D%22en-US%22%3E%3CP%3EI%20HAVE%20TO%20SPLIT%20G6%20INTO%20D6%20AND%20E6%20I.E%20-600%20AND%20-150%20RESPECTIVELY%20%2C%20PLEASE%20HELP%20ME.%3CBR%20%2F%3EFILE%20ATTACHED%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-805159%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805494%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULAS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805494%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381563%22%20target%3D%22_blank%22%3E%40aayushman_mishra%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20is%20it%20determined%20that%20-600%20goes%20into%20cell%20D6%20and%20-150%20goes%20into%20cell%20E6%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805607%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULAS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805607%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381563%22%20target%3D%22_blank%22%3E%40aayushman_mishra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20logic%20is%20as%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fformula-for-data-calculation%2Fm-p%2F804693%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fformula-for-data-calculation%2Fm-p%2F804693%3C%2FA%3E%2C%20when%20in%20D6%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%20(B6-C6%3D-600)%20%2B%20(B6-C6%3D-750)%2C-600%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20E6%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(AND(B6-C6%26gt%3B%3D-200%2CB6-C6%26lt%3B%3D-1)%2CB6-C6%2CIF(B6-C6%3D-750%2C-150%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20in%20G6%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DB6-C6-SUM(D6%3AF6)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805935%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULAS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3BhI%2C%20as%20per%20condition%20the%20discount%20amt%20not%20taken%20is%20-600%20and%20the%20fine%20lies%20between%20-1%20to%20-200%20and%20re%20adm%20fee%20is%20-500.%20thats%20why%20we%20have%20to%20fix%20this%20formula.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807693%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULAS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHello%20sir%2C%20as%20you%20told%20i%20applied%20the%20condition%20in%20E6%3CBR%20%2F%3EBUT%20AS%20I%20M%20EXTENDING%20THE%20FORMULA%20FOR%20MULTIPLE%20VALUES%20ITS%20SHOWING%20VALUE%20ERROR%20%2C%20PLEASE%20HELP%20ME%20REGARDING%20THIS%2C%20EXTENDED%20FORMULA%20IS%20MARKED%20GREEN.%3CBR%20%2F%3EI%20HAVE%20MULTIPLE%204%20CONDITIONS%20THATS%20WHY%20I%20M%20EXTENDING%20THE%20FORMULA%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3DIF(AND(B5-C5%26gt%3B%3D-200%2CB5-C5%26lt%3B%3D-1)%2CB5-C5%2CIF(B5-C5%3D-750%2C-150%2C%22%22)%3CFONT%20color%3D%22%2300FF00%22%3E%2BIF(B5-C5%3D-650%2C-50%2C%22%22)%3C%2FFONT%3E)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
aayushman_mishra
Occasional Contributor

I HAVE TO SPLIT G6 INTO D6 AND E6 I.E -600 AND -150 RESPECTIVELY , PLEASE HELP ME.
FILE ATTACHED

4 Replies

Hello @aayushman_mishra,

 

How is it determined that -600 goes into cell D6 and -150 goes into cell E6?

@aayushman_mishra 

If logic is as here https://techcommunity.microsoft.com/t5/Excel/formula-for-data-calculation/m-p/804693, when in D6

=IF( (B6-C6=-600) + (B6-C6=-750),-600,"")

in E6

=IF(AND(B6-C6>=-200,B6-C6<=-1),B6-C6,IF(B6-C6=-750,-150,""))

and in G6

=B6-C6-SUM(D6:F6)

@PReagan hI, as per condition the discount amt not taken is -600 and the fine lies between -1 to -200 and re adm fee is -500. thats why we have to fix this formula. 

@Sergei Baklan 
Hello sir, as you told i applied the condition in E6
BUT AS I M EXTENDING THE FORMULA FOR MULTIPLE VALUES ITS SHOWING VALUE ERROR , PLEASE HELP ME REGARDING THIS, EXTENDED FORMULA IS MARKED GREEN.
I HAVE MULTIPLE 4 CONDITIONS THATS WHY I M EXTENDING THE FORMULA

=IF(AND(B5-C5>=-200,B5-C5<=-1),B5-C5,IF(B5-C5=-750,-150,"")+IF(B5-C5=-650,-50,""))

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies