Excel IF/LARGE Function problem

[Harlan Grove]

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).

....

See my follow-up to myself (in excelforum, my second posting in sequence).
This is a rounding error problem that requires adding a small value to the
SUMPRODUCT result.

 

[andyp161]

Hi Harlan,

Thanks once again for your feedback. Your 'rounding' solution seems t
work ONLY if a 'majority' of cells in the points array contain number

0. Also, although I can't identify an exact pattern, the formula i

also very sensitive to the size of the division TOTALS eg, if only on
person within a division is awarded points so that the total for tha
division is equal to that sole person's score, this will cause th
formula to return erroneous results across the array, even if ever
other person in every other division is awarded points of differen
amounts.

I'm sure you're getting a bit fed up with this problem, but I a
extremely grateful.

Kind regard

 

[Harlan Grove]

Thanks once again for your feedback. Your 'rounding' solution seems to
work ONLY if a 'majority' of cells in the points array contain numbers

Do you mean your points column could contain positive and negative values?

. . . Also, although I can't identify an exact pattern, the formula is
also very sensitive to the size of the division TOTALS eg, if only one
person within a division is awarded points so that the total for that
division is equal to that sole person's score, this will cause the
formula to return erroneous results across the array, even if every
other person in every other division is awarded points of different
amounts.

My fault. I assumed the points column actually contained something like
sales figures which would be positive for all individuals. It appears you
really do mean points, and those points appear to be positive, zero or
negative for any individual. This can still be handled by a single
self-contained formula, but the necessary filtering will make the formula
LARGE.

C2 [not an *ARRAY* formula]:
=CHOOSE(MIN(SUM(($B$2:$B$201>=B2)*($A$2:$A$201<>"Total")
/(MMULT(($B$2:$B$201=TRANSPOSE($B$2:$B$201))
*TRANSPOSE($A$2:$A$201<>"Total"),ROW($B$2:$B$201)^0)
+($A$2:$A$201="Total")))*(A2<>"Total")+0.5/COUNT($B$2:$B$201),
4),1000,500,250,0)

If this doesn't work because, for example, there may be blank rows in the
range, the formula would get MUCH larger still. At that point, a single
formula solution becomes problematic. Still possible, but not a good idea.
The better approach would be to use one range without breaks of any kind
using my original formula with the rounding error correction, then use
simple VLOOKUP formulas to populate the report it seems you're trying to put
together.

While I have a tendency to give mostly single cell or as few cells as
possible solutions in my ng responses, there are times when such solutions
are clearly inferior to multiple cell solutions. This looks like one of
those times. The bonus awards you want can be calculated simply and
self-contained *if* the range in which they're calculated contains only the
individuals' points. Adding divisional subtotals complicates the calculation
considerable, and adding blank rows would complicate it further. At that
point, a 2-stage solution, a simple calculation of bonus awards given points
and a simple set of lookup formulas to populate the report given the
previously calculated bonus awards, would be easier to maintain.

 

[Leo Heuser]

Hi again

This array formula looks promising:

Names in A2:A201 and down, points in B2:B201 and down
Scattered in column A the text "Total" and in
the corresponding cell in column B, a SUM()-formula.
In H2 and down bonus points. If you want bonus points
for the 3 best, enter bonus points in H2:H4, for the 4
best enter points in H2:H5 etc.

The formula works for all numbers in column B: negative,
zero and positive. Zero (if among the chosen best) will give
bonus points, an empty cell will always return 0.

In C2 enter:

=IF(OR(A2="Total",B2=""),0,OFFSET($H$2, SUMPRODUCT((
FREQUENCY((IF(($A$2:$A$201<>"Total")*($B$2:$B$201>B2)*
($B$2:$B$201<>""),$B$2:$B$201)),(IF(($A$2:$A$201<>"Total")*
($B$2:$B$201>B2)*($B$2:$B$201<>""),$B$2:$B$201)))>=1)+0),0))

to be entered with <Shift><Ctrl><Enter>, also if edited later.

Copy C2 down.

 

[andyp161]

Many thanks Harlan/Leo

However, due to further complications I have decided to take Harlan'
advice and use Harlan's original formula that works withou
complications on a complete array, and use VLookup to complete th
report. Harlan, I do however have one (hopefully!) last favour to ask
If, using the formula:

=CHOOSE(MIN(SUMPRODUCT((G$3:G$80>=G3)/COUNTIF(G$3:G$80,G$3:G$80)),5),500,250,125,75,0


G3:G80 contains 0s only (i.e. the report I have set up is for 2004, s
points have not yet been awarded for August onwards), #VALUE! i
returned. Is there any way "0" could be returned instead - I hav
tried the following, but it doesn't seem to work.

=IF(ISERROR(MIN(SUMPRODUCT((G$3:G$80>=G3)/COUNTIF(G$3:G$80,G$3:G$80)),5)),"0",CHOOSE(MIN(SUMPRODUCT((G$3:G$80>=G3)/COUNTIF(G$3:G$80,G$3:G$80)),5),500,250,125,75,0))

Many thank

 

[Peo Sjoblom]

I haven't really followed this thread but the value error is because the
index number is zero, not tested you should be able to use

=CHOOSE(MIN(SUMPRODUCT((G$3:G$80>=G3)/COUNTIF(G$3:G$80,G$3:G$80)),5)+1,0,500
,250,125,75,0)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Harlan Grove]

I haven't really followed this thread but the value error is because the
index number is zero, not tested you should be able to use

=CHOOSE(MIN(SUMPRODUCT((G$3:G$80>=G3)/COUNTIF(G$3:G$80,G$3:G$80)),5)+1,
0,500,250,125,75,0)

....

I can't figure out why zero would result from the SUMPRODUCT expression. If
every cell in G3:G80 were zero, then (G$3:G$80>=G3) returns an array of all
TRUE entries (so all 1s in arithmetic context), and COUNTIF returns an array
all the entries of which should be 1/78, so
SUMPRODUCT({1;..;1}/({1;..;1}/78)) should return 1 *EXCEPT FOR* rounding
error. However, if the fundge factor I suggested a few rounds ago were
included, the SUMPRODUCT+FF term would be 1 effectively.

Peo's approach may work when the rounding error makes the SUMPRODUCT term
return just less than an integer, as it does when there are 78 cells, but it
won't work when there's no rounding error, as would occur for G3:G66 (64
cells). In that case, Peo's formula would return 1000 for all cells.

If no bonus should be awarded when all points are zero (or perhaps more
accurately no points are positive), try

=IF(COUNTIF(G$3:G$80,">0"),CHOOSE(MIN(SUMPRODUCT((G$3:G$80>=G3)
/COUNTIF(G$3:G$80,G$3:G$80))+0.5/COUNT(G$3:G$80),5),500,250,125,75,0)

 

[Harlan Grove]

This array formula looks promising: ....
In C2 enter:

=IF(OR(A2="Total",B2=""),0,OFFSET($H$2, SUMPRODUCT((
FREQUENCY((IF(($A$2:$A$201<>"Total")*($B$2:$B$201>B2)*
($B$2:$B$201<>""),$B$2:$B$201)),(IF(($A$2:$A$201<>"Total")*
($B$2:$B$201>B2)*($B$2:$B$201<>""),$B$2:$B$201)))>=1)+0),0))

....

This begs the question whether any bonuses should be awarded if the highest
points amount isn't positive.

Also it requires that column H contain only the bonus amounts and nothing
else. If there were 12 distinct point amounts, and if cell H13 happened to
contain "foo", then the person(s) with the lowest points would have "foo"
appear as their bonus.

 

[Harlan Grove]

If no bonus should be awarded when all points are zero (or perhaps more
accurately no points are positive), try

=IF(COUNTIF(G$3:G$80,">0"),CHOOSE(MIN(SUMPRODUCT((G$3:G$80>=G3)
/COUNTIF(G$3:G$80,G$3:G$80))+0.5/COUNT(G$3:G$80),5),500,250,125,75,0)

Might help is I got the formula right.

=IF(COUNTIF(G$3:G$80,">0"),CHOOSE(MIN(SUMPRODUCT((G$3:G$80>=G3)
/COUNTIF(G$3:G$80,G$3:G$80))+0.5/COUNT(G$3:G$80),5),500,250,125,75,0),0)

 

[Leo Heuser]

...

This begs the question whether any bonuses should be awarded if the highest
points amount isn't positive.

Your guess is as good as mine. The OP seems to disclose the constraints
in small pieces, so who is to know?
For all I know, the bonus could be awarded the 4 persons with the smallest
losses (assuming all persons had a loss, and that a loss is entered as a
negative number)

Also it requires that column H contain only the bonus amounts and nothing
else. If there were 12 distinct point amounts, and if cell H13 happened to
contain "foo", then the person(s) with the lowest points would have "foo"
appear as their bonus.

Of course. It should have followed from my remark "In H2 and down bonus
points.", that column H was to be used for bonuses only.
Should anybody get a "foo", he/she can always look at it as an
added bonus