IFS OR FUNCTION IN EXCEL

%3CLINGO-SUB%20id%3D%22lingo-sub-2071280%22%20slang%3D%22en-US%22%3EIFS%20OR%20FUNCTION%20IN%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2071280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22IFS.PNG%22%20style%3D%22width%3A%20396px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247284iF274C90DD06974B9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22IFS.PNG%22%20alt%3D%22IFS.PNG%22%20%2F%3E%3C%2FSPAN%3EDear%20Friends%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehelp%20me%20on%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%202%20years%20data%20like%202019%20and%202020%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20here%20i%20wanna%20to%20apply%203%20condition..%3C%2FP%3E%3CP%3EGREEN%2C%20AMBER%2C%20RED%3C%2FP%3E%3CP%3EGREEN%20Values%20should%20be%20more%20then%20%26lt%3B%3D0.50%2C%3C%2FP%3E%3CP%3EAMBER%20Values%20should%20be%20less%20then%20%26gt%3B%3D0.05%2C%3C%2FP%3E%3CP%3ERED%20values%20should%20be%20less%20then%20%26gt%3B%3D0.01%20including%20minus%20values%20also%20have%20to%20met..%3C%2FP%3E%3CP%3Eso%20here%20i%20have%20to%20use%20IFS()%20AND%20OR()%20function%3C%2FP%3E%3CP%3Ewhen%20i%20am%20using%20formula%20green(2019%20or%202020%20or%20both%26lt%3B%3D0.5)%20.%3C%2FP%3E%3CP%3Eamber%20(2019%20or%202020%20or%20both%26gt%3B%3D0.5)%2C%20Red(2019%20and%202020%20%26gt%3B%3D0).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20on%20formula.!!!.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%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-2071280%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2071377%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20OR%20FUNCTION%20IN%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2071377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931234%22%20target%3D%22_blank%22%3E%40SUDHAKAR-FRONTIER%3C%2FA%3E%26nbsp%3BI%20believe%20you%20received%20an%20explanation%20of%20the%20basics%20for%20these%20kind%20of%20calculations%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fifs-negative-values-error%2Fm-p%2F2066337%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fifs-negative-values-error%2Fm-p%2F2066337%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20now%20want%20to%20conditionally%20format%20the%20cells%20with%20a%20colour%20(Green%2C%20Amber%20or%20Red)%20in%20stead%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2072730%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20OR%20FUNCTION%20IN%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2072730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enot%20colour%26nbsp%3B%20Riny%2C%20its%20green%2C%20amber%2C%20red%20want%20to%20display..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2096616%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20OR%20FUNCTION%20IN%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2096616%22%20slang%3D%22en-US%22%3E%3CP%3EYES%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2096618%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20OR%20FUNCTION%20IN%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2096618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931234%22%20target%3D%22_blank%22%3E%40SUDHAKAR-FRONTIER%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPls%20check%20the%20conditions%20again%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreen%20-%20x%20%26gt%3B%3D0.50%3C%2FP%3E%3CP%3ERed%20-%20x%20%26lt%3B%3D%200.01%3C%2FP%3E%3CP%3EAmber%20-%20x%20between%200.02%20to%200.49%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20conditions%20are%20correct%20then%20you%20can%20easily%20do%20this%20with%20conditional%20formatting.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2096620%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20OR%20FUNCTION%20IN%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2096620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F942068%22%20target%3D%22_blank%22%3E%40bovasclion%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20This%20same%20kindly%20help%20me%20amber%20condition%20formula..%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

IFS.PNGDear Friends,

 

help me on below.

 

i have 2 years data like 2019 and 2020,

 

So here i wanna to apply 3 condition..

GREEN, AMBER, RED

GREEN Values should be more then <=0.50,

AMBER Values should be less then >=0.05,

RED values should be less then >=0.01 including minus values also have to met..

so here i have to use IFS() AND OR() function

when i am using formula green(2019 or 2020 or both<=0.5) .

amber (2019 or 2020 or both>=0.5), Red(2019 and 2020 >=0).

 

Please help me on formula.!!!.

 

 

 

6 Replies

@SUDHAKAR-FRONTIER I believe you received an explanation of the basics for these kind of calculations here:

https://techcommunity.microsoft.com/t5/excel/ifs-negative-values-error/m-p/2066337 

Do you now want to conditionally format the cells with a colour (Green, Amber or Red) in stead?

@Riny_van_Eekelen 

not colour  Riny, its green, amber, red want to display..

YES

@SUDHAKAR-FRONTIER 

 

Pls check the conditions again,

 

Green - x >=0.50

Red - x <= 0.01

Amber - x between 0.02 to 0.49

 

If this conditions are correct then you can easily do this with conditional formatting. 

@bovasclion 

 

Yes, This same kindly help me amber condition formula..

@SUDHAKAR-FRONTIER 

Check this, worksheet is also attached 

bovasclion_0-1611637285998.png