If problem????

  • Thread starter Thread starter Högberg
  • Start date Start date
H

Högberg

Hi,

I got a problem that I cant solve myself, I hope you guys could help me.

I want to know if E6>E8 Then it would return 1
E6=E8 Then it would return X
E6<E8 Then it would return 2

Best Regards

// Peter //
 
Try the following:

=IF(E6>E8,1,IF(E6=E8,"X",2))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
In E6 I got 1, In E8 i got 2.

The result should be 2, I get a error message When I type the formulas that
Ron & Chip suggested..

I will give you the big picture,

Arsenal - Aston Villa 1-2, That should be a 2

Arsenal - Aston Villa 2-1, That should be a 1

Arsenal - Aston Villa 2-2, That should be a X

I hope that you understand what I`m trying to do.

Best Regards

// Peter //
 
Högberg

What error message exactly do you get #NAME? #VALUE or what? With the
values that you give, 1 in E6 and 2 in E8 both the formulas that you were
given should work.

If your data is in the form you give in the narrative ie "Arsenal - Aston
Villa 1-2" then

=IF(MID(E6,LEN(E6)-4,1)=RIGHT(E6,1),"X",IF(MID(E6,LEN(E6)-4,1)>RIGHT(E6,1),1
,2))

will return return the right result but then I do not know why you would be
talking about E6 and E8. Have you got only 1 in E6 and only 2 in E8?

If the data is in the written for in your narrative then I would not
recommend using my formula because it would be too easy to get something
wrong. If you miss off or add a space you will get a wrong result. If
Villa beat Arsenal 10 - 0 then you will have a problem (and so will
Arsenal!)

Regards

Sandy


Högberg said:
In E6 I got 1, In E8 i got 2.
Click to expand...
The result should be 2, I get a error message When I type the formulas that
Ron & Chip suggested..

I will give you the big picture,

Arsenal - Aston Villa 1-2, That should be a 2

Arsenal - Aston Villa 2-1, That should be a 1

Arsenal - Aston Villa 2-2, That should be a X

I hope that you understand what I`m trying to do.

Best Regards

// Peter //





Dave Peterson said:
What do you have in E6?
what do you have in E8?

What did you expect?
What did you get?
Click to expand...
help
Click to expand...
 
It has just dawned on me. Are you importing the data? If so then there is
probably other characters attached, for example char(160) which is, I
believe, a non-breaking space. That will cause the problem that you
describe. Try removing them, here is a previous post by Ken Wright:

Ken's Post:
CHAR(160) is a non breaking space character from Html. Dave McRitchie is a
Microsoft MVP who has
his own website full of free goodies (Examples, spreadsheets and bits of
code). TrimAll is a
macro that Dave wrote to deal specifically with this kind of problem (ie
garbage characters in
your data). You need to go to his site with the link I gave you, copy the
code, paste it into a
module in your personal.xls file, and then select the affected data and do
Tools / Macro / Macros
/ Trimall.

This is only necessary if all the data has this character in it, but even
then you could just use
the second formula I gave you.

As you have said all your data came in from a csv file then it is likely to
be text.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

End of Ken's Post

HTH

Sandy


Högberg said:
In E6 I got 1, In E8 i got 2.

The result should be 2, I get a error message When I type the formulas that
Ron & Chip suggested..

I will give you the big picture,

Arsenal - Aston Villa 1-2, That should be a 2

Arsenal - Aston Villa 2-1, That should be a 1

Arsenal - Aston Villa 2-2, That should be a X

I hope that you understand what I`m trying to do.

Best Regards

// Peter //





Dave Peterson said:
What do you have in E6?
what do you have in E8?

What did you expect?
What did you get?
Click to expand...
help
Click to expand...
 
And one more question?

Are both cells contain text or numbers:

Use a couple of helper cells and try =isnumber(e6) and =isnumber(e8)

They should be the same (true or false). If E6 contained the Text 1 (like '1)
and E8 contained the number 2 (not text), then I didn't get what I really
wanted.

Format each cell as General and reenter the values to see if that helps.
 
The error message is #Name?

The cells looks like this:
A B C D E F G
H
1996 Arsenal - Aston Villa 2 - 2 #Name?
1997 Arsenal - Aston Villa 0 - 0
1998 Arsenal - Aston Villa 1 - 0
1999 Arsenal - Aston Villa 3 - 1
2000 Arsenal - Aston Villa 1 - 0
2001 Arsenal - Aston Villa 3 - 2


This is just a little example of how it works, actually it is 2281 rows long
and it contains match statistics from 1996-2002. All I wanna know is if
its a home win,draw or away win (1X2) in column H. The file I downloaded
was a ASCII file with ; as separator.

Best Regards

// Peter //


Sandy Mann said:
Högberg

What error message exactly do you get #NAME? #VALUE or what? With the
values that you give, 1 in E6 and 2 in E8 both the formulas that you were
given should work.

If your data is in the form you give in the narrative ie "Arsenal - Aston
Villa 1-2" then

=IF(MID(E6,LEN(E6)-4,1)=RIGHT(E6,1),"X",IF(MID(E6,LEN(E6)-4,1)>RIGHT(E6,1),1
,2))

will return return the right result but then I do not know why you would be
talking about E6 and E8. Have you got only 1 in E6 and only 2 in E8?

If the data is in the written for in your narrative then I would not
recommend using my formula because it would be too easy to get something
wrong. If you miss off or add a space you will get a wrong result. If
Villa beat Arsenal 10 - 0 then you will have a problem (and so will
Arsenal!)

Regards

Sandy
Click to expand...
 
Thanks Guys,

Do to your help I made it (or you).

I had to change the that Chip gave me
=IF(E6>E8,1,IF(E6=E8,"X",2))

To:
=IF(E6>E8;1;IF(E6=E8;"X";2))

Now it works just fine.

Many thanks to Chip,Ron,Dave and Sandy Mann
for their help.

Best Regards

// Peter //
 
Back
Top