Your question, your audience. Choose who sees your identityâ€”and your questionâ€”with question security.

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

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.

I have attached my spreadsheet to this question.

Thanks in advance for your help.

Shop-Statistics.xls

=IF(COUNTIF(G2:L2,MAX(G2:L

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

=IF(G2=VALUE(MID(F2,3,5)),

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

The choice, of course, is yours

regards, barry

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

=IF(SUM(G2:L2),TRIM(MCONCA

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

That should give the same results as my previous suggestion

regards, barry

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.

All Courses

From novice to tech pro — start learning today.

=IF(SUM(G2:L2),TRIM(IF(MAX

see attached

regards, barry

27322023.xls