|
|
|
|||
wulfiesmith Inner circle Beverley, UK 1339 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 227 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 1053 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 blood-dimmed 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 1053 Posts
|
The other alternative is to turn C1 into a number with
=VALUE(LEFT(B1,2)) John
The blood-dimmed 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 1339 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 5841 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 1339 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 5841 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 1339 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 663 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 1053 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 blood-dimmed 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 1339 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 1053 Posts
|
England, but the outcome will be uncomfortably uncertain right to the end.
The blood-dimmed 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 2010-06-11 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) | |
Posted: Jun 11, 2010 06:46 pm 
| [ Top of Page ] |
|
All content & postings Copyright © 2001-2024 Steve Brooks. All Rights Reserved. This page was created in 0.02 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 < 
|