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

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.

Shop-Statistics.xls

###### Who is Participating?

Commented:
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

Finance AnalystCommented:
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

Commented:
=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

Author Commented:
Thanks a lot
which formula is faster? is it the shorter formula or the or the longer formula?
0

Commented:
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

Commented:
...one last option for a shorter formula....

=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 Commented:
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
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.