Torben at 20:49, 30 October 2024

2024-10-30T20:49:58Z

New page

[[Category:Software]]
See [[OpenOffice_Calc|LibreOffice Calc]] as well

===Shortcuts===
* Insert Date / Datum einfügen CTRL+SHIFT+;

===Tips for working faster===
from [https://exceljet.net/29-ways-to-save-time-with-excel-formulas]

* 5. Use a table to enter formulas automatically
* 9. Insert function argument placeholders with a shortcut <br>if you type "=DATE(" and then use Control + Shift + A, Excel will give you "=DATE(year,month,day)". You can then double-click each argument (or use the Function tip window to select each argument) and change it to the value you want.
* 11. Toggle the display all formulas at once via CTRL + ~
* 14. Use Paste Special to adjust values in place <br> to convert a set of dates in place to one week later, do this: add the number 7 to any cell in the worksheet, then copy it to the clipboard. Next, select all of the dates you want to change. Then, use Paste Special > Operations > Add. When you click OK, Excel will add the number 7 to the dates you've selected
* 15. Use named ranges to make formulas more readable (B3 * $A$1 -> B3 * const1)
* 16. Apply names of named ranges to existing formulas (Formulas -> Define Name -> Apply Name)
* 19. Use (Win: F4 / Mac: Ctrl + T) to toggle relative and absolute references
* 22. Formula debugging (Windows Evaluate Formula from the formula menu / Mac: Formula builder)
* 24. Use named ranges like variables (store text snippets for reuse in named cells)
* 26. Add line breaks to nested IFs to make them easier to read
* 29. Enter the same formula in multiple cells at once (1. select, 2 write in first cell, 3 CTRL + Enter)

===Basic Operations===
TO exclude calculated nullvalues from chart plot, replace them with "not available":
=NA()

Weeknumber / Kalenderwoche
=IF(A1<WEEKNUM(TODAY());COUNT(A3:A55);#NV)

====Join Strings====
="Some String" & B8
DE: =VERKETTEN("Some String" ; B8)
EN: =CONCATENATE("Some String" ; B8)

====Substrings====
=RIGHT(A1;3)
=LEFT(A1;SEARCH("/";A1)-1)
=IF(
ISNUMBER(
SEARCH(" / ";Table4[@[MyCol2]])
);
RIGHT(Table4[@[MyCol2]];3);
""
)

====Search & Replace====
EN: =SUBSTITUDE(D2;".";"")
DE: =WECHSELN (D2;".";"")

====Find value of last numeric cell of column====
=LOOKUP(2;1/(ISNUMBER(T2:T9999));T2:T9999)
# corresponding row number
=LOOKUP(2;1/(ISNUMBER(T3:T10000));ROW(T3:T10000))

===Comparing Cells===
====filtered Sum====
Data: in A and B
Goal: Filter column A and give the sum oft the corresponding B values
EN: =SUMIF(A:A; "foo";B:B)
DE: =SUMMEWENN(A:A; "foo";B:B)
for is empty / isblank
=SUMIF(A:A;"<>"&"";B:B)
or
EN: =SUMIFS(B:B;"foo";A:A)
DE: =SUMMEWENNS(B:B;"foo";A:A)
(allows several filters)
or
EN: =SUMPRODUCT(--(A:A="foo");(B:B))
DE: =SUMMENPRODUKT(--(A:A="foo");(B:B))
-- converts boolean values to 0 and 1

Sum of a year
=SUMIFS(B:B;A:A;">=01.01.2018";A:A;"<01.01.2019")
better:
=SUMPRODUCT(--(YEAR(A:A)=2018);B:B)

Using date and today() function
=SUMIFS($C:$C;$A:$A;">="&today())

====Check if C in A====
Data: in A and C
Goal: Check each value of C if it is in A
=VERGLEICH(C1;A:A;0)
0 3rd parameter tells Excel to check for exact matches
if found the row nr is returned
else #nv
D1 : =VERGLEICH(C1;A:A;0)
E1 : =WENN(ISTFEHLER(D1);"";D1)

====Check if C in A and if so return the value of B correspondig to A====
Data: in A and C
D1 : =VERGLEICH(C1;A:A;0)
E1 : =INDIREKT(ADRESSE(D1;2))
2 -> column B
F1 : =WENN(ISTFEHLER(E1);"";E1)

====Find corresponding X-value to max(Y)====
EN: =INDEX(A:A;MATCH(MAX(B:B);B:B;0))
DE: =INDEX(A:A;VERGLEICH(MAX(B:B);B:B;0))

===Statistics===
====Weighted Average====
from https://blog.hubspot.com/marketing/weighted-average-excel
for A: values, B: weight
EN: =SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10)

===Generate Sequence of Dates===
="01.10.2022"+SEQUENCE(1+TODAY()-"01.10.2022";;0)

===VLookup===
Source data
A: Dates with gaps
B: Corresponding values

Target
D: Sequence of dates, see above
E: Value of B, that has the nearest lower value A to D
=VLOOKUP(D2;A:B;2;TRUE)

Excel - Revision history

Torben at 20:49, 30 October 2024