L
Leo Heuser
Harlan Grove said:
Yes, I overlooked it. My mistake.
If you had just the slightest sense of humour, we would all enjoy
the absence of such remarks.
LeoH
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Yes, I overlooked it. My mistake.
If you had just the slightest sense of humour, we would all enjoy
the absence of such remarks.
LeoH
H
Harlan Grove
Leo Heuser said:If you had just the slightest sense of humour, we would all enjoy
the absence of such remarks.
I do, but it's not like yours. What you're really trying to say is that if
*I* acted more like *YOU*, *YOU*'d be happier. Not a chance.
A
andyp161
Thanks to everyone for there feedback - the problem is solved.
Kind regard
Kind regard
H
hgrove
Harlan Grove wrote...
...
The range of bonus amounts also isn't necessary if there are 27 o
fewer bonus levels. Use CHOOSE and put the bonus amounts into th
formula. Here's a 3-level formula. Note that the second argument to MI
must be one more than the number of levels, and the final argument t
CHOOSE must be 0.
C2:
=CHOOSE(MIN(SUMPRODUCT((B$2:B$21>=B2)
/COUNTIF(B$2:B$21,B$2:B$21)),4),1000,500,250,0
...
...Using a 20-row sample data range in A2:B21 and the following
Bonus range *INCLUDING* the zero value as the final entry, ...
and changing my cell C2 formula to
=INDEX(Bonus,MIN(SUMPRODUCT((B$2:B$21>=B2)
/COUNTIF(B$2:B$21,B$2:B$21)),ROWS(Bonus)))
The range of bonus amounts also isn't necessary if there are 27 o
fewer bonus levels. Use CHOOSE and put the bonus amounts into th
formula. Here's a 3-level formula. Note that the second argument to MI
must be one more than the number of levels, and the final argument t
CHOOSE must be 0.
C2:
=CHOOSE(MIN(SUMPRODUCT((B$2:B$21>=B2)
/COUNTIF(B$2:B$21,B$2:B$21)),4),1000,500,250,0
A
andyp161
Hi Leo,
Thank you for the formula you posted as below. No matter how hard
try, I cannot seem to establish a logical pattern in order to extend i
to a fourth and final bonus of 75. I'd really appreciate your hel
and, if possible, a brief explanation so that I might be able to creat
similar formulae myself in the future...
=(B2=MAX($B$2:$B$13))*500+(B2=LARGE($B$2:$B$13,COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))*250+(B2=LARGE($B$2:$B$13,COUNTIF($B$2:$B$13,LARGE($B$2:$B$13,COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))+COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))*125
Kind regatd
Thank you for the formula you posted as below. No matter how hard
try, I cannot seem to establish a logical pattern in order to extend i
to a fourth and final bonus of 75. I'd really appreciate your hel
and, if possible, a brief explanation so that I might be able to creat
similar formulae myself in the future...
=(B2=MAX($B$2:$B$13))*500+(B2=LARGE($B$2:$B$13,COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))*250+(B2=LARGE($B$2:$B$13,COUNTIF($B$2:$B$13,LARGE($B$2:$B$13,COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))+COUNTIF($B$2:$B$13,MAX($B$2:$B$13))+1))*125
Kind regatd
L
Leo Heuser
Hi Andy
I appreciate your feedback, but it really isn't worth the trouble
extending the formula. My second solution from the 15th with
the named formulae is a better choice. However, you should go
for Harlan's formula. It's the best choice for your problem.
I appreciate your feedback, but it really isn't worth the trouble
extending the formula. My second solution from the 15th with
the named formulae is a better choice. However, you should go
for Harlan's formula. It's the best choice for your problem.
A
andyp161
I cannot seem to set this formula within the ISERROR function - coul
someone please help me??
=CHOOSE(MIN(SUMPRODUCT((K$2:K$79>=K2)/COUNTIF(K$2:K$79,K$2:K$79)),5),500,250,125,75,"")
Kind regard
someone please help me??
=CHOOSE(MIN(SUMPRODUCT((K$2:K$79>=K2)/COUNTIF(K$2:K$79,K$2:K$79)),5),500,250,125,75,"")
Kind regard
A
andyp161
Hi,
Is it possible to extend the formula Harlon posted across a broke
array ie (B2:B21,B23:B30,B32:B41), so that except for the exclusions
the array is essentially B2:B41?
C2:
=CHOOSE(MIN(SUMPRODUCT((B$2:B$21>=B2)/COUNTIF(B$2:B$21,B$2:B$21)),4),1000,500,250,0)
Many thank
Is it possible to extend the formula Harlon posted across a broke
array ie (B2:B21,B23:B30,B32:B41), so that except for the exclusions
the array is essentially B2:B41?
C2:
=CHOOSE(MIN(SUMPRODUCT((B$2:B$21>=B2)/COUNTIF(B$2:B$21,B$2:B$21)),4),1000,500,250,0)
Many thank
H
hgrove
andyp161 wrote...
So what error or problem arises from entering
=IF(CHOOSE(MIN(SUMPRODUCT((K$2:K$79>=K2)
/COUNTIF(K$2:K$79,K$2:K$79)),5),500,250,125,75,"")>0,
CHOOSE(MIN(SUMPRODUCT((K$2:K$79>=K2)
/COUNTIF(K$2:K$79,K$2:K$79)),5),500,250,125,75,""),"")
?
If you mean something different, post your formula and explai
*PRECISELY* what's not working
I cannot seem to set this formula within the ISERROR function -
could someone please help me??
=CHOOSE(MIN(SUMPRODUCT((K$2:K$79>=K2)
/COUNTIF(K$2:K$79,K$2:K$79)),5),500,250,125,75,"")
So what error or problem arises from entering
=IF(CHOOSE(MIN(SUMPRODUCT((K$2:K$79>=K2)
/COUNTIF(K$2:K$79,K$2:K$79)),5),500,250,125,75,"")>0,
CHOOSE(MIN(SUMPRODUCT((K$2:K$79>=K2)
/COUNTIF(K$2:K$79,K$2:K$79)),5),500,250,125,75,""),"")
?
If you mean something different, post your formula and explai
*PRECISELY* what's not working
H
hgrove
andyp161 wrote...
...
*FIRST* don't change subject lines. It screws up Google Group
archives. [Also, ignore ExcelForum's claim that subject lines ar
optional. In ExcelForum that may be so, but not in USENET. When i
doubt, follow USENET procedures and ignore ExcelForum.]
*SECOND* don't misspell people's names!
Not easily. It's much easier to add a masking term to the numerator i
SUMPRODUCT than to use multiple area ranges.
What are in cells B22 and B31? If they don't contain numbers, yo
should be able to use
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)
If B22 and B31 do contain numbers, what do A22 and A31 contain? I
their contents differ significantly from (A2:A21,A23:A30,A32:A40), i
may be possible to change the ISNUMBER call above to a differen
expression masking out the A22 and A31 values
...
*FIRST* don't change subject lines. It screws up Google Group
archives. [Also, ignore ExcelForum's claim that subject lines ar
optional. In ExcelForum that may be so, but not in USENET. When i
doubt, follow USENET procedures and ignore ExcelForum.]
*SECOND* don't misspell people's names!
...Is it possible to extend the formula Harlan [corrected] posted
across a broken array ie (B2:B21,B23:B30,B32:B41), so that
except for the exclusions, the array is essentially B2:B41?
Not easily. It's much easier to add a masking term to the numerator i
SUMPRODUCT than to use multiple area ranges.
What are in cells B22 and B31? If they don't contain numbers, yo
should be able to use
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)
If B22 and B31 do contain numbers, what do A22 and A31 contain? I
their contents differ significantly from (A2:A21,A23:A30,A32:A40), i
may be possible to change the ISNUMBER call above to a differen
expression masking out the A22 and A31 values
H
hgrove
hgrove wrote...
...
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
...
...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
A
andyp161
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 order
from above are that this must remain the structure!); however, al
divisions compete for the same bonus points.
Many thanks for your continued assistance.
Kind regard
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 order
from above are that this must remain the structure!); however, al
divisions compete for the same bonus points.
Many thanks for your continued assistance.
Kind regard
A
andyp161
Hi Harlan,
Based on my last explanation, do you think that your formula can b
adapted to a a broken array?
Many thanks
And
Based on my last explanation, do you think that your formula can b
adapted to a a broken array?
Many thanks
And
H
hgrove
Retain or re-enter your original subject line in follow-ups. Ignore th
misinformation from excelforum that implies Subject lines ar
optional.
andyp161 wrote...
No, and it may not need to be. What are in the column A cells in th
rows in which the column B cells contain subtotals? It's likely yo
could add a masking expression to the numerator term that would exclud
the subtotals. While it's possible to hack a formula to handle multipl
area ranges, there's no need in this case, and it'd be ver
inefficient
misinformation from excelforum that implies Subject lines ar
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 th
rows in which the column B cells contain subtotals? It's likely yo
could add a masking expression to the numerator term that would exclud
the subtotals. While it's possible to hack a formula to handle multipl
area ranges, there's no need in this case, and it'd be ver
inefficient
A
andyp161
Hi Harlan,
The totals in column B that are to be excluded from the formula ar
labelled 'TOTAL' in column A.
Many thank
The totals in column B that are to be excluded from the formula ar
labelled 'TOTAL' in column A.
Many thank
H
Harlan Grove
andyp161 > said: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)
A
andyp161
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 hav
attached a sample worksheet if you wouldn't mind taking a look.
Many thank
Attachment filename: forum.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62699
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 hav
attached a sample worksheet if you wouldn't mind taking a look.
Many thank
Attachment filename: forum.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62699
H
hgrove
andyp161 wrote...
I *NEVER* open attached files. If you want me to help you, you need t
express yourself solely in plain text.
What, exactly, is the formula you're using, what are the first an
second place values, and for every column B cell in which you se
'TOTAL' have you checked that they contain no stray space characters
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 t
express yourself solely in plain text.
What, exactly, is the formula you're using, what are the first an
second place values, and for every column B cell in which you se
'TOTAL' have you checked that they contain no stray space characters
H
hgrove
I played around with this on my own system, and there could be a proble
due to rounding error. The standard way to count ditinct values in
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. O
my system, sometimes it returns 0.9999... and other times it returns 1
So, alter my formula to provide a slight rounding error correctio
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
due to rounding error. The standard way to count ditinct values in
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. O
my system, sometimes it returns 0.9999... and other times it returns 1
So, alter my formula to provide a slight rounding error correctio
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
A
andyp161
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 formul
=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 returnin
#VALUE!, it would seem the masking is interfering with the rest of th
formula which works fine on a single array without the masking
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 formul
=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 returnin
#VALUE!, it would seem the masking is interfering with the rest of th
formula which works fine on a single array without the masking
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.