Help - Formula to count if the highest number is in the column

[DirtySkillz]

Hi guys,

Im trying to link up the main spreadsheet (Summary 2015 financial year), to calculate the highest response time for ACT in Week 41 spreadsheet to be placed in column C34 in the Summary 2015 Financial Year Sheet. Response times are in hours and minutes so i need the formula to pick this up.

Can anyone help me to create a formula. The spreadsheets is attached for your viewing

Thank you

[zbor]

Try this, confirmed with ctrl+shift+enter (not just enter):

Please Login or Register  to view this content.

If you would keep data consistent for other weeks as in Week 41 then you could extend formula to the right and you would get result.

Just in 5th row write number of a sheet and in sheet keep ACT in A column and result in B column.

To get proper result all numbers must be in minutes:

i.e.
3 minutes
2 hours

will return maximum 3.

[DirtySkillz]

Hi this failed.

The code returns a Value error and for some reason the sheet 4 is not referred to - Ami I missing something?

Also are the minutes meant to be in whole numbers such as 120 compared to 1 hour 20 minutes?

[zbor]

See is it work for you in the attachment.
It will work up to 99 minutes.

For more I must revise example.

But it would be much easier if you enter time as a time, without text as min, Under a minute etc.

[DirtySkillz]

Hi Zbor, Thank you for your help. You got the number 2!

This one works but a simple drag and copy cell to multiple cells won't work. Is there a way or easier formula for me to use to get the sheet:

Summary 2015 Financial year to calculate all empty cells. I have never used the max if function and it's quiet a complicated formula.

Any ideas?

[zbor]

If you would keep data consistent for other weeks as in Week 41 then you could extend formula to the right and you would get result.

[DirtySkillz]

How would I calculate all other cells though?For example in the summary sheet - I want to now calculate Jobs not set up/extended for payroll in sheet 41 to reflect in the summary using maxif- what cells do I amend to fix the full summary sheet to count?

[DirtySkillz]

Maybe explain this code to me - so I can see what you are referring to in (sheet 41)...

=MAX(IF(INDIRECT("'"&D$5&"'!A7:A100")=$A34,IFERROR(--SUBSTITUTE(LEFT(INDIRECT("'"&C$5&"'!b7:b100"),2)," ",""),0)))

[zbor]

Gladly.

First without referencing to sheet 41 so you can see how formula works:

Please Login or Register  to view this content.

If in A column you have value from A34 (Wich is ACT) then from B column take response time.

Since you don't have actually time there but text (as I wrote above it would be much easier if you enter time in B column) then you need to extract number that represent minutes.
There can be 1 min or 16 mins etc.

So we take 2 characters out by

Please Login or Register  to view this content.

- in first case 1 and space
- in second case 16

So we need to SUBSTITUTE space with nothing to get 1 and 16 (similliar for other numbers and that's the reason why is limitation to 99 because 3rd character for minutes above 99 will extract 1 m from 1 min and that is not number).

After you SUBSTITUTE space with nothing text (1 and 16 are still text at this point) need to be converted into number by -- (or some other method).

Since some cells will return error
(Like "Under a minute", 2 Left characters are "Un" converted into number return error)

then you need IFERROR formula to avoid them (Under a minute will be 0 after IFERROR formula).

From numbers that are left you will get MAX value.

Now, if you have times are times then all of this wouldn't be needed and you could just find MAX of them.

[zbor]

Instead of sheet we use INDIRECT formula to return range of sheet determine by data in row 5:

INDIRECT("'"&D$5&"'!A7:A100")

When you move this formula to the right it will look sheet name in E5 but A column will remain as a criteria.
Since in Week 42 you have move one column to the right then formula won't work.

When you put all Consultant/Team into same column (doesn't matter wich one) then fix above formula to that column but keep that to all other sheets.