

wulfiesmith Inner circle Beverley, UK 1149 Posts 
Problem...
imagine a spreadsheet. I have CELL B1 which contains the function =RIGHT(A1,3) if you now enter  for example  80042B into A1, the result in CELL B1 will be 42B. excellent! just what we need ... if we now enter the function into CELL C1 =LEFT(B1,2) we now get a result of 42 in CELL C1. here is where the problem starts ... now enter the function into CELL D1 =IF(C1<20,"less than 20","more than 20"). This math function should display less than 20 if the content of C1 is less, and more than 20 if it is more. The default is "more than 20". By that I mean, when you change the numbers in A1  B1 works  C1 works  but D1 does not change. HOWEVER, if you overstrike the figures in CELL C1 it works!!! By overstriking you delete the function within the cell, and the function in D1 now works??!! ANY HELP ON THIS ONE??? 
balducci Loyal user Canada 230 Posts 
Good question.
Yet another reason I never use Excel for anything really important. It seems to me the problem may be one with conversion and / or automatic checking / updating. I played with it a bit, but could not solve your problem. Sorry.
Make America Great Again!  Trump in 2020 ... "We're a capitalistic society. I go into business, I don't make it, I go bankrupt. They're not going to bail me out. I've been on welfare and food stamps. Did anyone help me? No."  Craig T. Nelson, actor.

Magnus Eisengrim Inner circle Sulla placed heads on 1064 Posts 
The problem is that C1 contains a text string, not a number. For the example given, if you change it to
D1=IF(C1<"20","less than 20","more than 20") you will get the correct answer. There is probably a command to convert the string to a number, but I don't know it. John
The blooddimmed tide is loosed, and everywhere
The ceremony of innocence is drowned; The best lack all conviction, while the worst Are full of passionate intensity.Yeats 
Magnus Eisengrim Inner circle Sulla placed heads on 1064 Posts 
The other alternative is to turn C1 into a number with
=VALUE(LEFT(B1,2)) John
The blooddimmed tide is loosed, and everywhere
The ceremony of innocence is drowned; The best lack all conviction, while the worst Are full of passionate intensity.Yeats 
wulfiesmith Inner circle Beverley, UK 1149 Posts 
Thanks for the help John,
unfortunately, it will not work. The answer is of course, that the number in Cell C1 comes from a Function (which can be seen in the menu bar) and not a direct entry. I am still working on it! 
MagicSanta Inner circle Northern Nevada 5845 Posts 
PM Cfrye and see if you can get hold of him. He is one of the top authors of books on excel and other office software and he's a member here.

wulfiesmith Inner circle Beverley, UK 1149 Posts 
Thanks MS ...
in fact Magnus had come up with the answer ... I had to shorten the argument a little, that's all ... I entered a new column before COL D ... (Col D now became Col E) In the new CELL D1 I entered =VALUE(C1) ... now I just altered the function in CELL E1 to read =IF(D1<20,"less than 20","more than 20") Thanks to everyone who contributed here! If you have any Excel probs give me a shout. regards, WulfieSmith 
MagicSanta Inner circle Northern Nevada 5845 Posts 
I thought John had the right idea but it confused me when you said it wasn't the fix. I use to do the same thing you did and it puzzled me.

wulfiesmith Inner circle Beverley, UK 1149 Posts 
Mmm ... as one door opens another closes it seems!
the above problem has been solved. However, I am left with a colmn of information I need to sort. But, because this column of information has resulted from an IF Statement, if will not data sort..... 
Magic.J.Manuel Special user I have danced upon 667 Posts 
John is correct about the fact that the result is a string, but quoting the 20 will result in a string compair not a numeric which may not work, but the value(x) function does not need a seperate cell.
just put it in the function that needs it: =IF(VALUE(C1)<20,"less","more") jmk
Nothing would get done at all, if man waited so long that no one could find fault with it.

Magnus Eisengrim Inner circle Sulla placed heads on 1064 Posts 
I hope you recognized that my two approaches are supposed to be distinct. Don't do them both at the same time.
John
The blooddimmed tide is loosed, and everywhere
The ceremony of innocence is drowned; The best lack all conviction, while the worst Are full of passionate intensity.Yeats 
wulfiesmith Inner circle Beverley, UK 1149 Posts 
I did John ...
and thanks ... now EVERYTHING has been completed. Including the sort problem. A milestone for the office clerical ... hope they appreciate it! regards, Wulfie PS ... who's going to win tonight?? 
Magnus Eisengrim Inner circle Sulla placed heads on 1064 Posts 
England, but the outcome will be uncomfortably uncertain right to the end.
The blooddimmed tide is loosed, and everywhere
The ceremony of innocence is drowned; The best lack all conviction, while the worst Are full of passionate intensity.Yeats 
nedved31 0 Posts 
Quote:
On 20100611 14:46, wulfiesmith wrote: To my mind I have necessary knowlede for solving such situation. One of the best method would become the next tool. It helped me and some my friends for some minutes and demonstrated many other interesting facilities, general of them is ability working out other kinds of troubles with ms excel  excel 2002 font problem. 
The Magic Cafe Forum Index » » Not very magical, still... » » Microsoft Excel problem (0 Likes) 
[ Top of Page ] 
All content & postings Copyright © 20012021 Steve Brooks. All Rights Reserved. This page was created in 0.67 seconds requiring 5 database queries. 
The views and comments expressed on The Magic Café are not necessarily those of The Magic Café, Steve Brooks, or Steve Brooks Magic. > Privacy Statement < 