Excel IF/LARGE Function problem

hgrove wrote...
...
C2:
=CHOOSE(MIN(SUMPRODUCT((B$2:B$41=B2)
*ISNUMBER(B$2:B$41)/COUNTIF(B$2:B$21,B$2:B$21)),
4),1000,500,250,0)
...

CARELESS!

Make that

=CHOOSE(MIN(SUMPRODUCT((B$2:B$41=B2)
*ISNUMBER(B$2:B$41)/COUNTIF(B$2:B$41,B$2:B$41)),
4),1000,500,250,0)


---
Message posted from http://www.ExcelForum.com/

Apologies Harlan!!

The cells I want to exclude contain totals of the broken arrays i.e.
each array refers to a particular division of the business (my orders
from above are that this must remain the structure!); however, all
divisions compete for the same bonus points.

Many thanks for your continued assistance.

Kind regards


---
Message posted from http://www.ExcelForum.com/

Hi Harlan,

Based on my last explanation, do you think that your formula can be
adapted to a a broken array?

Many thanks

Andy


---
Message posted from http://www.ExcelForum.com/

Retain or re-enter your original subject line in follow-ups. Ignore the
misinformation from excelforum that implies Subject lines are
optional.

andyp161 wrote...
Based on my last explanation, do you think that your formula
can be adapted to a a broken array?

No, and it may not need to be. What are in the column A cells in the
rows in which the column B cells contain subtotals? It's likely you
could add a masking expression to the numerator term that would exclude
the subtotals. While it's possible to hack a formula to handle multiple
area ranges, there's no need in this case, and it'd be very
inefficient.


---
Message posted from http://www.ExcelForum.com/

Hi Harlan,

The totals in column B that are to be excluded from the formula are
labelled 'TOTAL' in column A.

Many thanks


---
Message posted from http://www.ExcelForum.com/

"andyp161 " wrote...
The totals in column B that are to be excluded from the formula are
labelled 'TOTAL' in column A.

C2:
=CHOOSE(MIN(SUMPRODUCT((B$2:B$41=B2)*(A$2:A$41" TOTAL")
/COUNTIF(B$2:B$41,B$2:B$41)),4),1000,500,250,0)

Harlan,

Thanks for the formula. Unfortunately it doesn't seem to work i.e.
1000 points is awarded to the 1st and 2nd largest numbers. I have
attached a sample worksheet if you wouldn't mind taking a look.

Many thanks

Attachment filename: forum.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=626991
---
Message posted from http://www.ExcelForum.com/

andyp161 wrote...
Thanks for the formula. Unfortunately it doesn't seem to work
i.e. 1000 points is awarded to the 1st and 2nd largest
numbers. I have attached a sample worksheet if you wouldn't
mind taking a look.

I *NEVER* open attached files. If you want me to help you, you need to
express yourself solely in plain text.

What, exactly, is the formula you're using, what are the first and
second place values, and for every column B cell in which you see
'TOTAL' have you checked that they contain no stray space characters?


---
Message posted from http://www.ExcelForum.com/

I played around with this on my own system, and there could be a problem
due to rounding error. The standard way to count ditinct values in a
range is

SUMPRODUCT(1/COUNTIF(Range,Range))

In my latest testing, if there are three instances of the same value,
Excel could return 0.9999... rather than 1, but this isn't reliable. On
my system, sometimes it returns 0.9999... and other times it returns 1.


So, alter my formula to provide a slight rounding error correction
term.

C2:
=CHOOSE(MIN(SUMPRODUCT((B$2:B$41=B2)*(A$2:A$41" TOTAL")
/COUNTIF(B$2:B$41,B$2:B$41))+0.5/COUNT(B$2:B$41),4),1000,500,250,0)


---
Message posted from http://www.ExcelForum.com/

I'll try!!

Example:
A1:A15 as follows: TOTAL in A5, A10, A15
B1:B15 as follows: 1,2,3,4,=10,2,3,4,5,=14,3,4,5,5,=17
C1:C15 with formula
=CHOOSE(MIN(SUMPRODUCT((B$1:B$15=B1)*(A$1:A$15" TOTAL")/COUNTIF(B$1:B$15,B$1:B$15)),4),1000,500,250,0)
as follows:
0,0,250,1000,#VALUE!,0,0,250,1000,#VALUE!,250,1000 ,1000,1000.

Therefore the formula returns as follows:
1000 is input against 5(*3),4(*3).
500 is input against no numbers.
250 is input against 3(*3).

My objective is that the formula returns as follows:
1000 is input against all 5s
500 is input against all 4s
250 is input against all 3s

Although the formula appears to be ignoring TOTAL rows by returning
#VALUE!, it would seem the masking is interfering with the rest of the
formula which works fine on a single array without the masking.


---
Message posted from http://www.ExcelForum.com/