Link to home
Start Free TrialLog in
Avatar of M Zahid
M ZahidFlag for United Arab Emirates

asked on

stuck in average

I am working on a spreadsheet to take an average of prices.

However, they are not consecutive (a1:10). It looks more like this (D6, H6, L6, P6)

This information will be input on a weekly basis so there are currently $0.00 one  of the cells. When I go to average it, it takes all the cells into consideration, including the zero cell and therefore causes the average to be much lower than it should be.

Thx
SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Hello Guys – thanks for the response.

Values in D6(90.6%) , H6(0.0%), L6(61.6%), P6(0.0%) = answer should be 76.1%

Hi Rogonzo – I have tried =AVERAGEIF(A6:AZ6,"<>0") that already but it gives me the answer of 90%

Hi Shums – this is how I tried =IFERROR((SUM(D6,H6,L6,P6))/(COUNTIFS(D6:P6,">0")),"0") = 38%
Hi Rob – here is formula I used =SUMIF(D5:P5,"Percentage %",D6:P6)/COUNTIF(D5:P5,"Percentage %") = 38%

Which means it still read the two “0” & two values & taking out the average of 4 (152.2/4 = 38.05).
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rgonzo1971
Rgonzo1971

then try
=SUMIFS(D6:P6;D5:P5;"Percentage %";D6:P6;"<>0")/COUNTIFS(D5:P5;"Percentage %";D6:P6;"<>0")

Open in new window

Regards
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

Apologize for my above comments, you are right guys,

Seems like problem is with my data D6(90.6%) , H6(0.0%), L6(61.6%), P6(0.0%) which i m extracting from my source file with the macro but when I did it manually its working perfect. Let me work my data though, once again sorry guys.

But Rob it still the same ;)
Avatar of M Zahid

ASKER

Yessss  Rob & Rgonzo - SUMIFS/COUNTIFS works perfect :)

=SUMIFS(D6:P6,D5:P5,"Percentage %",D6:P6,"<>0")/COUNTIFS(D5:P5,"Percentage %",D6:P6,"<>0")

Shums its working on the manual place but still not with my data..

anyways thank you very much guys for all your help
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid

ASKER

thanks guys, ya all have given best solutions but here option is to chose only one...
Apologies to M Zahid. I had posted the simple comment above from my mobile while on the train with every intention of expanding on it once at my PC but got waylaid until now.

Thanks for the points though, glad I was able to help.
Avatar of M Zahid

ASKER

that's Fine ROB as long it got worked ;)

if i could hv option to give more than 500 would have given all u guys.

Cheers..