The Magic Café
Username:
Password:
[ Lost Password ]
  [ Forgot Username ]
The Magic Cafe Forum Index » » Not very magical, still... » » Microsoft Excel problem (0 Likes) Printer Friendly Version

wulfiesmith
View Profile
Inner circle
Beverley, UK
1149 Posts

Profile of wulfiesmith
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
View Profile
Loyal user
Canada
230 Posts

Profile of balducci
Good question.

Yet another reason I never use Excel for anything really important. Smile

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. Smile
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
View Profile
Inner circle
Sulla placed heads on
1064 Posts

Profile of Magnus Eisengrim
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
View Profile
Inner circle
Sulla placed heads on
1064 Posts

Profile of Magnus Eisengrim
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
View Profile
Inner circle
Beverley, UK
1149 Posts

Profile of wulfiesmith
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
View Profile
Inner circle
Northern Nevada
5845 Posts

Profile of MagicSanta
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
View Profile
Inner circle
Beverley, UK
1149 Posts

Profile of wulfiesmith
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
View Profile
Inner circle
Northern Nevada
5845 Posts

Profile of MagicSanta
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
View Profile
Inner circle
Beverley, UK
1149 Posts

Profile of wulfiesmith
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
View Profile
Special user
I have danced upon
667 Posts

Profile of Magic.J.Manuel
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
View Profile
Inner circle
Sulla placed heads on
1064 Posts

Profile of Magnus Eisengrim
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
View Profile
Inner circle
Beverley, UK
1149 Posts

Profile of wulfiesmith
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
View Profile
Inner circle
Sulla placed heads on
1064 Posts

Profile of Magnus Eisengrim
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
View Profile

0 Posts

Profile of nedved31
Quote:
On 2010-06-11 14:46, wulfiesmith wrote:
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???


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 © 2001-2021 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 <

ROTFL Billions and billions served! ROTFL