?
Solved

excel unique value 2 conditions

Posted on 2013-02-01
5
Medium Priority
?
253 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
Get to Know about Lotus Notes email migration to Office 365 in detail. Explore the article for better Lotus Notes to Office 365 migration techniques to transfer all data items to the O365 domain.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

590 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