Simple Rank Macro

[alocke]

Hi,
Just a simple thing - If I want to Rank data in cells A1,A3,A5,A7 amongst one another and also cells A2,A4,A6,A8 too.

The code I'm doing is more complex than this, but if someone can tell me how to do this then I'll be sorted. I think.

Cheers,
Alex

 

[alocke]

Yeah I thought it had too, it seemed to work on a small test. Maybe the problem is in all my data.

But I'll try and describe what I mean in better detail. Could turn out that the macro already does it, but just want to clarify whether it is/isn't the problem.

So back to the five cells I listed previously. If one was to perform a normal RANK function between them;

U408 - 1st
U208 - 2nd
U8 - 3rd
U808 - 4th
U608 - Ignored

What seems to be happening though is;
U608 is 'Ignored' as in it doesn't get Ranked. (So the cell next to it is blank)

But the formula in the cell V8 say, looks like this; =(.....(RANK(U8,( U8,U208,U408,U608,U808)))) [I've left out the other part of the formula]

As U608 is present in this formula, is it therefore included in the Range which U8 is compared against? Because at the moment all of V8,V208,V408,V608,V808, N/A values are appearing in them.

If this isn't the case,
does your code ignore every type of error value (i.e. REF, NUM etc.)?

I'll try and find a PC and may be able to tell you what I mean better via excel genie.

Thanks,
Alex.

 

[Peter_SSs]

1. What is the formula in column U (say U8 for example)?

2. Do you get all different errors in column U, or just #N/A?

3. I'm not familiar with Excel on a Mac, or what version you have anyway, but does the function IFERROR() exist in your version?

 

[alocke]

1. What is the formula in column U (say U8 for example)?

=IF(ISNUMBER(U8),PERCENTRANK((U8,U208,U408,U608,U808,U1008),U8),"") . This code works perfectly when all values are numbers and not errors. But doesn't work when there are errors.

2. Do you get all different errors in column U, or just #N/A?

Multiple errors, #REF/ #N/A, #NUM, #DIV/0.

3. I'm not familiar with Excel on a Mac, or what version you have anyway, but does the function IFERROR() exist in your version?

As far as I'm aware Mac excel is pretty much identical to windows version, as in it has all the same formulae and vba works exactly the same.

- I sent this to a PC and it worked in exactly the same way, so the Mac version isn't the problem I don't think. I am running on Office 10
' also.

Thanks.

 

[Peter_SSs]

=IF(ISNUMBER(U8),PERCENTRANK((U8,U208,U408,U608,U808,U1008),U8),"") . This code works perfectly when all values are numbers and not errors. But doesn't work when there are errors.

This looks like a formula from column V, not column U. Could I have the column U formula?

As far as I'm aware Mac excel is pretty much identical to windows version, as in it has all the same formulae ... so the Mac version isn't the problem I don't think.

Yes, but for Windows Excel, versions before Excel 2007 do not have the IFERROR() function but Excel 2007 and Excel 2010 do. I was trying to find out if your version does or doesn't. Could you check? Try this formula:

=IFERROR(1/0,"I have it")

 

[alocke]

This looks like a formula from column V, not column U. Could I have the column U formula?

Oh sorry:
U8: =(0.2*Q8)+(0.35*R8)+(0.45*S8). The problem is Q8,R8 and S8 all are made from other equations and calculations which require this external program to receive the data from.

I know why #N/A values are appearing, its because the data doesn't actually exist on the program. But I just have to leave it at that.

Also the "=IFERROR(1/0,"I have it") " works.

Thanks.

 

[Peter_SSs]

So, try changing the U8 formula to:

=IFERROR((0.2*Q8)+(0.35*R8)+(0.45*S8),"")

and apply that down to the other formula cells in column U.

 

[alocke]

Of course! Yep it all seems to be running perfectly now.
I want to thank you so much for helping me with this part of the project peter, and others too.
You've helped me understand VBA to a much greater level than I previously did.
Thanks so much,
Alex

 

[alocke]

Just out of curiosity, #36 the code you gave me.
If you had say a set of data which had to match multiple cases, i.e:

Select Case x.value

........................

End Select

Select Case y.value

.....................

End Select

How would you go about something like that? Just because it could be useful for later.
Cheers.

 

[alocke]

Or d'you need to do an 'If/else' ?

 

[Peter_SSs]

It isn't really clear to me what you would be trying to do with Select Case of the If..Then..Else


However, if you are using post #36 code, you may need to re-visit my earlier question, repeated below. That is because it seems from posts #46 and 47 that column U (and therefore V also) can now contain "".

.. what happens if the last value or the third last value is one of the null strings ("") because the corresponding cell in column U was blank or an error?

Your previous response to this question was (post #34)

there will be no Null string values in the calculation