Excel Vraag - criterium met joker karakter

[Martendejong]

Mijn vraag

Hallo,

Ik wil graag een Excel formule maken waarbij getallen boven de 10 met achter de komma ,05 naar beneden afgerond worden, maar alleen als het het getal eindigt op ,05. Dus 10,10 en 20,15 is oke.

situatie:

A1: 10,05 B2: =als(aantal.als(A1;"**,05");A1-0,05;A1)

Helaas werkt dit niet, is er een manier waarop dit wel functioneert zonder dat ik een kolom hoef te vullen met deze waardes?

[Ekitch]

moet het niet zijn =als(en(rechts(a1;3)=",05";A1>10);a1-0,05;a1)
Dit gaat er dan wel vanuit dat al je getallen altijd 2 cijfers achter de komma zijn.

[Reinier]

Modulusdeling door 1? Checken of dat 0,05 is?

[SinergyX]

Ekitch schreef op donderdag 2 november 2017 @ 18:07:
moet het niet zijn =als(en(rechts(a1;3)=",05";A1>10);a1-0,05;a1)
Dit gaat er dan wel vanuit dat al je getallen altijd 2 cijfers achter de komma zijn.

Let wel op dat bij bv 11,1 mis gaat, standaard waarde in de cel is 10,1, door je celopmaak aan te passen kan je hier wel 11,10 van maken, maar rechts functie houd dan ,1 als resultaat.

Denk dat je met Afronden.beneden.wisk wel kan werken, gokje:
Afronden.beneden.wisk (A1;0,05;1)
dit zou 0,15 op 0,15 laten staan, maar 0,13 wordt 0,1.
Natuurlijk nog een als voor de >10 erbij doen

[ Voor 3% gewijzigd door SinergyX op 02-11-2017 18:20 ]

[Ekitch]

Dat zou niet mis gaan, want 11,10 wil je toch niet afronden, alleen 0,05. Dus alle andere waardes maken niet uit,

Update: ik ga ervanuit dat het werkt?

[ Voor 15% gewijzigd door Ekitch op 02-11-2017 21:11 ]

[breew]

@Ekitch Ik blijf altijd weg van string-manipulatie, als het om getallen gaat. Het kan immers zomaar ineens verkeerd gaan, als het getal niet 10,05 is, maar 10,050 oid... Geen idee of het echt zo is, het is maar een gevoel
@Reinier kwam ook met de modulus (REST), maar dan MOET je afronden, anders krijg je met gekke zaken te maken (zie hieronder)

Ik ga eerder voor zoiets

code:

=ALS(EN(A1>10;AFRONDEN(REST(A1;1);10)=0,05);A1-0,05;A1)

Het afronden op 10 decimalen is nodig, want Excel doet gekke dingen met kleine (berekende) getallen (echt heeele gekke)
proof
tik in A1 het getal 10,05
tik in B1 de formule =REST(A1;1), dit levert 0,05 op
tik in C1 de formule =(B1=0,05).. die levert ONWAAR!!?? op.. hoe de f*ck is dat mogelijk??
tik in D1 de formule =WISSEN.CONTROL(B1), dit levert 0,0500000000000007 op...

Heeft blijkbaar iets te maken met de methodiek van het opslaan van decimalen..

When numbers are stored, a corresponding binary number can represent every number or fractional number. For example, the fraction 1/10 can be represented in a decimal number system as 0.1. However, the same number in binary format becomes the following repeating binary decimal:
0001100110011100110011 (and so on)
This can be infinitely repeated. This number cannot be represented in a finite (limited) amount of space. Therefore, this number is rounded down by approximately -2.8E-17 when it is stored.

meer: https://excelribbon.tips....ors_When_Subtracting.html

[ Voor 6% gewijzigd door breew op 03-11-2017 12:12 ]

[Martendejong]

@Ekitch Functioneert super.

Is het overigens zo dat het = teken ook vervangen kan worden door bijvoorbeeld?
= ".05"
< of >".05"
<=".05"
>=".05"
<>".05"

[Ekitch]

Nee in dit geval kijkt het echt naar de "tekst" dus groter/kleiner dan gaat dan niet werken.

Wat breew zegt klopt wel, maar daarom had ik ook gezegd dat deze formule ervanuit gaat dat je altijd cijfers met 2 achter de komma gebruikt. (financieel was mijn aanname). Maar goed, wel dus opletten als je formule op formule gebruikt zoals breew beschrijft.

[Martendejong]

Moet ik dan bij waarden kleiner dan steeds de optie onwaar gebruiken?
want als niet .05 ->check of niet .10 ? ipv kleiner dan .10

Het checken voor een waarde kleiner dan zou eigenlijk korter kunnen zijn.

Dus bijvoorbeeld vanaf waarde die meer dan 100 is dient:
Minder dan x,25 -> x.00
meer dan x,25 en minder dan x,75 -> x,50
meer dan x,75 -> 1,00

[ Voor 47% gewijzigd door Martendejong op 03-11-2017 11:54 . Reden: Aanvulling ]

[Lustucru]

Martendejong schreef op vrijdag 3 november 2017 @ 11:41:
Moet ik dan bij waarden kleiner dan steeds de optie onwaar gebruiken?
want als niet .05 ->check of niet .10 ? ipv kleiner dan .10

Nee. Om te beginnen moet je dus geen tekst gebruiken om getallen te vergelijken. Het antwoord van @Reinier en @breew is correct, waarbij je inderdaad rekening moet houden met het feit dat er oneindig veel getallen (breuken) zijn die niet goed kunnen worden weergegeven in een andere notatie.

Denk bijvoorbeeld aan 1/3. Dit is niet goed te noteren in decimale notatie. Het getal 1/20 kunnen we wel schrijven in decimale notatie (0,05), maar niet in binaire notatie. En computers zijn nu eenmaal binaire machines.

Daar zijn twee oplossingen voor:
het gebruik van afronden zoals Breew doet, of in de opties van Excel 'precisie zoals weergegeven' aanvinken. Bij financiele bladen heeft dat vaak de voorkeur om centenverschillen in optellingen te vermijden.

Martendejong schreef op vrijdag 3 november 2017 @ 11:41:
Dus bijvoorbeeld vanaf waarde die meer dan 100 is dient:
Minder dan x,25 -> x.00
meer dan x,25 en minder dan x,75 -> x,50
meer dan x,75 -> 1,00

Ah! Nu stel je een heel andere vraag. Afhankelijk van hoe representatief het voorbeeld is kun je dat oplossen met
* een afrondingstabel ( zie ook: Excel Afronden naar waarde uit tabel),
* een wiskundige benadering. In dit geval wil je afronden op halven ->afronden(a1*2,0)/2
* een serie Als() statements. (zie hieronder)

[ Voor 37% gewijzigd door Lustucru op 03-11-2017 12:17 ]

[breew]

Martendejong schreef op vrijdag 3 november 2017 @ 11:41:
Dus bijvoorbeeld vanaf waarde die meer dan 100 is dient:
Minder dan x,25 -> x.00
meer dan x,25 en minder dan x,75 -> x,50
meer dan x,75 -> 1,00

Je gaat dan een geneste functionaliteit maken inde vorm van

code:

ALS(<0.25;waarde als waar;ALS(EN(<=0.25;>0.75);waarde als waar;ALS(>=0.75;waarde als waar;waarde als onwaar)))

Persoonlijk houd ik niet zo van dat soort lange statements (onoverzichtelijk voor beheer en onderhoud), en gaat mijn voorkeur uit naar een stukje VBA code met Select...Case-statement.

[ Voor 26% gewijzigd door breew op 03-11-2017 12:09 . Reden: veel te veel typo's ]

[jopie]

Misschien een hele lelijke oplossing, maar voldoet als antwoord op de eerst gestelde vraag niet:
Vervang ,05 door ,00?

[breew]

jopie schreef op vrijdag 3 november 2017 @ 12:09:
Misschien een hele lelijke oplossing, maar voldoet als antwoord op de eerst gestelde vraag niet:
Vervang ,05 door ,00?

nee, want het moet blijkbaar alleen bij waarden >10.Enige logica is dus altijd vereist. Daarnaast is het, imho, beter om cijfermatige problemen niet tekstueel oplossen, maar cijfermatig.

[breew]

Lustucru schreef op vrijdag 3 november 2017 @ 12:05:
Nee. Om te beginnen moet je dus geen tekst gebruiken om getallen te vergelijken. Het antwoord van @Reinier en @breew is correct, waarbij je inderdaad rekening moet houden met het feit dat er oneindig veel getallen (breuken) zijn die niet goed kunnen worden weergegeven in een andere notatie.

Denk bijvoorbeeld aan 1/3. Dit is niet goed te noteren in decimale notatie. Het getal 1/20 kunnen we wel schrijven in decimale notatie (0,05), maar niet in binaire notatie. En computers zijn nu eenmaal binaire machines.

Daar zijn twee oplossingen voor:
het gebruik van afronden zoals Breew doet, of in de opties van Excel 'precisie zoals weergegeven' aanvinken. Bij financiele bladen heeft dat vaak de voorkeur om centenverschillen in optellingen te vermijden.

Toch is het ERG belangrijk om altijd bewust te zijn van dit fenomeen, als je met niet-hele-getallen werkt met computers.
Met de precisie-instellingen in excel kan je het probleem 'verbergen', maar als je het bijvoorbeeld exporteert naar csv, en op een ander systeem weer inleest en daar weer verder bewerkt, kunnen de fouten weer naar voren komen.

Dit gedrag is trouwens bij lange na niet beperkt tot Excel, ook in (bijvoorbeeld) R loop ik er regelmatig tegenaan. Goed afronden is dan ook echt, voor zover ik weet, de enige goede manier om met deze eigenaardigheid van computers om te gaan.

[Lustucru]

Het zou helpen als de TS duidelijker aangeeft wat hij wil bereiken... Lustucru in "Excel Vraag - criterium met joker karakter"

breew schreef op vrijdag 3 november 2017 @ 12:16:
Met de precisie-instellingen in excel kan je het probleem 'verbergen',
[...]
de enige goede manier om met deze eigenaardigheid van computers om te gaan.

offtopic:
het is dus geen eigenaardigheid van computers, maar een eigenschap van getallen. Er zijn oneindig meer getallen dan we kunnen noteren in een willekeurig notatiesysteem. De precisieinstellingen verbergen het probleem ook niet, maar zorgen voor de gewenste omgang met breuken.

Als op een financieel blad ziet dat 21% BTW van 104,71 een bedrag is van 21,99, wat bedoel je dan? Is de btw 21,99 (precisie zoals weergegeven of 21,9891? De standaardinstelling? maar idd, de keuze moet bewust gemaakt worden.

[ Voor 44% gewijzigd door Lustucru op 03-11-2017 12:25 ]

[Martendejong]

Bedankt allemaal voor jullie hulp! De sheet is klaar voor gebruikt!