Using HLOOKUP and MAX functions to find the most commonly occurring weekday in a list

[mriddell93]

Hi all,

I'm trying to create a single-line formula that will count the day of the week (from a list) that occurs most commonly. The catch is that I MUST use the MAX and HLOOKUP functions together in the formula.

My outline defines it as so: "On the first worksheet develop a single line formula using the HLOOKUP and MAX functions to find the day of the week with the maximum number of customers."

Attached is the spreadsheet of data. I'm attempting to do this for the days of the week from Column D (Scheduled Day).

Thanks

[Pete_UK]

It strikes me that if you HAVE to use certain functions then this is most likely to be a school assignment or homework, though you didn't state that above. How can you hope to learn to do these things yourself if someone just gives you the answer?

Pete

[mriddell93]

You're correct, it is a school assignment. I've worked with Excel in vast amounts in my previous internships, as well as taken various courses on the program. This may be a simple fix, but I've attempted to solve it and cannot figure it out.

Any input you have is appreciated.

[sktneer]

In cell K12 put the formula given below and confirm it with Ctrl+Shift+Enter because its an array formula.

Please Login or Register  to view this content.

Hope it helps.

[mriddell93]

Thanks for the quick response!

[martindwilson]

well that doesn't use hlookup !!!!
i dont normally do homework but its hard to point you in the right direction without giving an answer
is the table J6:M8 already there? or did you add it?
=HLOOKUP(MAX(K7:M7),K7:M8,2,FALSE)
would work but it has drawbacks ,what if 2 values are the same?