Help Calculating A Simple Moving Average

[artiststevens]

Greetings,
I need help calculating a simple moving average. I can formulate the basic formula of adding up all of the values and then dividing. The problem is I’d like some of the values not to be counted when calculated and for this I need a formula. All of the values with an “X” next to them need to be skipped. This is a 100 period moving average so it would be the sum of 100 values divided by 100 to get the SMA.

The only difference as stated is I want every value with an “X” next to it to be skipped and not included in the 100 values used to calculate the SMA. A spreadsheet is attached giving an example of what I need and more information. Thank you in advance for any and all help.

[oeldere]

Maybe with an pivot table.

See the attached file.

[artiststevens]

Maybe with an pivot table.

See the attached file.

Thank you for the help oeldere. I'm not very familiar with pivot tables and I need a formula because I'm calculating other formulas on the same sheet, so a formula would be more ideal.

[oeldere]

With sumproduct.

See the attached file (the orange cells).

[Fotis1991]

Do you mean?

=AVERAGE(IF(D2:D114<>"X",C2:C114))

Array formula.

[artiststevens]

Hello Fotis,
Thank you for the help! The array worked. I think it needs to be changed slightly. It appears to be rounding the numbers down.

Manual Array
1.0422 1.0421
1.0422 1.0422
1.0423 1.0422
1.0424 1.0423
1.0424 1.0423
1.0426 1.0425
1.0427 1.0426
1.0429 1.0428
1.0430 1.0429
1.0432 1.0431
1.0433 1.0433
1.0435 1.0434
1.0436 1.0435
1.0438 1.0437
1.0439 1.0438
1.0440 1.0439
1.0441 1.0440

[Fotis1991]

Not sure that i am able to follow you...

Do you put this formula to a specific cell and copied down?

[artiststevens]

I was able to make it work. Thank you Fotis.