If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#41
|
|||
|
|||
Excel IF/LARGE Function problem
"andyp161 " wrote...
.... 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. |
#42
|
|||
|
|||
Excel IF/LARGE Function problem
Hi Harlan,
Thanks once again for your feedback. Your 'rounding' solution seems to work ONLY if a 'majority' of cells in the points array contain numbers 0. 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. I'm sure you're getting a bit fed up with this problem, but I am extremely grateful. Kind regards --- Message posted from http://www.ExcelForum.com/ |
#43
|
|||
|
|||
Excel IF/LARGE Function problem
"andyp161 " wrote...
Thanks once again for your feedback. Your 'rounding' solution seems to work ONLY if a 'majority' of cells in the points array contain numbers 0. . . . 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"T otal") /(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. |
#44
|
|||
|
|||
Excel IF/LARGE Function problem
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$201B2)*($B$2:$B$201""),$B$2:$B$201))) =1)+0),0)) to be entered with ShiftCtrlEnter, also if edited later. Copy C2 down. -- Best Regards Leo Heuser Followup to newsgroup only please. "andyp161 " skrev i en meddelelse ... Hi Harlan, Thanks once again for your feedback. Your 'rounding' solution seems to work ONLY if a 'majority' of cells in the points array contain numbers 0. 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. I'm sure you're getting a bit fed up with this problem, but I am extremely grateful. Kind regards --- Message posted from http://www.ExcelForum.com/ |
#45
|
|||
|
|||
Excel IF/LARGE Function problem
Many thanks Harlan/Leo
However, due to further complications I have decided to take Harlan's advice and use Harlan's original formula that works without complications on a complete array, and use VLookup to complete the 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, so points have not yet been awarded for August onwards), #VALUE! is returned. Is there any way "0" could be returned instead - I have 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(SUM PRODUCT((G$3:G$80=G3)/COUNTIF(G$3:G$80,G$3:G$80)),5),500,250,125,75,0)) Many thanks --- Message posted from http://www.ExcelForum.com/ |
#46
|
|||
|
|||
Excel IF/LARGE Function problem
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) "andyp161 " wrote in message ... Many thanks Harlan/Leo However, due to further complications I have decided to take Harlan's advice and use Harlan's original formula that works without complications on a complete array, and use VLookup to complete the 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, so points have not yet been awarded for August onwards), #VALUE! is returned. Is there any way "0" could be returned instead - I have 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,25 0,125,75,0)) Many thanks --- Message posted from http://www.ExcelForum.com/ |
#47
|
|||
|
|||
Excel IF/LARGE Function problem
"Peo Sjoblom" wrote...
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) |
#48
|
|||
|
|||
Excel IF/LARGE Function problem
"Leo Heuser" wrote...
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$201B2)*($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. |
#49
|
|||
|
|||
Excel IF/LARGE Function problem
"Harlan Grove" wrote...
.... 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) |
#50
|
|||
|
|||
Excel IF/LARGE Function problem
"Harlan Grove" skrev i en meddelelse
... "Leo Heuser" wrote... 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$201B2)*($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. 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 :-) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with Office Excel | aphmhidc | Worksheet Functions | 1 | June 3rd, 2004 03:41 PM |
Mouse Wheel Function Lost in Excel VBA 2003 | Sid | Setting up and Configuration | 2 | April 10th, 2004 02:53 AM |
Excel XP Nested If Function Problem | DaffyD® | Worksheet Functions | 2 | March 6th, 2004 06:46 PM |
GETPIVOTDATA function in Excel 2000 | LS | Worksheet Functions | 1 | February 26th, 2004 11:54 AM |