# Compare with average

#### texasalynn

##### Well-known Member
I'm so deep in the forest I can't see the trees! Help me please.

I am trying to compare each month to the years average and see if it is within 10%+/-

I have this:
=IF(B5=10%>AVERAGE(B5:U5)<10%,"YES","NO")

but that's not getting it!!!! What am I doing wrong? Or I'm completely offbase?

Thanks . . .
texasalynn

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Maybe:

=IF(ABS(B5-AVERAGE(B5:U5))<10%,"Yes","No")

assuming that the numbers in B5:U5 are in %.

Maybe this will help explain it better
Book7
ABCDEFGHIJKLMNOPQR
3SORT_CODEJan-02Feb-02Mar-02Apr-02May-02Jun-02Jul-02Aug-02GrandTotalJan-02Feb-02Mar-02Apr-02May-02Jun-02Jul-02Aug-02
43763,180.00795.00795.00795.005,565.00NONONONONONONONO
55231,056.581,018.23974.53504.231,445.61845.735,844.91NONONONONONONONO
6532818.06818.06818.06818.06818.06818.06818.06818.066,544.48NONONONONONONONO
7533995.911,114.321,443.741,182.251,538.091,276.631,295.958,846.89NONONONONONONONO
8
Sheet1

Thanks . .
texasalynn

Just a thought, does this work?

=IF(B5=AVERAGE(B5:I5)<>10%,"YES","NO")

The figure shows an OR formula. Why, if it's not correct?

What is the expected result for the value in B4 in the figure? I guess the question is 10% of what?

Look at G6 - this figure is within the 10%+/- of the average. So I would expect my formula to give "YES" as a result. So my "OR" may be meaningless. I think I need a between.
So if the average for the G6 line is \$802.70 the +10% = 882.9689 and the
-10% = 801.60

Does that help?

thanks . . .texaslaynn

=IF(AND(B3>=AVERAGE(B3:I3)*(0.9),B3<=AVERAGE(B3:I3)*(1.1)),"Yes","No")

If you compute the average just like you compute the grand total, you can replace the AVERAGE bit in the above formula with the cell where the respective average is computed.

You might also want to replace >= with > and <= with <.

Replies
7
Views
233
Replies
11
Views
504
Replies
4
Views
177
Replies
5
Views
325
Replies
1
Views
135

1,206,971
Messages
6,075,925
Members
446,170
Latest member
zzzz02

### 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