Trick on Averaging numbers

[rbpd5015]

I am looking for the correct way to average a group of numbers.

I know the formula but it doeskin work correct.

I am trying to average this numbers below.

Now when I do this I use this formula

=AVERAGE(G1:G17)

and get this result
-33.079

Now the only problem is, with baseball lines +100 and -100 are both the same and there is never and two or one digit lines like
-4
or -88

its always at min -101 or +101

127
163
146
-119
-124
-139
119
-133
-113
-137
-130
-142
-101
-104
114
111
-111

[Ron Coderre]

Try this regular formula:

Please Login or Register  to view this content.

or...if you prefer an ARRAY FORMULA, which is committed with CTRL+SHIFT+ENTER (instead of just ENTER):

Please Login or Register  to view this content.

With your sample data, those formulas return: 125.470588235294

Does that help?

[rbpd5015]

ron, it works, but it seems to be backwards.

it produces the number as positive when it should be negative and negative when positive? whats the trick to flip that?


For example the average of...
-108
-232
-235
-182
-142
-154
-140
-136
-122


is 161.222 it should be I think -162.222

Matt

[shg]

If you just had +100 and -100, what should the result be?

[Ron Coderre]

I think we could use more samples and expected results.

Meanwhile...Maybe this array formula? (committed with C+S+E)

Please Login or Register  to view this content.

Does that help?

[rbpd5015]

shg good point I have always wondered the same thing. It is sports betting lines so I do not know. I believe they always write it as +100 and -101. I could be wrong though.

Matt

[rbpd5015]

I know this at +100 = 1 for 1

at -1.01 you must bet 1.01 to win 1

or a normal line is -110 so bet 110 to win 1

where as 145 is bet 1.00 and win 1.45

or bet 1/1.45 (.68) to win 1.00

Matt

[shg]

Don't go away ...

[rbpd5015]

??? I wont go away I am here

[shg]

So positive numbers actually mean reciprocals. In that case, this formula tells you how much you have to bet to win a buck (no blanks allowed in the range):

=SUMPRODUCT( (G1:G17<0) * -G1:G17 / 100 + (G1:G17>0) * 100 / G1:G17) / ROWS(G1:G17)

So, for example,

Please Login or Register  to view this content.

I1 has the formula above, which says, on average, you need to be $0.976 to win a dollar.

The formula in I2 converts that to the conventional form: =100 * IF(I1>1, -I1, 1/I1)

[rbpd5015]

shg,

Thanks man I acutally have that and sorry I should have posted that answer. The problem I am having is.

I want to create the average game line. I was just explaining the bet part becuase you asked about +100 and -100.

Basically I want to look at a list like
-108
-232
-235
-182
-142
-154
-140
-136
-122

and for it to spit ot and average like -161.22

Matt

[shg]

OK, here it is with those numbers:

Please Login or Register  to view this content.

[rbpd5015]

thanks worked perfect

Matt