Conditional average.
Posted by Hasib on November 14, 2001 5:00 PM
I need help on the following two formulas:
1. =AVERAGE(IF(AND('Tracking Data'!$Y$4:$Y$500="CLOSED",'Tracking Data'!$V$4:$V$500='Averages Data'!B4),'Tracking Data'!$W$4:$W$500))
2. =AVERAGE(IF(AND(('Tracking Data'!$V$4:$V$500=B4),('Tracking Data'!$AP$4:$AP$500<>#N/A)),'Tracking Data'!$AP$4:$AP$500))
#1 is giving me a answer of 0.0 while #2 is giving me an answer of #N/A. Any help would be greatly appreciate it!
Posted by Aladin Akyurek on November 14, 2001 5:21 PM
For (1), try:
=SUMPRODUCT(('Tracking Data'!$Y$4:$Y$500="CLOSED")*('Tracking Data'!$V$4:$V$500='Averages Data'!B4)*('Tracking Data'!$W$4:$W$500))/(MAX(1,SUMPRODUCT(('Tracking Data'!$Y$4:$Y$500="CLOSED")*('Tracking Data'!$V$4:$V$500='Averages Data'!B4))))
For (2), try:
=SUMPRODUCT(('Tracking Data'!$V$4:$V$500=B4)*(ISNUMBER('Tracking Data'!$AP$4:$AP$500))*('Tracking Data'!$AP$4:$AP$500))/(MAX(1,SUMPRODUCT(('Tracking Data'!$V$4:$V$500=B4)*(ISNUMBER('Tracking Data'!$AP$4:$AP$500)))))
Hope I didn't make mistakes with copying and pasting ranges from your formulas.
Aladin
===========
Posted by Hasib on November 15, 2001 10:19 AM
Aladin, thanks a lot for the formulas. They almost work. But the #N/As are a nuisance. #N/A seemed to be a good way to keep clean data, but they mess up formulas. Any #N/A in the data will result in the result of the formula being #N/A. I had to get rid of the #N/As in the data to get the formulas below to work. Is there any easy way around it? Any help would be greatly appreciated! Hasib..
Posted by Aladin Akyurek on November 15, 2001 10:58 AM
Hasib --
I guess it's the first one that is affected by #N/A's, not the second one. I that right?
Aladin
========= Aladin, thanks a lot for the formulas. They almost work. But the #N/As are a nuisance. #N/A seemed to be a good way to keep clean data, but they mess up formulas. Any #N/A in the data will result in the result of the formula being #N/A. I had to get rid of the #N/As in the data to get the formulas below to work. Is there any easy way around it? Any help would be greatly appreciated! Hasib.. : For (1), try
Posted by Hasib on November 15, 2001 11:07 AM
Aladin:
Actually, both of them were affected. What I noticed is that even though the second one was checking whether a particular was a number or not, in the SUMPRODUCT the result of a FALSE*FALSE*#N/A is #N/A. Actually combination with a #N/A will result in #N/A. How do I get around this. I would like to keep the #N/As in the data because it is cleaner for the type of data I have.
Thanks so much for your help!
Hasib..
Posted by Aladin Akyurek on November 16, 2001 4:05 PM
Hasib --
You use #N/A also as an entry besides as a value returned by formulas. That means that you will often have to resort to array formulas with IF in them for filtering them out. Anyway, you need array formulas in your "Averages Data". I'll reproduce here the following as an example:
{=SUM(IF(('Tracking Data'!$V$4:$V$500=B4)*(ISNUMBER('Tracking Data'!$AH$4:$AH$500)),'Tracking Data'!$AH$4:$AH$500))/MAX(1,SUM(('Tracking Data'!$V$4:$V$500=B4)*(ISNUMBER('Tracking Data'!$AH$4:$AH$500))))}
which filters out relevant rows with #N/A and does not lead to #DIV/0!
PS. Your workbook is underway to you.
Aladin
