forked from unidoc/unioffice
-
Notifications
You must be signed in to change notification settings - Fork 0
/
fnstatistical.go
233 lines (213 loc) · 5.39 KB
/
fnstatistical.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
// Copyright 2017 Baliance. All rights reserved.
//
// Use of this source code is governed by the terms of the Affero GNU General
// Public License version 3.0 as published by the Free Software Foundation and
// appearing in the file LICENSE included in the packaging of this file. A
// commercial license can be purchased by contacting sales@baliance.com.
package formula
import (
"math"
"sort"
"baliance.com/gooxml"
)
func init() {
RegisterFunction("AVERAGE", Average)
RegisterFunction("AVERAGEA", Averagea)
RegisterFunction("COUNT", Count)
RegisterFunction("COUNTA", Counta)
RegisterFunction("COUNTBLANK", CountBlank)
RegisterFunction("MAX", Max)
RegisterFunction("MIN", Min)
RegisterFunction("MEDIAN", Median)
}
func sumCount(args []Result, countText bool) (float64, float64) {
cnt := 0.0
sum := 0.0
for _, arg := range args {
switch arg.Type {
case ResultTypeNumber:
sum += arg.ValueNumber
cnt++
case ResultTypeList, ResultTypeArray:
s, c := sumCount(arg.ListValues(), countText)
sum += s
cnt += c
case ResultTypeString:
if countText {
cnt++
}
case ResultTypeEmpty: // do nothing
}
}
return sum, cnt
}
// Average implements the AVERAGE function. It differs slightly from Excel (and
// agrees with LibreOffice) in that boolean values are counted. As an example,
// AVERAGE of two cells containing TRUE & FALSE is 0.5 in LibreOffice and
// #DIV/0! in Excel. gooxml will return 0.5 in this case.
func Average(args []Result) Result {
sum, cnt := sumCount(args, false)
if cnt == 0 {
return MakeErrorResultType(ErrorTypeDivideByZero, "AVERAGE divide by zero")
}
return MakeNumberResult(sum / cnt)
}
// Averagea implements the AVERAGEA function, AVERAGEA counts cells that contain
// text as a zero where AVERAGE ignores them entirely.
func Averagea(args []Result) Result {
sum, cnt := sumCount(args, true)
if cnt == 0 {
return MakeErrorResultType(ErrorTypeDivideByZero, "AVERAGE divide by zero")
}
return MakeNumberResult(sum / cnt)
}
type countMode byte
const (
countNormal countMode = iota
countText
countEmpty
)
func count(args []Result, m countMode) float64 {
cnt := 0.0
for _, arg := range args {
switch arg.Type {
case ResultTypeNumber:
if m != countEmpty {
cnt++
}
case ResultTypeList, ResultTypeArray:
cnt += count(arg.ListValues(), m)
case ResultTypeString:
if m == countText {
cnt++
}
case ResultTypeEmpty:
if m == countEmpty {
cnt++
}
}
}
return cnt
}
// Count implements the COUNT function.
func Count(args []Result) Result {
return MakeNumberResult(count(args, countNormal))
}
// Counta implements the COUNTA function.
func Counta(args []Result) Result {
return MakeNumberResult(count(args, countText))
}
// CountBlank implements the COUNTBLANK function.
func CountBlank(args []Result) Result {
// COUNT and COUNTA don't require arguments, COUNTBLANK does
if len(args) == 0 {
return MakeErrorResult("COUNTBLANK requires an argument")
}
return MakeNumberResult(count(args, countEmpty))
}
// Min is an implementation of the Excel MIN() function.
func Min(args []Result) Result {
if len(args) == 0 {
return MakeErrorResult("MIN requires at least one argument")
}
v := math.MaxFloat64
for _, a := range args {
a = a.AsNumber()
switch a.Type {
case ResultTypeNumber:
if a.ValueNumber < v {
v = a.ValueNumber
}
case ResultTypeList, ResultTypeArray:
subMin := Min(a.ListValues())
if subMin.ValueNumber < v {
v = subMin.ValueNumber
}
case ResultTypeString:
// treated as zero by Excel
if 0 < v {
v = 0
}
case ResultTypeEmpty:
// skip
case ResultTypeError:
return a
default:
gooxml.Log("unhandled MIN() argument type %s", a.Type)
}
}
if v == math.MaxFloat64 {
v = 0
}
return MakeNumberResult(v)
}
// Max is an implementation of the Excel MAX() function.
func Max(args []Result) Result {
if len(args) == 0 {
return MakeErrorResult("MAX requires at least one argument")
}
v := -math.MaxFloat64
for _, a := range args {
a = a.AsNumber()
switch a.Type {
case ResultTypeNumber:
if a.ValueNumber > v {
v = a.ValueNumber
}
case ResultTypeList, ResultTypeArray:
subMax := Max(a.ListValues())
if subMax.ValueNumber > v {
v = subMax.ValueNumber
}
case ResultTypeEmpty:
// skip
case ResultTypeString:
// treated as zero by Excel
if 0 > v {
v = 0
}
default:
gooxml.Log("unhandled MAX() argument type %s", a.Type)
}
}
if v == -math.MaxFloat64 {
v = 0
}
return MakeNumberResult(v)
}
func extractNumbers(args []Result) []float64 {
values := make([]float64, 0)
for _, a := range args {
if a.Type == ResultTypeEmpty {
continue
}
a = a.AsNumber()
switch a.Type {
case ResultTypeNumber:
values = append(values, a.ValueNumber)
case ResultTypeList, ResultTypeArray:
values = append(values, extractNumbers(a.ListValues())...)
case ResultTypeString:
// treated as zero by Excel
default:
gooxml.Log("unhandled extractNumbers argument type %s", a.Type)
}
}
return values
}
// Median implements the MEDIAN function that returns the median of a range of
// values.
func Median(args []Result) Result {
if len(args) == 0 {
return MakeErrorResult("MEDIAN requires at least one argument")
}
values := extractNumbers(args)
sort.Float64s(values)
var v float64
if len(values)%2 == 0 {
v = (values[len(values)/2-1] + values[len(values)/2]) / 2
} else {
v = values[len(values)/2]
}
return MakeNumberResult(v)
}