Solved

excel unique value 2 conditions

Posted on 2013-02-01
5
209 Views
Last Modified: 2013-02-04
Win XP SVC Pack3   XP 2007 SVC 2  uniquevalue2conditions.xls

=AVERAGEIFS('3 Months Data'!$I:$I,'3 Months Data'!$M:$M,2,'3 Months Data'!$P:$P,0)
Tells me average charges column I where DOW column M = 2 (Monday) and not a holiday or a special day column P = 0

I want average number of customers on Monday
=SUMIFS('3 Months Data'!$Q:$Q,'3 Months Data'!$M:$M,2,'3 Months Data'!$P:$P,0)
Tells me total numbers of Monday customers Column Q = Cases 1 per line   867
{=SUM(IF(FREQUENCY('3 Months Data'!$B:$B,'3 Months Data'!$B:$B)>0,1))}
Tells me number of unique dates, but not number of unique mondays  88

I was hoping this would tell me number of unique mondays but it shows 0
{=SUM(IF(AND(FREQUENCY('3 Months Data'!$B:$B,'3 Months Data'!$B:$B)>0,FREQUENCY('3 Months Data'!$M:$M,'3 Months Data'!$M:$M)=2),1))  }  I am looking for the number 10

Then I could divide formula one by formula two and get that there are 86.7 average customers on a Monday.  I am editing it and saving with Cntrl+Shift+Enter, but I am an arrray beginner.  This way is most likely the long way, but I can explain it to the boss
UniqueValue2conditions.xls
0
Comment
Question by:avgplusguy
  • 3
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 38845265
I would suggest you reduce the range sizes for these array formulas to max needed...

I get 12 dates (I filtered column M for 2 to check)

=SUM(IF(FREQUENCY(IF('3 Months Data'!M:M=2,IF('3 Months Data'!B:B<>"",MATCH('3 Months Data'!B:B,'3 Months Data'!B:B,0))),ROW('3 Months Data'!B:B)-ROW('3 Months Data'!B1)+1),1))

cse confirmed after you change range sizes to max needed... not whole columns.
0
 

Author Comment

by:avgplusguy
ID: 38845360
December 24th and December 31 are special days with ones in column p and need to be excluded bringing total to 10. I could limit column B to 15,998 and the holidays / special days to 30 staying under the magical 32 and 16,000 numbers
0
 

Author Comment

by:avgplusguy
ID: 38845573
works so NB_VC is gettting some points
{=SUM(IF(FREQUENCY(IF('3 Months Data'!$M$2:$M$15998=2,IF('3 Months Data'!$P$2:$P$15998=0,IF('3 Months Data'!$B$2:$B$15998<>"",MATCH('3 Months Data'!$B$2:$B$15998,'3 Months Data'!$B$2:$B$15998,0)))),ROW('3 Months Data'!$B$2:$B$15998)-ROW('3 Months Data'!B1)+1),1))}


does not work
=SUM(IF(FREQUENCY('3 Months Data'!$M$2:$M$15998=2,IF('3 Months Data'!$P$2:$P$15998=0,IF('3 Months Data'!$B$2:$B$15998<>"",))),1))}

I need something closer to B instead of A that works
Boss gets concerned everytime he finds a match in one of my formulas.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38850942
Why is your boss concerned when it works?
0
 

Author Closing Comment

by:avgplusguy
ID: 38851858
Put me on right track.  Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now