Forum

Notifications
Clear all

Average of numbers including Positive, Negative & Zeros

4 Posts
3 Users
0 Reactions
76 Views
(@benjamip)
Posts: 89
Estimable Member
Topic starter
 

Hi There,

I'm struggling to find the correct formula that would only give me the average of numbers Greater/Less than 0

£0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £182.37 £92.67 £52.77 -£264.54 -£383.67 -£326.57 -£206.51 -£369.86 -£164.47 -£135.84   #DIV/0! Average

I have tried AverageIf formular =(AVERAGEIF(B34:R34,">0")=AVERAGEIF(B34:R34,"<0")) but get an error,

Any help would be greatly appreciated

Regards

Paul

 
Posted : 19/01/2018 6:56 am
(@shaowu459)
Posts: 44
Eminent Member
 

=SUM(B34:R34)/COUNTIF(B34:R34,"<>0")

please try this.

 
Posted : 19/01/2018 7:38 am
(@sunnykow)
Posts: 1417
Noble Member
 

No reason why your formula is giving an error.

What error is being displayed?

What is your expected result?

If you are only interested in averaging non-zeros, then try this

=AVERAGEIF(B34:R34,"<>0")

Hope this helps.

Sunny

 
Posted : 19/01/2018 10:39 pm
(@benjamip)
Posts: 89
Estimable Member
Topic starter
 

Thanks, formula works fine

 

Regards

Paul

 
Posted : 23/01/2018 1:33 pm
Share: