SOLVED

Need formula based on conditions for lower or higher column values

%3CLINGO-SUB%20id%3D%22lingo-sub-2357052%22%20slang%3D%22en-US%22%3ENeed%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357052%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20for%20getting%20output%20in%20Remarks%20column%20based%20on%20conditions%20data.%3C%2FP%3E%3CP%3EThe%20data%20is%20given%20in%20attached%20excel%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2357052%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357123%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054548%22%20target%3D%22_blank%22%3E%40ramesh98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(%0ASUMPRODUCT(--(B3%26gt%3BC3%3AK3))%3D9%2C%24H%2411%2C%0ASUMPRODUCT(--(B3%3CK3%3EC3)*(SUMPRODUCT(--(B3%3CK3%3ED3%3AK3))%3D8)%2C%24H%2414%2C%0A(B3%26gt%3BC3)*(SUMPRODUCT(--(B3%3CK3%3ED3%3AK3))%3D8)%2C%24H%2416%2C%0A(SUMPRODUCT(--(B3%26gt%3BC3%3AF3))%3D4)*%0A(SUMPRODUCT(--(B3%3CK3%3E%3C%2FK3%3E%3C%2FK3%3E%3C%2FK3%3E%3C%2FK3%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20your%20sample%20different%20conditions%20use%20same%20logic%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20300px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280865iF6E2CF2388568707%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eplus%20some%20records%20meet%20no%20one.%20If%20misprint%20you%20may%20correct%20the%20formula%20as%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357128%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357128%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054548%22%20target%3D%22_blank%22%3E%40ramesh98%3C%2FA%3E%26nbsp%3BIf%20you%20are%20open%20to%20a%20more%20graphic%20representation%20of%20the%20trends%2C%20you%20might%20want%20to%20consider%20a%20variety%20of%20Sparklines%20as%20demonstrated%20in%20the%20attached%20sheet.%20In%20this%20case%2C%20I%20chose%20the%20Winn%2FLoss%20type%20with%20markers%20for%20highest%20(green)%20and%20lowest%20(red).%20The%20%22Trend%22%20column%20in%20M%20fairly%20accurately%20represents%20the%20textual%20trend%20descriptions%20by%20looking%20at%20the%20placement%20of%20the%20different%20markers.%3C%2FP%3E%3CP%3EJust%20another%20way%20to%20look%20at%20things.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357129%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054548%22%20target%3D%22_blank%22%3E%40ramesh98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20conditions%20in%20D13%20and%20D15%20are%20the%20same%2C%20and%20the%20conditions%20in%20D14%20and%20D16%20are%20also%20the%20same.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357132%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357132%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20sir%2C%20Somewhere%20there%20seems%20to%20be%20little%20mistake%20from%20my%20side.%20Can%20you%20check%20in%20attached%20excel%20sheet%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%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357133%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357133%22%20slang%3D%22en-US%22%3EThanks%20sir%2C%20at%20the%20moment%20i%20am%20looking%20at%20answer%20in%20text%20form%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357135%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357135%22%20slang%3D%22en-US%22%3E%3CP%3Edata%20corrected%2C%20pl%20check%20again%20thanks%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357136%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357136%22%20slang%3D%22en-US%22%3E%3CP%3ESir%2C%20sorry%20I%20have%20attached%20data%20again.%20There%20was%20small%20error%20in%20conditions%20at%20D15%2FD17.%20Also%20kindly%20help%20resolve%20in%20attached%20sheet%20itself%26nbsp%3B%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%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357144%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054548%22%20target%3D%22_blank%22%3E%40ramesh98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20L3%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFS(B3%26gt%3BMAX(C3%3AK3)%2C%24H%2411%2CB3%3CMIN%3EC3%2CB3%3CMIN%3EMAX(D3%3AK3))%2C%24H%2414%2CAND(B3%26gt%3BMAX(C3%3AD3)%2CB3%3CMIN%3EMIN(E3%3AK3))%2C%24H%2416%2CAND(B3%26gt%3BMAX(C3%3AF3)%2CB3%3CMIN%3E%0A%3C%2FMIN%3E%3C%2FMIN%3E%3C%2FMIN%3E%3C%2FMIN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357146%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357146%22%20slang%3D%22en-US%22%3E%3CP%3ESir%20thanks%20but%20still%20showing%20name%20error.%20Kindly%20check%20attached%20sheet%26nbsp%3B%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357223%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357223%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054548%22%20target%3D%22_blank%22%3E%40ramesh98%3C%2FA%3E%20Apparently%20you%20have%20an%20older%20version%20of%20Excel%20that%20doesn't%20have%20the%20IFS%20function.%3C%2FP%3E%0A%3CP%3EThis%20version%20will%20work%20in%20all%20versions%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(B3%26gt%3BMAX(C3%3AK3)%2C%24H%2411%2CIF(B3%3CMIN%3EC3%2CB3%3CMIN%3EMAX(D3%3AK3))%2C%24H%2414%2CIF(AND(B3%26gt%3BMAX(C3%3AD3)%2CB3%3CMIN%3EMIN(E3%3AK3))%2C%24H%2416%2CIF(AND(B3%26gt%3BMAX(C3%3AF3)%2CB3%3CMIN%3E%3C%2FMIN%3E%3C%2FMIN%3E%3C%2FMIN%3E%3C%2FMIN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357313%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357313%22%20slang%3D%22en-US%22%3E%3CP%3EI%20realize%20that%20not%20all%20rank%205s%20or%20rank%209s%20are%20the%20same%2C%20for%20example.%20I%20thought%20I'd%20take%20a%20different%20approach%20to%20the%20logic%20offered%20and%20go%20based%20on%20positioning.%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIFNA(VLOOKUP(RANK.AVG(B3%2CB3%3AK3)%2C%7B1%2C%22Long%20term%20uptrend%20intact%22%3B2%2C%22Chance%20of%20initiation%20of%20uptrend%22%3B3%2C%22Uptrend%20initiated%22%3B6%2C%22Check%20support%20at%2020%20DEMA%22%3B8%2C%22Downtrend%20initiated%22%3B9%2C%22Chance%20of%20initiation%20of%20downtrend%22%3B10%2C%22Long%20term%20downtrend%20intact%22%7D%2C2%2C0)%2C%22Other%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357812%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20formula%20based%20on%20conditions%20for%20lower%20or%20higher%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357812%22%20slang%3D%22en-US%22%3EWorking%20fine.%20Thanks%20a%20lot%20sir%3C%2FLINGO-BODY%3E
Occasional Contributor

 

I need help for getting output in Remarks column based on conditions data.

The data is given in attached excel file

 

13 Replies

@ramesh98 

As variant

=IFS(
SUMPRODUCT(--(B3>C3:K3))=9,$H$11,
SUMPRODUCT(--(B3<C3:K3))=9,$H$12,
(B3>C3)*(SUMPRODUCT(--(B3<D3:K3)))=8,$H$13,
(B3<C3)*(SUMPRODUCT(--(B3>D3:K3))=8),$H$14,
(B3>C3)*(SUMPRODUCT(--(B3<D3:K3))=8),$H$15,
(B3<C3)*(SUMPRODUCT(--(B3>D3:K3))=8),$H$16,
(SUMPRODUCT(--(B3>C3:F3))=4)*
(SUMPRODUCT(--(B3<G3:K3))=5),$H$17,
TRUE, "nothing")

In your sample different conditions use same logic

image.png

plus some records meet no one. If misprint you may correct the formula as needed.

@ramesh98 If you are open to a more graphic representation of the trends, you might want to consider a variety of Sparklines as demonstrated in the attached sheet. In this case, I chose the Winn/Loss type with markers for highest (green) and lowest (red). The "Trend" column in M fairly accurately represents the textual trend descriptions by looking at the placement of the different markers.

Just another way to look at things.

 

@ramesh98 

The conditions in D13 and D15 are the same, and the conditions in D14 and D16 are also the same.

Thanks sir, Somewhere there seems to be little mistake from my side. Can you check in attached excel sheet@Sergei Baklan 

Thanks sir, at the moment i am looking at answer in text form

data corrected, pl check again thanks@Hans Vogelaar 

Sir, sorry I have attached data again. There was small error in conditions at D15/D17. Also kindly help resolve in attached sheet itself @Sergei Baklan 

@ramesh98 

In L3:

 

=IFS(B3>MAX(C3:K3),$H$11,B3<MIN(C3:K3),$H$12,AND(B3>C3,B3<MIN(D3:K3)),$H$13,AND(B3<C3,B3>MAX(D3:K3)),$H$14,AND(B3>MAX(C3:D3),B3<MIN(E3:K3)),$H$15,AND(B3<MAX(C3:D3),B3>MIN(E3:K3)),$H$16,AND(B3>MAX(C3:F3),B3<MIN(G3:K3)),$H$17,TRUE,"Other")

 

Fill down.

Sir thanks but still showing name error. Kindly check attached sheet @Hans Vogelaar 

best response confirmed by ramesh98 (Occasional Contributor)
Solution

@ramesh98 Apparently you have an older version of Excel that doesn't have the IFS function.

This version will work in all versions:

 

=IF(B3>MAX(C3:K3),$H$11,IF(B3<MIN(C3:K3),$H$12,IF(AND(B3>C3,B3<MIN(D3:K3)),$H$13,IF(AND(B3<C3,B3>MAX(D3:K3)),$H$14,IF(AND(B3>MAX(C3:D3),B3<MIN(E3:K3)),$H$15,IF(AND(B3<MAX(C3:D3),B3>MIN(E3:K3)),$H$16,IF(AND(B3>MAX(C3:F3),B3<MIN(G3:K3)),$H$17,"Other")))))))

I realize that not all rank 5s or rank 9s are the same, for example. I thought I'd take a different approach to the logic offered and go based on positioning.

=IFNA(VLOOKUP(RANK.AVG(B3,B3:K3),{1,"Long term uptrend intact";2,"Chance of initiation of uptrend";3,"Uptrend initiated";6,"Check support at 20 DEMA";8,"Downtrend initiated";9,"Chance of initiation of downtrend";10,"Long term downtrend intact"},2,0),"Other")

Working fine. Thanks a lot sir
Working nicely thanks. Will check by adding conditions