Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1599095%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599095%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%26nbsp%3BI%20have%20two%20columns%20A%20and%20B.%20I%20would%20like%20to%20calculate%20the%20median%20of%20the%20data%20in%20A%20only%20if%20it%20in%20B%20says%20%22Yes%22.%26nbsp%3BCan%20anybody%20help%20my%20with%20the%20command%20for%20this%3F%26nbsp%3BThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1599095%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1599292%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F764684%22%20target%3D%22_blank%22%3E%40luna155%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20a%20third%20column%20saying%20if(B1%3D%22Yes%22%2CA1%2C%22%22)%3C%2FP%3E%3CP%3Ethen%20I%20took%20the%20median%20of%20column%20C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esee%20the%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1599964%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F764684%22%20target%3D%22_blank%22%3E%40luna155%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%3DMEDIAN(IF(B%3AB%3D%22Yes%22%2CA%3AA))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eusing%20as%20it%20is%20on%20Excel%20with%20dynamic%20arrays%20or%20entered%20as%20array%20formula%20by%20Ctrl%2BShift%2BEnter%20in%20other%20case.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1603519%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603519%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much%20-%20was%20a%20great%20help!%26nbsp%3B%3CBR%20%2F%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%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1603520%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603520%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1603558%22%20slang%3D%22en-US%22%3ERE%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603558%22%20slang%3D%22en-US%22%3Ecertainly%20possible%20if%20you%20use%20a%20%22sumproduct%22%20search%20on%20web%20sommeproduct%20%2B%20median%20best%20PMF_EXCEL%3C%2FLINGO-BODY%3E
New Contributor

Hi. I have two columns A and B. I would like to calculate the median of the data in A only if it in B says "Yes". Can anybody help my with the command for this? Thanks 

5 Replies

@luna155 

 

I used a third column saying if(B1="Yes",A1,"")

then I took the median of column C

 

see the attached file

@luna155 

As variant

=MEDIAN(IF(B:B="Yes",A:A))

using as it is on Excel with dynamic arrays or entered as array formula by Ctrl+Shift+Enter in other case.

Thank you very much - was a great help! 
@Sergei Baklan 

Thank you very much!
certainly possible if you use a "sumproduct" search on web sommeproduct + median best PMF_EXCEL