Averaging formula.

[Oryan77]

I have cell F30 which contains a formula at the moment that averages out cells F7:F25 and divides by the value in cell F32:

=IF(F32>0,ROUNDUP(AVERAGE(F7:F25),0),0)

I need to add the following rules to it but I am not sure how to get it to work:

For every 2 above 4 in cell F32, add 2 to the final average.

Example #1 : F32 = 6, the average (rounded up) of cells F7:F25 = 5, then add 2 so F30 = 7.
Example #2 : F32 = 8, the average (rounded up) of cells F7:F25 = 5, then add 4 so F30 = 9.

I also need the same thing to happen for every 2 less than 4 in cell F32, then subtract 2 from the final average, but minimum value = 1.

I hope that explains things enough. Thanks!

 

[Eric W]

Maybe:

=MAX(IF(F32>0,ROUNDUP(AVERAGE(F7:F25),0),0)+SIGN(F32-4)*INT(ABS(F32-4)/2)*2,1)

 

[Oryan77]

That worked perfectly. Just in case I need it later (if you don't mind), what would that code be if I wanted to alter it to the following rule:

For every 1 above 4 in cell F32, add 1 to the final average (and same in reverse, for every 1 less than 4 in cell F32, then subtract 1 from the final average, but minimum value = 1)

instead of how I initially asked for it to be (For every 2 above 4 in cell F32, add 2 to the final average)? I'm just wondering where I might change that if I needed too. Thanks!

 

[Oryan77]

Maybe:

=MAX(IF(F32>0,ROUNDUP(AVERAGE(F7:F25),0),0)+SIGN(F32-4)*INT(ABS(F32-4)/2)*2,1)

Sorry, I have another request that is very similar to this formula, except it is for another sheet.

What would that same formula be if it also included the following rule:

If D7 is blank, do not include F7 when calculating the average if there is data in F7. This rule would be the same for every cell from D7 to D25 with F7 to F25 being the corresponding cells in the formula that may or may not have data entered.

 

[Eric W]

For your first question, just change the red 2s to 1s:

=MAX(IF(F32>0,ROUNDUP(AVERAGE(F7:F25),0),0)+SIGN(F32-4)*INT(ABS(F32-4)/2)*2,1)


For your second question, change the AVERAGE part from

... AVERAGE(F7:F25) ...

to

... AVERAGEIF(D7:D25,"<>",F7:F25) ...

This should work as long as the D7:D25 range has data, not formulas.