VLOOKUP Issues

[tapsmiled]

Hi,

In the attached workbook on the FRI sheet (F83) I have a VLOOKUP formula that is giving me an error. =VLOOKUP(MAX(C32:D39,C54:D61,C71:D78),(C32:D39,C54:D61,C71:D78),1,FALSE)

I want the formula to look through the C32:D78 ranges to find the greatest value and then display the corresponding value from column A, which is a time. I compiled the formula from something I saw on another site, but it is giving me the #N/A error. Eventually, this formula will make its way onto each page of the woorkbook.

Extra points for someone who can provide the formula to take all 7 of these eventual results (1 from each sheet) and add them to the actual SUMMARY worksheet in a yet to be determined field...especially if it can show the most-frequently occuring value if there is one.

Thanks in advance!!!

[FDibbins]

I would suggest that you do away with ALL of the merged cells/columns that you have there - they cause nothing but problems, and are in fact another reason that your formula is not working...
=VLOOKUP(MAX(C32:D39,C54:D61,C71:D78),(C32:D39,C54:D61,C71:D78),1,FALSE)
needs to be...
=VLOOKUP(MAX(C32:C39,C54:C61,C71:C78),C32:C78,1,FALSE)

And if you change your SUM() formulas for the TOTAL's, to this...
=SUBTOTAL(9,C71:D78)
Then you can simplify it further tho this...
=VLOOKUP(SUBTOTAL(4,C32:C79),C32:C78,1,FALSE)

[TMS]

I think you are getting the C:D ranges because of the merged cells.

This can be:

Please Login or Register  to view this content.

But, if you want to return column 1, there's no point doing a VLOOKUP. Just do the MAX:

Please Login or Register  to view this content.

Someone else can have the extra points



Regards, TMS

[FDibbins]

Not really sure what i need to do for the "extra points", but if you want to add all values in a specific cell across sheets, this wull do it for you...
=SUM(SAT:FRI!F83:F83)

[tapsmiled]

I think you are getting the C:D ranges because of the merged cells.

This can be:

Please Login or Register  to view this content.

But, if you want to return column 1, there's no point doing a VLOOKUP. Just do the MAX:

Please Login or Register  to view this content.

Someone else can have the extra points



Regards, TMS

The absolute value will alleviate the issue of having merged cells? I'd hate to have to go back and start making widespread changes.

EDITED: I tried the first half, and all it did was give me the MAX value, not the corresponding value from Column A

[FDibbins]

But, if you want to return column 1, there's no point doing a VLOOKUP. Just do the MAX:

Please Login or Register  to view this content.

Good point Trevor, I guess I got too wrapped up in fixing and simlifying, so then just...
=SUBTOTAL(4,C32:C79)

[FDibbins]

The absolute value will alleviate the issue of having merged cells? I'd hate to have to go back and start making widespread changes.

No it wont. They can stay, just know that any range you construct using the mouse for, say, C, will also incude D, and may need to ne modified

[tapsmiled]

When I added your formula to the workbook, it gave me the number 12, which is the highest value, but what I really want is "1800-1900" which was the corresponding value in Column A.

=VLOOKUP(MAX($C$32:$C$78),($C$32:$C$78),1,FALSE)

[Kaper]

This part:

I want the formula to look through the C32:D78 ranges to find the greatest value and then display the corresponding value from column A, which is a time.

is quite clear to me. You cannot use VLOOKUP to return results from the column, which is located left from the searched one. Instedad you cen use combination of 2 functions INDEX and MATCH in one formula:

Please Login or Register  to view this content.

As for the second part of your question:
Of course you can list "rush hours" for each day in the Summary sheet in a similar way you listed there for instance total calls. So once you copy the above formula into other days sheets, in a designated cell write:

Please Login or Register  to view this content.

and so on.

Alternatively, you could include sheetname in the formula and then it works no matter in which sheet is located. for example:

Please Login or Register  to view this content.

If you plan to find most frequent (in a given week) hours, and if there is a chance that some days (as it is now) have no data at all this formula shall be included inside IF function, like:

Please Login or Register  to view this content.

And last thing - showing the most frequent one (if there is one) can be done with conditional formatting of the cells with these formulas in summary sheet.
For instance the simple way - using a formula for formatting rule - in the attached file I selected in summary sheet range A25:G25
and used the folowing formula for conditional formatting rule

Please Login or Register  to view this content.

This is not exactly the most frequent, but one which occured twice or more. I do not know nature of the business, but it is probably worth noticing if for instance there were 2 most fequent hours.

Kopia DSR (BLANK BETA).xlsx

Hope it helps,

[FDibbins]

Try this...
=INDEX($A:$A,MATCH( SUBTOTAL(4,C32:C79),$C:$C,0))

[TMS]

Just going with the broken formula that you had. You need:

Please Login or Register  to view this content.

Regards, TMS

[tapsmiled]

Wow, that is a lot to absorb. The first formula Kaper posted worked perfectly.

Can you please explain the last formula you posted and how I would apply that to the SUMMARY page. Let's say that I applied the first formula to every sheet in F83 and SUMMARY!H26 is where I wanted to show the busiest hour(s) of the week. I understand that I could potentially have up to 7 possibilities here (if each day had a different time of occurence for the MAX or if 2 days had the same exact MAX).

[FDibbins]

@ TMS, a pure MAX() wont work because there are totals inside that range

@ tapsmiled, Maybe add a small tabl;e to the SUMMRY sheet than pulls in all the times from each sheet - you can then see at a glance what the most rfrequent times are - or if there are any at all

[Haseeb Avarakkan]

Wow, that is a lot to absorb. The first formula Kaper posted worked perfectly...

Hello tapsmiled,

I don't think so this will work, since you have TOTAL cell in col A.

Check this.

Enter 13 in C34 & C35, so total will be 26

Enter 26 in C60, so Total will be 26

That formula will give you output TOTAL. Since 26 is the MAX value actual needs to be A60? 1300-1400

So try this Array Formula with CTRL+SHIFT+ENTER

=INDEX(A32:A78,MATCH(1,IF(A32:A78<>"Total",IF(C32:C78=MAX(IF(A32:A78<>"Total",C32:C78)),1)),0))

[tapsmiled]

I actually like the idea of a chart, though I dont know how I would apply my results to it.

For clarification, this is for a group of 911 call centers. The number of calls can vary from as little as 2 to over a hundred, depending upon how busy each center is. For statistical purposes, we have to achieve standards above 90%, and this is based upon the busiest hour of the day. Each week, the supervisors for each center need to report their numbers, and I want to simplify the process.

[tapsmiled]

Thank you Haseeb. It actually did work. When there are no numbers entered, it reads TOTAL, and when there is data, it is correctly selecting the appropriate value from column A.

[FDibbins]

In its simplest way, something like this....

	A	B
29	Sat	0
30	Sun	0
31	Mon	0
32	Tues	0
33	Wed	0
34	Thur	0
35	Fri	1800-1900

...with this in B29, copied down...
=INDIRECT(A29&"!F83")

You use this same method to pull in other cell refs from those sheets

[tapsmiled]

Wow, am I really this dumb? I hid all of the rows at the bottom of each page. How do I show them again so that i can try to enter the chart? I know....noob question

[FDibbins]

click on the last riow number, right click - unhide

[tapsmiled]

Tried that; it didnt work.

[Haseeb Avarakkan]

Select a whole column, eg: Column K

Home >> Format >> Hide & Unhide >> Unhide Rows

or press Ctrl+Shift+(

[tapsmiled]

Tried that too. I don't get it.

[Haseeb Avarakkan]

What about, Selct a whole column, then

Home >> Format >> AutoFit Row Height.

[tapsmiled]

I tried that as well. If you look at the workbook in the original post, you will see that B28 is the last visible row on SUMMARY. Nothing I have tried has revealed B29+

[FDibbins]

If you click on the row number (28) and "drag down" as if there was another row beneath, then right-click on 28, select unhide

[tapsmiled]

If you click on the row number (28) and "drag down" as if there was another row beneath, then right-click on 28, select unhide

That did it, though it completely locked up my computer for about 10 minutes. Okay...back to that chart now.

[FDibbins]

hmm when I did it (2007 on a not-really-great PC) it was almost instant, but as long as you got there

[tapsmiled]

I did eventually. Regarding that chart now...I did as you instructed, and it returned the earliest times with the MAX value. Is there a way, using your formula, to post ALL of the times =MAX. In other words, if 3 (or more) separate times have the same total, could I list each of those times on the chart?

[tapsmiled]

Here is the latest version of the workbook. As you can see on SAT, there are 3 separate instances where the call volume =10 (the MAX), but at the bottom (F83) and on the SUMMARY, it only lists a single instance rather than each instance. I know the calculation in F83 is the one that needs to be altered; I just dont know how.

Actually, the file is too large now to upload

[FDibbins]

So you are saying you may, for instance on FRI, have more than 1 entry with 12?

Take a look at the attached. I added a helper column (to each sheet) to pull out duplicate qty's. Then I created a table on the summary sheet which goes to 5 - you can extend it by just increasing the columns right-wards.

Let me know if this is what you are after?

[tapsmiled]

That would do it, but it seems like I would have to make massive alterations to what I just finished writing. You are awesome though. I guess I will have to leave it as it is.....can't hand them EVERYTHING on a silver platter. LOL

[tapsmiled]

Thank you to everyone who contributed!!! You guys are great!!!

[FDibbins]

Happy t5o help, look forward to assisting you further Thanks for the feedback

[tapsmiled]

Thank you!