M Zahid
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
then try
=SUMIFS(D6:P6;D5:P5;"Percentage %";D6:P6;"<>0")/COUNTIFS(D5:P5;"Percentage %";D6:P6;"<>0")
Regards
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ;)
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 ;)
ASKER
Yessss Rob & Rgonzo - SUMIFS/COUNTIFS works perfect :)
=SUMIFS(D6:P6,D5:P5,"Perce ntage %",D6:P6,"<>0")/COUNTIFS(D 5:P5,"Perc entage %",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
=SUMIFS(D6:P6,D5:P5,"Perce
Shums its working on the manual place but still not with my data..
anyways thank you very much guys for all your help
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Thanks for the points though, glad I was able to help.
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..
if i could hv option to give more than 500 would have given all u guys.
Cheers..
ASKER
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)
Hi Rob – here is formula I used =SUMIF(D5:P5,"Percentage %",D6:P6)/COUNTIF(D5:P5,"P
Which means it still read the two “0” & two values & taking out the average of 4 (152.2/4 = 38.05).