DATA SPREADSHEET WITH Sumif array formula

[bill351]

Hi Guys n Girls
I have a spreadsheet that I am using to analyse Leave patterns and Overtime which is working pretty good with the formula =SUM(IF(ISNUMBER(SEARCH("OT",F84:BG84)),VALUE(MID(F84:BG84,FIND(" ",F84:BG84)+1,2)))) and I currently am entering in the cells ot 12 (12hrs Overtime) however if enter 12.5 or any 3 characters after the control OT it does not read that number now that can be 8.5 or 105. Am i missing something or is Excel restricted to a certain number of numbers in regards to the Array Formula above?

Cheers Bill from Oz

 

[StephenCrump]

Your problem is in MID( .... , 2)

What do the values actually look like in F84:BG84, i.e. so that we can work out how to extract the numerical bit you're interested in?

 

[bill351]

Your problem is in MID( .... , 2)

What do the values actually look like in F84:BG84, i.e. so that we can work out how to extract the numerical bit you're interested in?

The data in F84:BG84 can be a combination of any 2 digit number IE "OT 24". Is what you are indicating to that the ,2 should be a 3? because the highest number could be 100 or 10.5 the common factor is 3 characters after the "OT"

Hope this explains what I am trying to fix

cheers from Oz

 

[StephenCrump]

Sorry, still no clearer. "10.5" has 4 characters, and "10.25" would have 5 characters.

Can you please provide some examples of the text values you would typically have in F84:BG84, so that we can see the pattern and provide a formula to extract the hours component.

Otherwise we'll just be guessing.

 

[Jonmo1]

Instead of trying to 'find' the number...

Why not just 'remove' the known text "OT " <- This seems like a more consistent pattern to use.

Instead of
MID(F84:BG84,FIND(" ",F84:BG84)+1,2)
try
REPLACE(F84:BG84,1,3,"")