Excel Formula Problem

[Thinh]

Hello,

I have a problem with formula's not recognising some cells fo
calculations, hence giving me the wrong results.

I was just wondering why some cells cannot be recognised by formula's?

The only way i can fix this is to go into these cells and push enter.
was wondering if there is a quick fix because i have over 20000 cell
to go through and i don't want to go through them one by one.

Thank you everyone in advance :

 

[Norman Harker]

Hi Thinh!

Post an example of one of these formulas and detail of what cell it
won't recognise and the contents of that cell.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dave Marden]

I take it what you are talking about is Custom Functions you created in
VBA. If so you need to add Application.Volatile at the end of your
custom function. This will make it so you formula automatically is
calculated anytime anything changes in your spreadsheet.

I hope this helps,

Dave Marden

 

[Dave Marden]

Another method to autocalculate these is to simply hit F9, this causes
excel to autocalculate the spreadsheet.

You could also goto tools -> options and under the calculation tab make
sure it is set to automatic. There you will also see a button for F9 which
is another place to click instead of hitting F9 to force the spreadsheet to
calculate.

Dave Marden

 

[Gord Dibben]

Sounds like some of your data is text and not numeric.

Select all and format to General.

Copy an empty cell. Select your data and Paste Special>Add>OK>Esc.

This will force it to numeric in most cases.

Gord Dibben Excel MVP

 

[Thinh]

Thank you all so much for your help!!

Gord had the answer

 

[Gord Dibben]

We all appreciate the feedback. Sometimes we just shot-gun a bunch of
suggestions. Nice to know what does and doesn't fit. Helps us help others.

Thanks, Gord

 

[Dave Marden]

No Problem, I thought you may be using a custom function.

Thanks For the feedback,
Dave Marden