Forum Discussion
Excel formula not working
Hi all,
I have a problem with a file with 2 tabs.
In tab 1 we gather information and in tab 2 we process that information.
the information from tab 1 we copy and paste into tab 2 where there is a formula =average which should count the average time which it is not doing right now.
As you can see the Average Time Used cell =average(A2:A300) should count the cells in row A but it does not do that, all the other formulas do work.
It does not work for me but for some colleagues of mine it does, my settings don't appear to be different then theirs.
Now the weird thing is, if i type the time manually it starts counting it in the formula.
I now manually retyped the 00:30 and now it does count it.
This would be no problem, but as seen at ''Total MRR performed'' there is 250 cells which should then be retyped, often more going further then even 300 in total.
I already tried to change ''format cells'' CTRL+1 but no effect.
thanks for your time and help.
Here are some steps you can try to troubleshoot the issue:
1. Check Cell Formatting:
- Check the format of cells in A2:A300: Ensure that these cells are formatted as Time and not as Text. Text format treats values like text and ignores formulas applied to them.
- Right-click on any cell in the range A2:A300 and select Format Cells.
- In the Format Cells window, go to the Number tab.
- Under Category, select Time.
- Choose a desired time format and click OK.
2. Clear Formatting:
- Select cells A2:A300.
- Right-click and choose Clear > Formats.
- This will remove any formatting applied to the cells, including potential text formatting.
3. Paste Special - Values Only:
- Copy the data from tab 1.
- In tab 2, right-click on cell A2 and choose Paste Special.
- Select Values and click OK.
- This will paste only the numerical values from the copied data, ensuring they are recognized as numbers by the formula.
4. Check for Leading/Trailing Spaces:
- Sometimes, leading or trailing spaces in copied data can interfere with formulas.
- Select cells A2:A300 and check if there are any spaces before or after the time values.
- You can use the TRIM function to remove any leading/trailing spaces: =AVERAGE(TRIM(A2:A300))
5. Check for Circular References:
- If other formulas in your sheet reference the AVERAGE cell, it might create a circular reference, causing the formula not to work correctly.
- Use the Formula Auditing tools on the Formulas tab to check for circular references.
Additional Tips:
- Try copying and pasting the formula itself instead of just the cell reference.
- Double-check the formula syntax for any typos or errors.
- Consider using the SUM and COUNT functions instead of AVERAGE if you need to calculate the total time spent and then divide by the number of entries.
By following these steps, you should be able to identify the cause of the issue and get your AVERAGE formula working correctly
- The times in column A are not recognized as time by Excel. Try this:
- Select column A
- From the Data tab of the ribbon, press the Text To Columns button
- Click Finish
