Home

Excel wont recognize values when trying to do the average function

%3CLINGO-SUB%20id%3D%22lingo-sub-530911%22%20slang%3D%22en-US%22%3EExcel%20wont%20recognize%20values%20when%20trying%20to%20do%20the%20average%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530911%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20I'm%20trying%20to%20take%20the%20average%20of%20C337-C354%20but%20excel%20wont%20recognize%20the%20big%20numbers%20(obviously)%20since%20the%20average%20is%20313%20as%20seen%20in%20C357.%20How%20can%20I%20fix%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-530911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-530925%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20wont%20recognize%20values%20when%20trying%20to%20do%20the%20average%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530925%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F290720%22%20target%3D%22_blank%22%3E%40Dannni_Yea1240%3C%2FA%3E%26nbsp%3B%2C%20Excel%20definitely%20recognizes%20relatively%20big%20numbers%2C%20if%20only%20these%20are%20numbers%2C%20not%20texts.%20When%20AVERAGE%20ignores%20them.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20you%20sample%20you%20have%3C%2FP%3E%0A%3CP%3E9%20019.57%3C%2FP%3E%0A%3CP%3Elooks%20like%20space%20is%20thousand%20separator%20and%20dot%20separates%20decimal%20part.%3C%2FP%3E%0A%3CP%3EAt%20the%20same%20time%20you%20have%3C%2FP%3E%0A%3CP%3E22%2C55%3C%2FP%3E%0A%3CP%3E547%2C34%3C%2FP%3E%0A%3CP%3Eand%20here%20looks%20like%20comma%20separates%20decimal%20part.%3C%2FP%3E%0A%3CP%3EIt%20shall%20be%20no%20such%20mix.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20test%20in%20next%20column%20do%20you%20have%20numbers%20or%20texts%20by%20%3DISNUMBER()%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-530929%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20wont%20recognize%20values%20when%20trying%20to%20do%20the%20average%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530929%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20correct!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20changed%20to%20decimal%20seprator%20as%20.%20and%20thousands%20as%20%2C%20but%20it%20still%20wont%20remove%20the%20spaces.%20Now%20it%20looks%20like%20this%2C%20any%20idea%20how%20to%20fix%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-530939%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20wont%20recognize%20values%20when%20trying%20to%20do%20the%20average%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530939%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F290720%22%20target%3D%22_blank%22%3E%40Dannni_Yea1240%3C%2FA%3E%26nbsp%3B%2C%20perhaps%20space%20is%20part%20of%20numbers%20format%20which%20separates%20thousands%2C%20when%20it's%20not%20necessary%20to%20remove%20them.%20If%20you%20test%20%3DISNUMBER(C337)%20it%20returns%20TRUE%20of%20FALSE%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-530948%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20wont%20recognize%20values%20when%20trying%20to%20do%20the%20average%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThat%20returns%20as%20False%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-530965%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20wont%20recognize%20values%20when%20trying%20to%20do%20the%20average%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530965%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F290720%22%20target%3D%22_blank%22%3E%40Dannni_Yea1240%3C%2FA%3E%26nbsp%3B%2C%20when%20you%20may%20try%20to%20select%20your%20range%20with%20%22numbers%22%20in%20question%2C%20Ctrl%2BH%20(it%20works%20for%20Excel%20Online)%20and%20replace%20space%20on%20nothing.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20439px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112113iE4E355C56D8F9E5A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20that's%20not%20space%20but%20another%20not-printable%20character%20you%20may%20try%20to%20copy%20it%20use%20here%20instead%20of%20space.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-530967%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20wont%20recognize%20values%20when%20trying%20to%20do%20the%20average%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530967%22%20slang%3D%22en-US%22%3EYou%20are%20a%20savior%2C%20I'm%20very%20thankful.%20The%20last%20tip%20worked%20excellent%20to%20copy%20the%20not-printable%20character.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-530974%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20wont%20recognize%20values%20when%20trying%20to%20do%20the%20average%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F290720%22%20target%3D%22_blank%22%3E%40Dannni_Yea1240%3C%2FA%3E%26nbsp%3B%2C%20good.%20Hope%20AVERAGE%20works%20now%20as%20expected.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Dannni_Yea1240
Occasional Contributor

Hi! I'm trying to take the average of C337-C354 but excel wont recognize the big numbers (obviously) since the average is 313 as seen in C357. How can I fix this?

 

 

7 Replies

@Dannni_Yea1240 , Excel definitely recognizes relatively big numbers, if only these are numbers, not texts. When AVERAGE ignores them.

 

In you sample you have

9 019.57

looks like space is thousand separator and dot separates decimal part.

At the same time you have

22,55

547,34

and here looks like comma separates decimal part.

It shall be no such mix.

 

You may test in next column do you have numbers or texts by =ISNUMBER() 

@Sergei Baklan 

 

You are correct!

 

I have changed to decimal seprator as . and thousands as , but it still wont remove the spaces. Now it looks like this, any idea how to fix this? 

 

 

@Dannni_Yea1240 , perhaps space is part of numbers format which separates thousands, when it's not necessary to remove them. If you test =ISNUMBER(C337) it returns TRUE of FALSE?

@Dannni_Yea1240 , when you may try to select your range with "numbers" in question, Ctrl+H (it works for Excel Online) and replace space on nothing.

image.png

If that's not space but another not-printable character you may try to copy it use here instead of space.

You are a savior, I'm very thankful. The last tip worked excellent to copy the not-printable character.

@Dannni_Yea1240 , good. Hope AVERAGE works now as expected.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies