In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.

Try this:
=IF(E1="","",SUMPRODUCT(--(E1<E$1:E$34),1/COUNTIF(E$1:E$34,E$1:E$34&""))+1)
Ctrl+Shift+Enter

Or this:
=RANK(E1,$E$1:$E$34)
Ctrl+Shift+Enter


Regards,
Ryan---


--
RyGuy


"JBG" wrote:

In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.

I copied the numbers that were in row D into row E should I just have that
row empty and is that why it is still not working?

"ryguy7272" wrote:

Try this:
=IF(E1="","",SUMPRODUCT(--(E1<E$1:E$34),1/COUNTIF(E$1:E$34,E$1:E$34&""))+1)
Ctrl+Shift+Enter

Or this:
=RANK(E1,$E$1:$E$34)
Ctrl+Shift+Enter


Regards,
Ryan---


--
RyGuy


"JBG" wrote:

In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.

Make sure the numbers that you are ranking are in column E, because that is
the column being referenced, and when you enter the functionmake sure you
hold down the Control key and the Shift key and the Enter key ALL AT THE SAME
TIME (or hold down CTRL then hold down Shift then hold down Enter). Try it;
it should work fine for you...


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

I copied the numbers that were in row D into row E should I just have that
row empty and is that why it is still not working?

"ryguy7272" wrote:

Try this:
=IF(E1="","",SUMPRODUCT(--(E1<E$1:E$34),1/COUNTIF(E$1:E$34,E$1:E$34&""))+1)
Ctrl+Shift+Enter

Or this:
=RANK(E1,$E$1:$E$34)
Ctrl+Shift+Enter


Regards,
Ryan---


--
RyGuy


"JBG" wrote:

In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.

Thank you, I think I got it. If you have three rankings and you autosum the
three which I have done. How do you rank the autosum?
=RANK(D4,$D$4:$D$34) is the formula for all three rankings, then the formula
for autosum is
=SUM(E4,I4,M4).
When I try the formula =RANK(N4,$N$4:$N$34) the cell displays #N/A. Does it
have to do with the autosum?



"ryguy7272" wrote:

Make sure the numbers that you are ranking are in column E, because that is
the column being referenced, and when you enter the functionmake sure you
hold down the Control key and the Shift key and the Enter key ALL AT THE SAME
TIME (or hold down CTRL then hold down Shift then hold down Enter). Try it;
it should work fine for you...


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

I copied the numbers that were in row D into row E should I just have that
row empty and is that why it is still not working?

"ryguy7272" wrote:

Try this:
=IF(E1="","",SUMPRODUCT(--(E1<E$1:E$34),1/COUNTIF(E$1:E$34,E$1:E$34&""))+1)
Ctrl+Shift+Enter

Or this:
=RANK(E1,$E$1:$E$34)
Ctrl+Shift+Enter


Regards,
Ryan---


--
RyGuy


"JBG" wrote:

In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.

You lost me now. Your Rank function looks correct and your Sum function is
correct too, so I'm not sure why it's not working for you. I'd say just
fiddle around with it for a bit and you'll probably figure it out.


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

Thank you, I think I got it. If you have three rankings and you autosum the
three which I have done. How do you rank the autosum?
=RANK(D4,$D$4:$D$34) is the formula for all three rankings, then the formula
for autosum is
=SUM(E4,I4,M4).
When I try the formula =RANK(N4,$N$4:$N$34) the cell displays #N/A. Does it
have to do with the autosum?



"ryguy7272" wrote:

Make sure the numbers that you are ranking are in column E, because that is
the column being referenced, and when you enter the functionmake sure you
hold down the Control key and the Shift key and the Enter key ALL AT THE SAME
TIME (or hold down CTRL then hold down Shift then hold down Enter). Try it;
it should work fine for you...


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

I copied the numbers that were in row D into row E should I just have that
row empty and is that why it is still not working?

"ryguy7272" wrote:

Try this:
=IF(E1="","",SUMPRODUCT(--(E1<E$1:E$34),1/COUNTIF(E$1:E$34,E$1:E$34&""))+1)
Ctrl+Shift+Enter

Or this:
=RANK(E1,$E$1:$E$34)
Ctrl+Shift+Enter


Regards,
Ryan---


--
RyGuy


"JBG" wrote:

In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.

I used the trace problem and when someone was deleting a row there was a
little left and it was throwing if off somehow. Which is my first question,
once I delete row 7 how do I get what was row 8 to turn into row 7. Should I
worry about it because it may throw off my forumulas? My second main
question is how do I get a reverse ranking for lack of better term? For
example, basketball scoring high is good "regular ranking." Golf on the
other hand scoring high is bad. I am trying to rank the last column as if
the rows were golfers and scoring high is bad and should have a high number
not a low number. I am using the =RANK(N4,$N$4:$N$34) formula. My third
question involves an if, then statement. I have not ever used one and are
you able to help with that, before I post?

"ryguy7272" wrote:

You lost me now. Your Rank function looks correct and your Sum function is
correct too, so I'm not sure why it's not working for you. I'd say just
fiddle around with it for a bit and you'll probably figure it out.


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

Thank you, I think I got it. If you have three rankings and you autosum the
three which I have done. How do you rank the autosum?
=RANK(D4,$D$4:$D$34) is the formula for all three rankings, then the formula
for autosum is
=SUM(E4,I4,M4).
When I try the formula =RANK(N4,$N$4:$N$34) the cell displays #N/A. Does it
have to do with the autosum?



"ryguy7272" wrote:

Make sure the numbers that you are ranking are in column E, because that is
the column being referenced, and when you enter the functionmake sure you
hold down the Control key and the Shift key and the Enter key ALL AT THE SAME
TIME (or hold down CTRL then hold down Shift then hold down Enter). Try it;
it should work fine for you...


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

I copied the numbers that were in row D into row E should I just have that
row empty and is that why it is still not working?

"ryguy7272" wrote:

Try this:
=IF(E1="","",SUMPRODUCT(--(E1<E$1:E$34),1/COUNTIF(E$1:E$34,E$1:E$34&""))+1)
Ctrl+Shift+Enter

Or this:
=RANK(E1,$E$1:$E$34)
Ctrl+Shift+Enter


Regards,
Ryan---


--
RyGuy


"JBG" wrote:

In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.

Now I'm totally confused. If you send me an email, with explicit
instructions of what you want, I will do it for you.

Regards,
Ryan---

....take out the xxx part
--
RyGuy


"JBG" wrote:

I used the trace problem and when someone was deleting a row there was a
little left and it was throwing if off somehow. Which is my first question,
once I delete row 7 how do I get what was row 8 to turn into row 7. Should I
worry about it because it may throw off my forumulas? My second main
question is how do I get a reverse ranking for lack of better term? For
example, basketball scoring high is good "regular ranking." Golf on the
other hand scoring high is bad. I am trying to rank the last column as if
the rows were golfers and scoring high is bad and should have a high number
not a low number. I am using the =RANK(N4,$N$4:$N$34) formula. My third
question involves an if, then statement. I have not ever used one and are
you able to help with that, before I post?

"ryguy7272" wrote:

You lost me now. Your Rank function looks correct and your Sum function is
correct too, so I'm not sure why it's not working for you. I'd say just
fiddle around with it for a bit and you'll probably figure it out.


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

Thank you, I think I got it. If you have three rankings and you autosum the
three which I have done. How do you rank the autosum?
=RANK(D4,$D$4:$D$34) is the formula for all three rankings, then the formula
for autosum is
=SUM(E4,I4,M4).
When I try the formula =RANK(N4,$N$4:$N$34) the cell displays #N/A. Does it
have to do with the autosum?



"ryguy7272" wrote:

Make sure the numbers that you are ranking are in column E, because that is
the column being referenced, and when you enter the functionmake sure you
hold down the Control key and the Shift key and the Enter key ALL AT THE SAME
TIME (or hold down CTRL then hold down Shift then hold down Enter). Try it;
it should work fine for you...


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

I copied the numbers that were in row D into row E should I just have that
row empty and is that why it is still not working?

"ryguy7272" wrote:

Try this:
=IF(E1="","",SUMPRODUCT(--(E1<E$1:E$34),1/COUNTIF(E$1:E$34,E$1:E$34&""))+1)
Ctrl+Shift+Enter

Or this:
=RANK(E1,$E$1:$E$34)
Ctrl+Shift+Enter


Regards,
Ryan---


--
RyGuy


"JBG" wrote:

In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.

Problem resolved! Just reused the same rank function and applied conditional
formatting and summed CF colors with code from he
http://www.cpearson.com/excel/colors.htm
http://www.cpearson.com/excel/CFColors.htm

Regards,
Ryan--
--
RyGuy


"ryguy7272" wrote:

Now I'm totally confused. If you send me an email, with explicit
instructions of what you want, I will do it for you.

Regards,
Ryan---

...take out the xxx part
--
RyGuy


"JBG" wrote:

I used the trace problem and when someone was deleting a row there was a
little left and it was throwing if off somehow. Which is my first question,
once I delete row 7 how do I get what was row 8 to turn into row 7. Should I
worry about it because it may throw off my forumulas? My second main
question is how do I get a reverse ranking for lack of better term? For
example, basketball scoring high is good "regular ranking." Golf on the
other hand scoring high is bad. I am trying to rank the last column as if
the rows were golfers and scoring high is bad and should have a high number
not a low number. I am using the =RANK(N4,$N$4:$N$34) formula. My third
question involves an if, then statement. I have not ever used one and are
you able to help with that, before I post?

"ryguy7272" wrote:

You lost me now. Your Rank function looks correct and your Sum function is
correct too, so I'm not sure why it's not working for you. I'd say just
fiddle around with it for a bit and you'll probably figure it out.


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

Thank you, I think I got it. If you have three rankings and you autosum the
three which I have done. How do you rank the autosum?
=RANK(D4,$D$4:$D$34) is the formula for all three rankings, then the formula
for autosum is
=SUM(E4,I4,M4).
When I try the formula =RANK(N4,$N$4:$N$34) the cell displays #N/A. Does it
have to do with the autosum?



"ryguy7272" wrote:

Make sure the numbers that you are ranking are in column E, because that is
the column being referenced, and when you enter the functionmake sure you
hold down the Control key and the Shift key and the Enter key ALL AT THE SAME
TIME (or hold down CTRL then hold down Shift then hold down Enter). Try it;
it should work fine for you...


Regards,
Ryan--

--
RyGuy


"JBG" wrote:

I copied the numbers that were in row D into row E should I just have that
row empty and is that why it is still not working?

"ryguy7272" wrote:

Try this:
=IF(E1="","",SUMPRODUCT(--(E1<E$1:E$34),1/COUNTIF(E$1:E$34,E$1:E$34&""))+1)
Ctrl+Shift+Enter

Or this:
=RANK(E1,$E$1:$E$34)
Ctrl+Shift+Enter


Regards,
Ryan---


--
RyGuy


"JBG" wrote:

In my workbook I have a set of numbers that I want to rank. I have a empty
colomn and when I use the rank function it displays the error window. I know
I am making this harder than needed. What do I use in the number, ref,
order? The numbers I want to rank numbers E4:E34 and have column F empty.