PDA

View Full Version : Median with a Condition on Text Selection that Differs Only by Ending Number



kewiopex
09-26-2017, 08:54 AM
Dear Experts and Specialists
I have been trying to perfect calculations for a Median based on a selection of text (names) that differ only by their ending number.But my searches and my limited experience have proved pointless.

As per the attached spreadsheet, I wish to be able to determine the Median of a subgrouping of texts such as Brue 1 to Brue 4 within the group of text names that goes from Brue 1 to Brue 9. I have tried various versions of the median and if fomula but have failed to get any subgroup determinations. Wildcards and ">" do not work! This formula was the latest version but again I could not find a way to do a slection such as <Brue 5. =MEDIAN(IF(NOT(ISERR(SEARCH("Brue",All!$C$3:$C$70,1))),All!$E$3:$E$70,"x"))

The attached file contains a version using search as part of the median formula but again it offers no capability to derive a median based on a subgrouping that has specifications that differ only by the ending digit.

Are there any methods that will be able to do what I need, calculate a Median for a selection of names ( texts) that only differ by the ending number?

Your support and assistance is greatly appreciated once again!

Bob Phillips
09-26-2017, 01:37 PM
Try

=MEDIAN(IF((LEFT(All!$C$3:$C$70,5)="Brue ")*(RIGHT(All!$C$3:$C$70,1)>="1")*(RIGHT(All!$C$3:$C$70,1)<="4"),All!$E$3:$E$70))

It is an array formula, so CSE enter it.

kewiopex
09-26-2017, 02:56 PM
Hello Xld
Thank for you for such a fast, and excellent response! The formula as tested just gave the result for Brue 4 and not for the range for Brue 1 to Brue 4. I amended your excellent formula to just contain the *(RIGHT(All!$C$3:$C$70,1)<="4")so that the final formula string looks like this =MEDIAN(IF((LEFT(All!$C$3:$C$70,5)="Brue ")*(RIGHT(All!$C$3:$C$70,1)<="4"),All!$E$3:$ E$70)) using CSE and it worked. I can also adapt it for other combinations using the algebraic expressions such as <, > etc.

However, it appears that the overall formula cannot properly evaluate 2 AND sections using the * as the AND connector. So, for example, if I wanted to do a Median just Brue 2 to 4 I would run into difficulty since the expression can only properly eval one * section. Is this correct?

Very much appreciated!

Bob Phillips
09-27-2017, 02:10 AM
It can't do an OR (which is what I think you mean, Brue 2 or Brue 4), but you can with the + operator

=MEDIAN(IF((LEFT(All!$C$3:$C$70,5)="Brue ")*((RIGHT(All!$C$3:$C$70,1)<="2")+(RIGHT(All!$C$3:$C$70,1)<="4")),All!$E$3:$E$70))

This is saying, all rows where the N1 condition starts with Brue AND the trailing numeric is either 2 or 4,

kewiopex
09-27-2017, 05:37 AM
Perfect! I was looking for the equivalent for the OR expression and the + is it!
Thanks again.