How is it that I can get Excel Status Bar cell count but not average?

garyrobbins
garyrobbins used Ask the Experts™
on
Hello Experts.  I need your help regarding working with the Status Bar – Average function.

I have a spreadsheet created from a system export file.  The column titled time is formatted [h]:mm.  I can use the Excel Status Bar and view the cell count in the column but not the average or sum.  See attached sample file.  I’ve tried reformatting the original cells but doesn’t solve the problem.  What does change things is when I Edit each sell and simply exit the cell – the the average function works.

Any thoughts about how to work with data sheet which comes in each day?

Thanks,
Gary

Mikes-Report---Weekly-Hours---EE.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Juan OcasioApplication Developer

Commented:
Yeah, that happens to me too.  what you have to do is go into the cell and F2 and enter.  I'm not sure of any other way to do this.
Jorge PaulinoIT Pro/Developer
Top Expert 2008

Commented:
You need to hide the Ink annotations
capture.png
Jorge PaulinoIT Pro/Developer
Top Expert 2008

Commented:
Oppps, wrong question!

You need to do what jocasio123 told you ... maybe a macro will help!
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Top Expert 2010

Commented:
Sounds to me like the data are actually entered as text and not as a true date.

A macro could work for this.
Top Expert 2010

Commented:
A very simple macro like this does the trick:

Sub FixTheTime()

    With [f:f]
        .Value = .Value
        .NumberFormat = "[h]:mm"
    End With

End Sub

Open in new window

Most Valuable Expert 2011
Top Expert 2011
Commented:
You can also just copy a blank cell, then paste special - Values, Add on top of your time cells.
Rob HensonFinance Analyst

Commented:
I come across values as text quite often with SAP downloads so have written this little script to overcome it. Select the cells requiring conversion before running the macro. Switches off calculation so that it runs quicker.

Sub TextToValue()

If Application.Calculation = xlCalculationAutomatic Then
Let CalcFlag = True

With Application
    .Calculation = xlCalculationManual
End With

End If
    
    With Selection
        .NumberFormat = "General"
    End With
    
    For Each cell In Selection
        TV = cell.Value
        If cell.Value <> "" Then cell.Value = Trim(TV)
    Next cell
    
If CalcFlag = True Then

With Application
    .Calculation = xlCalculationAutomatic
End With

End If
        
End Sub

Open in new window


If you then format the cells with Custom format [hh]:mm it will allow the hours to go over 24.

Cheers
Rob H

Author

Commented:
Rorya, you are a wizard!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial