Tabel hulp

[eccs18]

Goedendag!

Ik ben bezit met het maken van een tabel, maar ik kom er niet uit met de verschillende formules. Bijgevoegd het bestand wat ik tot nu toe heb. Hier zullen onwaarschijnlijk omslachtige en/of foutieve formules in staan, maar verder ben ik helaas niet gekomen Ik ben opzoek naar vier formules (of andere logischere mogelijkheden):

Optellen tot aan bepaald bedrag
Ik wil graag bedragen bij elkaar optellen waarbij de eerste 100,00 euro automatisch niet meegenomen wordt in de berekening.

In kolom I bepaal ik of een bedrag überhaupt meegenomen moet worden in deze telling. Wat wel en niet meegenomen wordt, wordt weergegeven in kolom J. Momenteel heb ik voor mijzelf een "rekenhulp" in kolom K gezet, waarbij ik kan zien wanneer de 100,00 wordt bereikt. Echter, bij voorkeur zou ik hebben dat dit automatisch gaat. Dat wil zeggen:
- het bedrag van 95,00 euro van regel 3, wordt NIET weergegeven in het eindtotaal in kolom N (valt onder de 100,00 euro)
- van het bedrag van 45,00 van regel 4, wordt slechts voor 40,00 WEL weergeven in kolom N omdat hier de 100,00 gepasseerd wordt. Dus 5,00 euro wordt nog vrijgelaten.

Vervolgens zou het fijn zijn als dit per maand opnieuw berekend kan worden. Dus: in januari 100,00 euro vrijlaten en de rest meetellen in kolom N. Wanneer er een datum ingevuld wordt in februari (in kolom B), dat deze vrijlating opnieuw geldt enzovoort.

Berekening afhankelijk maken van ingevuld bedrag
De 100,00 euro die ik nu steeds wil vrijlaten, kan ook per jaar verschillen. Is het mogelijk om in Q2 een ander bedrag in te vullen, b.v. 150,00 euro en dat de berekening van vorige vraag, automatisch meegenomen wordt?

Formules in datums afhankelijk maken van jaartal
In kolom P en Q heb ik een overzicht van de uitgaven per maand. De formule is nu gemaakt o.b.v. 2024. Ik zou graag willen dat wanneer ik een ander jaartal in Q3 invul, dit automatisch meegenomen wordt in de formule in Q.

Cellen met formules leeglaten
Als er in kolom I bv. "nee" of "storting" staat, dan zou ik het liefste hebben dat kolom K, L en M leeg blijven en dat het bedrag alleen weergegeven wordt in kolom N.

Ik hoop dat iemand mij hierbij kan helpen

Alvast heel erg bedankt!

Groetjes!

 

[peter59]

Volgens mij gaat het al aan het begin mis.
Als ik bijvoorbeeld in kolom E de bedragen optel waarvoor in kolom I bij "meerekenen" "ja" staat, kom ik uit op €740.
Als ik daar dan het bedrag "vrijlaten" van €100 vanaf haal, hou je €640 over om verder mee te stoeien.
Of zit ik er nu helemaal naast?

 

[roeljongman]

Na wat puzzelen kom ik op het volgende.
Ik beantwoord je vragen van onder naar boven..

1. Kolom K,l,m zijn in mijn ogen overbodig die heb ik leeg gemaakt. wellicht kun je er 1 behouden om kort aan te geven dat er een vrijlating is toegepast. maar mogelijk is dat ook overbodig als je mijn oplossing ziet.

2. voor kolom P heb ik de volgende formule gebruikt om de datums dynamisch te maken.

=DATUM($Q$2;RIJ(A1);1)

rij(a1) geeft waarde 1 voor januari, is een simpel trucje om dus ook snel de maand dynamisch te maken. enjaartal komt altijd uit Q2

Voor de optelling in kolom Q:
ik gebruik altijd de sommen.als() formule ipv de som.als() omdat je bij sommen.als meerdere criteria kwijt kan. Ook is er een handig trucje om naar cellen te verwijzen en ook groter dan criteria toe te kunnen passen. Dan kom je op onderstaande formule:

=SOMMEN.ALS(Tabel7[Bedrag];Tabel7[Datum];">="&P4;Tabel7[Datum];"<="&LAATSTE.DAG(P4;0))

Dankzij het ">="& kun je een celwaarde (hier P4) groter dan en gelijk aan laten meten.
met de functie laatste.dag kun je op basis van dezelfde celwaarde heel snel de laatste dag van deze maand vinden.

als extraatje heb ik ook nog eenkolom per omschrijving opgeteld hiervoor heb ik 2 functies gebruikt die alleen werken als je Microsoft 365 abbo werkt. dat zijn functies UNIEK() en TRANSPONEREN()
kijk daarvoor maar even in het bestand. Anders wordt de uitleg wel erg lang.

3. Voor jou totaalkolom N heb ik de volgende formule bedacht. Helaas met herhaling van de sommen.als formule die 2x hetzelfde is. Maar er wordt ineens in de kolom altijd het juiste totaal voor "Ja" gegeven.
Nee lijdt tot een lege cel in kolom N.
Maar alle andere meerekenen teksten krijgen de waarde van die rij

=ALS([@Meerekenen]="nee";"";ALS([@Meerekenen]<>"ja";[@Bedrag];SOMMEN.ALS([Bedrag];[Meerekenen];"ja";[Datum];">"&LAATSTE.DAG([@Datum];-1);[Datum];"<="&[@Datum])-MIN(SOMMEN.ALS([Bedrag];[Meerekenen];"ja";[Datum];">"&LAATSTE.DAG([@Datum];-1);[Datum];"<="&[@Datum]);$Q$1)))

Ook hier zie je dat via de sommen.als de datums dynamisch worden bepaald in dit geval is het altijd het totaal tot en met de huidige regel van alle ja waarden in die maand.

Om 100 euro vrij te laten is dit deel toegevoegd.

MIN(SOMMEN.ALS([Bedrag];[Meerekenen];"ja";[Datum];">"&LAATSTE.DAG([@Datum];-1);[Datum];"<="&[@Datum]);$Q$1)))

Daarmee wordt dus altijd het laagste bedrag van de sommen.als of de ingevulde waarde in Q1 afgetrokken.
Daardoor kan er nooit een negatief bedrag uitkomen en ook niet meer afgetrokken worden dan het vrij te laten bedrag in Q1 (nu 100 euro)

Afijn, lang verhaal. maar hopelijk een eind in de goede richting.

 

[emields]

of zo?