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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 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
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies