Solved

excel unique value 2 conditions

Posted on 2013-02-01
5
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

707 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