# Averaging formula.

#### Oryan77

##### Board Regular
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:

Code:
``=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!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Maybe:

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

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!

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.

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.

Replies
1
Views
141
Replies
10
Views
352
Replies
5
Views
246
Replies
4
Views
258
Replies
9
Views
670

1,202,916
Messages
6,052,539
Members
444,591
Latest member
exceldummy774

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back