How is it that I can get Excel Status Bar cell count but not average?
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
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
Last Comment
Juan Ocasio
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.
You need to hide the Ink annotations
capture.png
capture.png
Oppps, wrong question!
You need to do what jocasio123 told you ... maybe a macro will help!
You need to do what jocasio123 told you ... maybe a macro will help!
Sounds to me like the data are actually entered as text and not as a true date.
A macro could work for this.
A macro could work for this.
A very simple macro like this does the trick:
Sub FixTheTime()
With [f:f]
.Value = .Value
.NumberFormat = "[h]:mm"
End With
End Sub
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
If you then format the cells with Custom format [hh]:mm it will allow the hours to go over 24.
Cheers
Rob H
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
If you then format the cells with Custom format [hh]:mm it will allow the hours to go over 24.
Cheers
Rob H
ASKER
Rorya, you are a wizard!
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
