If problem????

[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 //

 

[Chip Pearson]

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)

 

[Ron de Bruin]

Try this

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

 

[Högberg]

I get an wrong message.

// Peter //

Try this

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

 

[Högberg]

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 //

 

[Sandy Mann]

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

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 //

What do you have in E6?
what do you have in E8?

What did you expect?
What did you get?

help

 

[Sandy Mann]

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

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 //

What do you have in E6?
what do you have in E8?

What did you expect?
What did you get?

help

 

[Dave Peterson]

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.

 

[Högberg]

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 //

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]

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 //