En läsare skickade mig ett intressant formelproblem den här veckan, så tänkte att jag skulle dela det som en formelutmaning. Problemet är detta:
Du har en fast månadsbetalning, ett startdatum och ett visst antal månader. Vilken formel kan du använda för att summera totala betalningar per år, baserat på följande kalkylblad:
Med andra ord, vilken formel fungerar i E5, kopierad till I5, för att få en summa för varje visat år?
Jag kom fram till en formel själv, men jag skulle också gärna vilja se dina idéer. Om du är intresserad, lämna en kommentar med den formel du föreslår.
Du kan använda följande namnområden i din formel om du vill: mos (C5), mängd (C6), start (C7), slut (C8).
Du kan ladda ner kalkylbladet nedan.
Svar (klicka för att expandera)Så många fantastiska formler! Tack till alla som tog sig tid att skicka ett svar. Nedan följer mina vandrade tankar om problemet och några av lösningarna nedan.
Obs! Jag klargjorde aldrig hur månadsgränser ska hanteras. Jag följde bara ett annat kalkylblad som ett exempel. De viktigaste uppgifterna är 30 betalningar, från och med den 1 mars: 10 betalningar 2017, 12 betalningar 2018 och 8 betalningar (balansen) 2019.
Så om du kämpar för att förstå hur du kan försöka lösa ett problem som detta, fokusera först på betalningar. När du väl vet hur många betalningar som sker per år kan du bara multiplicera det numret med beloppet och du är klar.
Så, hur kan du hitta antalet betalningar under ett visst år? I kommentarerna nedan hittar du massor av bra idéer. Det finns flera mönster jag har märkt, och jag har listat några nedan. Detta är ett pågående arbete …
Design mönster
IF + AND/OR + YEAR + MONTH
IF är en pålitlig beredskap i så många formler, och den används i många av de föreslagna formlerna för att ta reda på om intresseåret är "inom gränserna" för start- och slutdatum. I många fall kombineras IF med OR eller AND för att hålla formlerna kompakta.
IFERROR + DATEDIF + MAX + MIN
DATEDIF kan returnera skillnaden mellan två datum i månader, så tanken här är att använda MAX och MIN (för korthet istället för IF) för att beräkna ett startdatum och slutdatum för varje år och låta DATEDIF få månaderna mellan. DATEDIF kastar ett #NUM-fel när startdatumet inte är mindre än slutdatumet, så FEL används för att fånga upp felet och returnera noll. Se formler av 闫 强, Arun och David nedan.
MAX + MIN + YEAR + MONTH
Robert och Peters formler gör nästan allt arbete med MAX och MIN, utan IF i sikte. Fantastisk. Om tanken att använda MAX och MIN för att ersätta IF är ny för dig förklarar den här artikeln konceptet.
DAYS360
Excel DAYS360-funktionen returnerar antalet dagar mellan två datum baserat på ett 360-dagarsår. Det är ett sätt att beräkna månader baserat på idén att varje månad har 30 dagar.
SUM + DATE
Detta är mitt ineffektiva (men eleganta!) Tillvägagångssätt med DATE-funktionen och en arraykonstant med ett nummer för varje månad. DATE-funktionen snurrar upp ett datum för varje månad på ett år med hjälp av en arraykonstant och boolesk logik används för att kontrollera överlappning.
