Rekenen met tekst binnen een formule zonder het maskeren van waardes

[Azoura]

Goedemiddag,

Ik heb een bestand waarin laboratoriumgegevens bijgehouden worden. Een laboratirum geeft ons zodra eens stof geanalyseerd is, maar niet gevonden boven de kwantificatielimiet een “Minder dan” waarde. Bijv: <0,1, <0,05 ect. Alle uitslagen staan in dezelfde rij.
Ik heb twee formules geschreven die bekijkt wat de mediaan en 95th percentiel uitslag is tussen een bepaalde periode. Een samenvattings staat in de 05th percentile sheet, de datum wordt ingegeven in kolom '95th percentile'!I4 en J4. Deze formule functioneert naar behoren.
Hoe krijg ik het voor elkaar om in deze formule ervoor te zorgen dat er met alle waardes geraporteerd als “minder dan” gerekend wordt met de helft van deze waarde (dus <0,1 wordt 0,05, <0,05 wordt 0,025 ect.) Ik wil eigenlijk geen waardes maskeren met tekst dmv. Format cells. Dit is namelijk erg moeilijk te begrijpen voor de mensen die de waardes invullen. Het liefst wil ik dus een oplossing binnen mijn formule.
Als we dan toch bezig zijn: Ik ben ook opzoek naar een oplossing waarbij kollom K op de eerste sheet het aantal resultaten telt dat ook binnen de tijdsperiode valt waar de Mediaan en 95th percentile mee telt.

Als voorbeeld bestand een kale versie van mijn bestand.

Alvast bedankt!

 

[AlexCEL]

Probeer deze eens:

I9: =MEDIAAN(ALS(($N$5:$XFD$5>'95th percentile'!$I$4)*($N$5:$XFD$5<'95th percentile'!$J$4);--SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(N9:XFD9;"<0,1";0,05);"<0,05";"0,025");"<0,01";"0,005")))

J9: =PERCENTIEL(ALS(($N$5:$XFD$5>'95th percentile'!$I$4)*($N$5:$XFD$5<'95th percentile'!$J$4);--SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(N9:XFD9;"<0,1";0,05);"<0,05";"0,025");"<0,01";"0,005"));0,95)

K9: =SOMPRODUCT(($N$5:$XFD$5>='95th percentile'!$I$4)*(($N$5:$XFD$5<='95th percentile'!$J$4)*($N9:$XFD9<>"")))

Let op: de eerste 2 zijn matrixfuncties, dus afsluiten met Control+Shift+Enter

 

[Azoura]

Held! Dit lijkt te werken. Moet ik voor elke ‘minder dan’ optie een substitutie regel toevoegen of id er ook een manier om dit vanuit een langere lijst te laten zoeken?

 

[AlexCEL]

Moet ik voor elke ‘minder dan’ optie een substitutie regel toevoegen

Inderdaad, dus ik hoop dat het er niet teveel zijn...

 

[Azoura]

Ik loop in de grote dataset toch tegen een probleem aan: als er binnen de aangegeven tijd een lege cel in de rij zit dan krijg ik een #value melding. Dit was eerst geen probleem. Het kan echter voorkomen dat een analyse gewoon niet gedaan is op dat monster. Is er een manier om dit op te lossen?

 

[AlexCEL]

Zo?

=MEDIAAN(ALS(($N$5:$XFD$5>'95th percentile'!$I$4)*($N$5:$XFD$5<'95th percentile'!$J$4);ALS(N9:XFD9<>"";--SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(N9:XFD9;"<0,1";0,05);"<0,05";"0,025");"<0,01";"0,005");"")))

Rest kun je zelf wel aanpassen denk ik..

 

[Azoura]

Dat deed de klus klaren! Heel erg bedankt voor je hulp! Ik hoop er zelf ook zo goed in te worden