Excel Questions

[armaghniac]

BAsic one here - how do you input numbers to a cell (e.g. phone nbrs) without losing the 0 at the beginning?

Format the column or whatever as Text before you type them in.
or for individual entries a quote will signal that you want text e.g. '02830881122

[take_yer_points]

That's what I'm getting too. I can't understand what's happening in the first case. Are you using 2007 version too?

I said in my earlier post I was using 2007 - it's actually 2010

[Hardy]

Sorry anyway. So it's either a bug that was never seen and so carried into the 2010 version, or it's not a bug, in which case I can't see what it's meant to achieve.

[screenexile]

I have to admit I do like finding out new things in Excel and there's not much better than a perfectly formulated spreadsheet but to normal people is this possibly the most boring thread on the board??

[haveaharp]

Anyone been on an excel course recently ? Im being asked to go on a basic one but i did basic and advanced about 10 years ago. Has it changed much in the interim ? Just dont want to waste my time if its going to be the same.

[westbound]

In Excel 2007, something weird happens when I try to do a Find and Replace in a range of COUNTIF formulae. Can anyone try the setup below and see if it happens and let me know the Excel version you're using? Also, any insight would be appreciated.



Column C is counting the number of instances of each category in the range A1 to A5. Now I want to count the number of instances of each category in the range A6 to A10. So I do a Find and Replace on the formula range C1 to C3. I put $1 in the Find box and $6 in the Replace box and hit Replace All.

Tell me what happens in your case.

Replaced $1 with $6 and it updated to the following - also using Excel 2007.

=COUNTIF(A$5:A$6,...

Tried:

Find: A$1:A$5
Replace with: A$6:A$10

That worked ok - not sure why the initial find and replace does that

I don't see what the problem is?

When you replace $1 with $6, the formula would read =countif(A$6:A$5,.......). But as 5 < 6 excel automatically puts the formula in chronological order, i.e. A$5:A$6.

If you then replace $5 with $10 you get the formula =countif(A$6:A$10........) as desired.

Maybe I'm misunderstanding something but I don't see what the problem is?

[Hardy]

Westbound, I didn't realise that's what it was doing, because I didn't go beyond the first step and wondering, "what the hell's going on?". To get the job I was doing done, I just edited the whole string.

Anyway, you're right - that's what it's doing. Thanks for pointing it out. What's wrong with it, in my view, is that it's completely counter-intuitive. It was for me, anyway. It would never have occurred to me, after the first step, when I now had "A$5:A$10" and was scratching my head, that replacing 5 with 10 would give me "A$6:A$10". I don't know any other formulae that respond this way to Find and Replace (maybe there are), but it's completely at odds with how I expect the Find/Replace function to work - i.e. as a text editor.

[laceer]

It has worked as a text editor though. You've changed the first cell in your range from A1 to A6 but you haven't changed the second cell, which is what westbound has said. You're looking for find/replace to move the second cell in the range relative to the first cell, which I don't think it can do

[Hardy]

Sorry, I think I'm missing the point. I always thought Find and Replace simply treated the content of any sell as text, even if it was a formula.

Say I have 1 in A1, 2 in A2 and 3 in A3. In B1 I have the formula =A1+A2 and the result is 3. Now if I select column B and go "Find A1, Replace with A3", my formula in B1 changes to =A3+A2 and the result is 5. It has simply edited the text in the formula.

In my problem formula, I was using Find and Replace to edit the text in the whole range of formulae to change A1 to A6. I was then going to do a subsequent Find and Replace to change A6 to A10. The last thing I expected a text editor to do was Change A1 to A5 when I told it to change it to A6. Even less did I expect it, if I had then told it to change A5 to A10 (which I didn't, since I now had A5:A5 as the formula text and it wouldn't make sense to change it to A10:A10) to find a non-existent A6 and change it to A10.


Am I making sense?

[laceer]

Not really but it might be me that's confused!

Original range:  A1:A5
Find A1 / Replace A6
Range is now A5:A6
Find A5 / Replace A10
Range is now A6:A10

[armaghniac]

Sorry, I think I'm missing the point. I always thought Find and Replace simply treated the content of any sell as text, even if it was a formula.

The Find and Replace does treat the content as text, but the formatting of that text then determines how it is displayed, in this case the representation of formulas.

A bit like Duffleking's problem with the phone numbers. If you had of numbes 3531222333 etc and you changed these to +35312223333 then the replace would change them but you'd still end up with the original number.

[westbound]

In my problem formula, I was using Find and Replace to edit the text in the whole range of formulae to change A1 to A6. I was then going to do a subsequent Find and Replace to change A6 to A10. The last thing I expected a text editor to do was Change A1 to A5 when I told it to change it to A6. Even less did I expect it, if I had then told it to change A5 to A10 (which I didn't, since I now had A5:A5 as the formula text and it wouldn't make sense to change it to A10:A10) to find a non-existent A6 and change it to A10.


Am I making sense?

Not really making sense to me!

Couple of things;
1 - the subsequent Find and Replace should be to change A5 to A10. (not A6 to A10).
2 - it didn't Change A1 to A5. A5 is still in the formula (it just appears before A6 in the formula). A1 was replaced by A6, and A5 remained in the formula. You now have both A6 and A5 in the formula, but they are in the other order.
3 - You shouldn't have A5:A5 as the formula text at this stage. It should be A5 to A6.


The countif formula in excel is designed that the range must be in chronological order. To prove this point, type the following into an empty cell
=countif(A7:A4,B1)

When you hit enter and look back at what you have typed, excel has automatically corrected the formula to read as follows:
=countif(A4:A7,B1)

Hope this makes sense?

[Hardy]

It does make sense (same for laceer, armaghniac). Clearly, this is how Excel works. And it would have worked fine if I'd gone ahead and done my two-stage Find/Replace without noticing at the first stage that it had seemed to do something other than what I'd asked.

I presume there are lots of other Excel formulae that work this way - I just never noticed it before.

[Orior]

Possibly something covered before, but if you enter a 16 digit number into excel, then it will round it to the first 15 digits. B'astardo.

Even if you clever store it as text by preceding it with a single apostophe, then convert it back to a number using the value function it rounds calculations. B'astardo.

Does Openoffice work any better?

[WeeDonns]

Open office works the same way