Zestien Excelformules voor de architect

Iedere architect zou deze formules moeten kennen, tenminste volgens dit artikel.

Maar vergeet het belangrijkste niet:

The key thing to remember when writing formulas for Excel is that all formulas must begin with an equals sign (=). This is because the cell contains—or is equal to—the formula and its value.

1.     SUM It sums all the values within a defined range, for a single or multiple rows or columns. =SUM(A1:F1)=SUM(A1:A7)
2.     MIN It gives the “smallest” value within a defined range. =MIN(A1:F1)=MIN(A1:A7)
3.     MAX It gives the “largest” value within a defined range. =MAX(A1:F1)=MAX(A1:A7)
4.     AVERAGE It calculates the average / Arithmetic mean for a defined range. =AVERAGE(A1:F1)=AVERAGE(A1:A7)
5.     COUNT It counts the cells containing numbers within a defined range. =COUNT(A1:F1)=COUNT(A1:A7)
6.     COUNTA It counts all non-empty cells within a defined range, regardless the content. =COUNTA(A1:F1)=COUNTA(A1:A7)
7.     COUNTBLANK It counts the empty cells within a defined range. =COUNTBLANK(A1:F1)=COUNTBLANK(A1:A7)
8.     IF It gives one of two different outcomes depending on whether a condition is satisfied or not. =IF(Condition, “if true value”, “if false value”)=IF(A1<B1, “Yes”, “NO”)
9.     SUMIF It operates the SUM only if a given condition is satisfied. =SUMIF(B1:B7, “<100”)
10.  SUMIFS It operates the SUM only if multiple conditions are satisfied. =SUMIFS(B1:B7, B1:B7, “>10”, B1:B7, “<100”)
11.  COUNTIF It counts cells with numbers that satisfy the specified conditions only. =COUNTIF(A1:A7, “>10″)
12.  ROUND It rounds numbers to specified number of digits. =ROUND(8.39,1). . 8.39 will be 8.4
13.  ROUNDUP It defines the direction of the rounding to upwards. =ROUNDUP(8.39,0). . 8.39 will be 9
14. ROUNDDOWN It defines the direction of the rounding to upwards. =ROUNDDOWN(8.39, 1) . . 8.39 will be 8.3
15. Floor It rounds a number down to a specified multiple. =Floor(B2, 1000)e.g. 1350 will be 1000
16. Ceiling It rounds a number up to a specified multiple. =Ceiling(B2, 1500)e.g. 1350 will be 1500