Excel pissing me off...

[The Boosh!]

There is normally an excel guru at work but he's on holiday, and I can't find my answer on google.

My formulas aren't calculating.

EG if i typ a1+2 in a cell, it wont give me the value of a1+2, it just displays exactly what I have typed (=a1+2).

Have checked the settings and it set to automatically calculate and the formulas are also not set to show.

It has something to do with the format of the sheet I am using (excel 4 i think), but I can't work out what (cell formats etc are correct).

Has anyone got any idea?

Thanks

 

[DanFreemanPhoto]

You need =SUM(a1+2)

Any formula must start with '=' as far as I know.

 

[Will.]

Are you trying to get cells a1 and a2 to add up or just add 2 onto a1?

 

[The Boosh!]

No i'm doing a complex(ish, for me anyway) nested if/vlookup/text string values formula.

The actual formula is below:

=IF(ISERROR(VALUE(LEFT(I3,1))),IF(AND(ISTEXT(LEFT(I3,1)),ISNUMBER(VALUE(MID(I3,2,3))),ISTEXT(RIGHT(I3,3))),"OLD REGISTRATION",IF(AND(ISTEXT(LEFT(I3,2)),ISNUMBER(VALUE(MID(I3,3,2))),ISTEXT(RIGHT(I3,3))),"NEW REGISTRATION","ANOMALY OR PRIVATE REGISTRATION")),"ANOMALY OR PRIVATE REGISTRATION")

The formula does exactly what I want it to do when I knock up some test data in a new file - No issues with my formula. The issue is the format somehow of my data ( there are 66,000 rows (IE Max that excel can handle)).

I used the example in my OP to simplify things.

Dan, you don't have to type =sum if you are just adding two numbers together

 

[Mr Pants]

=SUM(A1+A2)

(Oops just saw your new post NVM Haha).

 

[Will.]

Oh well. Adding two cells is the extent of my excel knowledge. I find it the worst bit of my course.

 

[The Boosh!]

It's great when it does what you tell it to do.

10% of the time though it doesn't listen to you

 

[The Psychedelic Socialist]

Your cells are formatted as 'Text'. If you change the format back to 'General' (or something else), it'll start taking notice of your '=' sign again

 

[The Boosh!]

Your cells are formatted as 'Text'. If you change the format back to 'General' (or something else), it'll start taking notice of your '=' sign again

You were correct (kind of).

Some (not all) of the values in my workbook were formatted as text, but when I selected it and changed it to number it ignored me. Ended up doing it through error checking and it works now!

Thanks.

 

[Sarah]

glad you got it sorted, do u have to ask questions all the time, if regular forum guru is on holiday?

 

[The Boosh!]

My knowledge of excel before starting this job was really basic.

In the past month i've learn't a s**t load of stuff. Before I knew said expert I asked on CS, but now if I get stuck on anything I just give him a bell and he comes and gives me a bit of help.

As mentioned though he's not in and no one else knew why it was doing it

PS I am the forum guru

 

[Lagerlout]

I fecking hate excel. Never really had to use it much until now. It can be a little b**ch for no reason sometimes. Seems to do s**t of it's own accord tbh.

 

[iain187]

if you go tools > options > somewhere in there there is a tick box like 'show formulas in cells instead of calculated results' i thought you was gonna have that one ticked... depends on version of office as to where youll find it exactly :s