RE: Formula required.
- From: xlmate <xlsmate@xxxxxxxxx>
- Date: Fri, 23 Jan 2009 12:58:03 -0800
Hi Mike
Simply excellent ;)
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.
Thank You
cheers, francis
"Mike H" wrote:
Hi,.
Nice approach to the problem. How about this to make it cope with 3 way ties
=SUM(1*(L1>=$L$1:$L$8))-ROUND((SUM(1*(L1=$L$1:$L$8))-1)/COUNTIF(L$1:L$8,L1),2)
Mike
"xlmate" wrote:
forgot to mention that this is an array formula, after placing the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly
brackets wrap around the formula in the formula bar.
HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.
Thank You
cheers, francis
"xlmate" wrote:
assuming that your data start in row 1, try this formula in L1 and copy down
=SUM(1*(L1>=$L$1:$L$8))-(SUM(1*(L1=$L$1:$L$8))-1)/2
Does this do what you what?
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.
Thank You
cheers, francis
"sherbrooke" wrote:
I have rows of figures over a number of columns, starting at column A,
with totals for each row in column K. I then want to allocate values
from 1 to 8 to each of the rows, in column L, with 8 to the highest
total and 1 to the lowest total, if 2 rows are the same total they each
receive half of the combined figures, as per the examples below:-
(This is a very simple example of what I use, in reality there are some
24 columns and 16 rows, where the values are from 1 to 16 rather than 1
to 8)
ABCDE FG H I J K L
Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8
Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2
Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5
Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5
Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1
Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7
Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5
Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5
What I require is a formula which will automatically insert the
appropriate value, 1 to 8 in the example above.
I would be most grateful for any suggestions.
--
JohnD
- References:
- Formula required.
- From: sherbrooke
- RE: Formula required.
- From: xlmate
- RE: Formula required.
- From: xlmate
- RE: Formula required.
- From: Mike H
- Formula required.
- Prev by Date: Formatting to text - apostrophe does not show
- Next by Date: Adding a new template spread sheet on the first day of a new month
- Previous by thread: RE: Formula required.
- Next by thread: Re: Formula required.
- Index(es):
Relevant Pages
|
