Solved

How do I show the most visited days of the week with Excel. Part of the formula included.

Posted on 2011-09-22
7
256 Views
Last Modified: 2012-06-27
Hi

We check with an Excel spreadsheet how often someone visits a shop. We calculate how often someone visited the shop on a Monday, Tuesday etc. I have a formula which works.

Excel should display the most visited day of a week.  I use a MAX() to check for the highest number. It can happen that there isn't a a most visited day. Maybe there are two ore more Max() numbers. My formula displays the first highest day of the day only. It should display both or more days.
Have a look Walmart: The most visited days are Tuesday and Friday. What should I do in order for (example) cell C3 to display the names "Thursday" and "Friday".

I have attached my spreadsheet to this question.

Thanks in advance for your help.


 Shop-Statistics.xls
 Part of the spreadsheet Part of the spreadsheet
0
Comment
Question by:Massimo Scola
[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
7 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 36580455
Try this formula in E2 copied down

=IF(SUM(G2:L2),TRIM(IF(MAX(G2:L2)=G2,G$1,"")&" "&IF(MAX(G2:L2)=H2,H$1,"")&" "&IF(MAX(G2:L2)=I2,I$1,"")&" "&IF(MAX(G2:L2)=J2,J$1,"")&" "&IF(MAX(G2:L2)=K2,K$1,"")&" "&IF(MAX(G2:L2)=L2,L$1,"")),"")

see attached

regards, barry
27322023.xls
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 125 total points
ID: 36580569
A combination of the MAX formula in E2 and the INDEX formula in E3 down:

=IF(COUNTIF(G2:L2,MAX(G2:L2))>1,IF(MAX(G2:L2)=G2,"Monday & ","")&IF(MAX(G2:L2)=H2,"Tuesday & ","")&IF(MAX(G2:L2)=I2,"Wednesday & ","")&IF(MAX(G2:L2)=J2,"Thursday & ","")&IF(MAX(G2:L2)=K2,"Friday & ","Saturday"),INDEX(G$1:L$1,MATCH(MAX(G2:L2),G2:L2,)))

This says IF the count of the max value is more than 1 then evaluate each value and put the day and an & for combining in a string with the next day. If the count is only 1 then do the INDEX formula as before.

It might look like there is too many nested IF statements but each IF is closed off as a separate statement, the false option being the "" rather than moving to the next IF.

Thanks
Rob H
0
 
LVL 2

Assisted Solution

by:sanofi-aventis
sanofi-aventis earned 125 total points
ID: 36580913
How about this one:
=IF(G2=VALUE(MID(F2,3,5)), G1, "") &
 IF(H2=VALUE(MID(F2,3,5)), IF( VALUE(MID(F2,1,1))<2, "; ", "" ) & $H$1, "") &
 IF(I2=VALUE(MID(F2,3,5)), IF( VALUE(MID(F2,1,1))<3, "; ", "" ) & $I$1, "") &
 IF(J2=VALUE(MID(F2,3,5)), IF( VALUE(MID(F2,1,1))<4, "; ", "" ) & $K$1, "") &
 IF(K2=VALUE(MID(F2,3,5)), IF( VALUE(MID(F2,1,1))<5, "; ", "" ) & $J$1, "") &
 IF(L2=VALUE(MID(F2,3,5)), IF( VALUE(MID(F2,1,1))<6, "; ", "" ) & $L$1, "")
To simplify the formula I put part of the logic in column F to calculate the Max and first occurance. This logic could be added to the main formula or the values in column F could be hidded.
=MATCH( MAX(G2:L2), G2:L2, 0) & " " & MAX(G2:L2)

Just a different way to skin the cat.

Sincerely,
T-Bone
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Massimo Scola
ID: 36580982
Thanks a lot
which formula is faster? is it the shorter formula or the or the longer formula?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36581225
Speed wise there's probably little in it but I imagine that my suggestion will be marginally fastest because it uses less function calls. It also has the advantage of returning a blank if all rows are blank or zero so I recommend mine .......but I would say that wouldn't I?!

The choice, of course, is yours

regards, barry
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 36581323
...one last option for a shorter formula....

Download Morefunc add-in which allows you to use MCONCAT function then use this formula in E2

=IF(SUM(G2:L2),TRIM(MCONCAT(IF(G2:L2=MAX(G2:L2),G$1:L$1,"")," ")),"")

confirmed with CTRL+SHIFT+ENTER and copied down the column

That should give the same results as my previous suggestion

regards, barry
0
 

Author Closing Comment

by:Massimo Scola
ID: 36585482
Hi barry

I chose your solution because it was easier for me to understand at first glance.
Thanks a lot to you guys! I really appreciate your help.

Regards
Massimo
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
converting excel into labels 6 36
INSERT value into first row in 75 workbooks 8 35
count values within multiple bands 7 33
Userform to show a range in excel. 3 32
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

737 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