Comparing non-numerical data, for example 2D and 2E. = and <=

[mtaylor]

Hi all,

I am comparing student marks from two columns. All the other year groups have simply used numeric values which I can easily compare, unfortunately one group has used letters with their numbers!

Here's what I was previously using:

=sumproduct(--('KG2'!E3:E26<='KG2'!F3:F26))

All the results for this new tab (KG1) are either 1D or 1E, with 1D being greater than 1E

I essentially have two cells, one calculating how many are <= and one calculating are<



Many thanks in advance

Matt

 

[Rick Rothstein]

It might help if you told us what the limits are for the grades. That is, what is the lowest and highest number grade? When letters are used... are there only one letter after the number? is there always a number when there is a letter? what range of letters are we talking about? You can also add anything else about your grading system that you think might help (remember, we know nothing about what your data looks like so you have to tell us).

 

[mtaylor]

It might help if you told us what the limits are for the grades. That is, what is the lowest and highest number grade? When letters are used... are there only one letter after the number? is there always a number when there is a letter? what range of letters are we talking about? You can also add anything else about your grading system that you think might help (remember, we know nothing about what your data looks like so you have to tell us).

True, thanks Rick

At present it only contains 1E and then 1D, however as the year progresses 1C, 1B and 1A would come into operation. 1A being the best, 1E being the worst.

 

[Rick Rothstein]

Does this formula give you the results you expect...

=SUMPRODUCT(--(LEFT('KG2'!B3:B26)&IFERROR("."&CODE(MID('KG2'!B3:B26,2,1))-64,".0")<=LEFT('KG2'!C3:C26)&IFERROR("."&CODE(MID('KG2'!C3:C26,2,1))-64,".0")))

 

[Peter_SSs]

You don't mention or give any examples from this group of marks starting with anything but "1". If that is so then this may do what you want.

=SUMPRODUCT(--(RIGHT('KG2'!E3:E26,1)>=RIGHT('KG2'!F3:F26,1)))

If the leading number can be anything other than 1, please give some realistic examples (eg could it be more than one digit?) and the expected results with any further explanation. For example, is 2A better or worse than 1B etc?

 

[mtaylor]

Love it, many thanks chaps.

There may be developments to this as the year progresses but for now I'd say we're laughing.

Thanks again.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.