Calculating the Average Value based on certain criteria

It's less Excel than just math.

Use the formula:

Formula:

=(B2*B3+B4*((B3-1)*B3/2))/B6

of course it can be simplified (and one unnecessary bracket removed) to:

Formula:

=B3*(B2+B4*(B3-1)/2)/B6

but the first form explains better how was it calculated.

Notice the values in column F are:
B2+0
B2+B4 (remember B4 is negative)
B2+2*B4
...
B2+n*B4 (until n = B3-1) - so in sample case B2+2*B4 was final element

and then zeros untiil B6-th position

We take a sum of all the above elements and divide it by B6 to get the average

so B2*B3 + sum of 0+B4+2*B4+... n*B4 (n=B3-1)
But the sum of 0+1 + 2 + ... B3-1 is the sum of arithmentic progression from 0 to B3-1 (B3 elements, so it's (0+(B3-1))*B3/2 =(B3-1)*B3/2