OpenOffice Calc

From Torben's Wiki
Jump to navigationJump to search

See Excel as well Open Office / Libre Ovvice oocalc / localc

Matching

A1:A10 =  ""
A1:A10 <> ""

CountIf with joined criteria of 2 columns

I want to join 2 CountIf like

=COUNTIF(A1:A10,"j") 
=COUNTIF(B1:B10,"Dresden")
--> =COUNTIFS(A1:A10,"j",B1:B10,"Dresden")
or 
--> =SUMPRODUCT((A1:A10="j"),(A1:A10="Dresden"))

Last Line Contents

Fetch contents (numbers) of last line (column D here)

=LOOKUP(1E+099;D5:D9999)

Count Items in Date Range

=SUMPRODUCT(A1:A9999>(B1-14))

Count Row A if Row B matches sth

=SUMPRODUCT(B2:B999;A2:A999>(A2-14))

Histrogram

see [1]

Ranking (Sport etc)

C holds the points. How many competitors have less points: (=your rank)

1+COUNTIF($C$2:$C$999,">"&C2)

Pivot Tables

see [2]

Strings

Substring, starting at ": ", having max 32 char length
=MID(D4;SEARCH(": " ; D4)+2 ; 32)

Time and Date

Days between Dates

=A1-A2
=DATEDIF(A2;DATE(2013;12;31);"d")

last / first day of month

# last day of current month
=EOMONTH(A2;0)
# first day of current month = last of previous + 1 day
=EOMONTH(A2;-1)+1