| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide LARGE if multiple returns
Hello Excel Users and Experts,
With the following, if the LARGE value occurs twice in the range, then two of the formulas will return B11. If it occurs three times then of course three of the formulas return B11. Can the formulas be modified to divide LARGE equally between the cells that return B11? So, if LARGE occured three times, and B11 = 70, then each return would be 23.33. If it was a seven way tie, then each would return 10. Notice that the formulas run from B15 to B23, skipping 19 and 20, probably not significant. =IF(B15=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B16=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B17=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B18=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B21=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B22=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B23=(LARGE($B$15:$B$23,1)),$B$11,"") Thanks Regards, Howard |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Divide LARGE if multiple returns
I probably misunderstood what you want
=B11/COUNTIF(B15:B23,LARGE(B15:B23,1)) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "L. Howard Kittle" wrote in message ... Hello Excel Users and Experts, With the following, if the LARGE value occurs twice in the range, then two of the formulas will return B11. If it occurs three times then of course three of the formulas return B11. Can the formulas be modified to divide LARGE equally between the cells that return B11? So, if LARGE occured three times, and B11 = 70, then each return would be 23.33. If it was a seven way tie, then each would return 10. Notice that the formulas run from B15 to B23, skipping 19 and 20, probably not significant. =IF(B15=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B16=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B17=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B18=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B21=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B22=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B23=(LARGE($B$15:$B$23,1)),$B$11,"") Thanks Regards, Howard |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Divide LARGE if multiple returns
Hi Peo,
Thanks for the response. That formula divides the Large value but not what I am looking for. I'll try again to be more clear, I hope. Say if B15 and B23 both = Large, then instead of each returning B11's value of 70, each would return 35. Regards, Howard "L. Howard Kittle" wrote in message ... Hello Excel Users and Experts, With the following, if the LARGE value occurs twice in the range, then two of the formulas will return B11. If it occurs three times then of course three of the formulas return B11. Can the formulas be modified to divide LARGE equally between the cells that return B11? So, if LARGE occured three times, and B11 = 70, then each return would be 23.33. If it was a seven way tie, then each would return 10. Notice that the formulas run from B15 to B23, skipping 19 and 20, probably not significant. =IF(B15=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B16=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B17=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B18=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B21=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B22=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B23=(LARGE($B$15:$B$23,1)),$B$11,"") Thanks Regards, Howard |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Divide LARGE if multiple returns
=IF(B15=(LARGE($B$15:$B$23,1)),$B$11/COUNTIF($B$15:$B$23,LARGE($B$15:$B$23,1)),"")
-- Regards, Peo Sjoblom http://nwexcelsolutions.com "L. Howard Kittle" wrote in message ... Hi Peo, Thanks for the response. That formula divides the Large value but not what I am looking for. I'll try again to be more clear, I hope. Say if B15 and B23 both = Large, then instead of each returning B11's value of 70, each would return 35. Regards, Howard "L. Howard Kittle" wrote in message ... Hello Excel Users and Experts, With the following, if the LARGE value occurs twice in the range, then two of the formulas will return B11. If it occurs three times then of course three of the formulas return B11. Can the formulas be modified to divide LARGE equally between the cells that return B11? So, if LARGE occured three times, and B11 = 70, then each return would be 23.33. If it was a seven way tie, then each would return 10. Notice that the formulas run from B15 to B23, skipping 19 and 20, probably not significant. =IF(B15=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B16=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B17=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B18=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B21=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B22=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B23=(LARGE($B$15:$B$23,1)),$B$11,"") Thanks Regards, Howard |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Divide LARGE if multiple returns
Smack on Peo, thanks a ton. Once I see it, it makes some sense to me.
Again, thanks Regards, Howard "L. Howard Kittle" wrote in message ... Hello Excel Users and Experts, With the following, if the LARGE value occurs twice in the range, then two of the formulas will return B11. If it occurs three times then of course three of the formulas return B11. Can the formulas be modified to divide LARGE equally between the cells that return B11? So, if LARGE occured three times, and B11 = 70, then each return would be 23.33. If it was a seven way tie, then each would return 10. Notice that the formulas run from B15 to B23, skipping 19 and 20, probably not significant. =IF(B15=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B16=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B17=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B18=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B21=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B22=(LARGE($B$15:$B$23,1)),$B$11,"") =IF(B23=(LARGE($B$15:$B$23,1)),$B$11,"") Thanks Regards, Howard |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Move multiple rows of data that are not sequential | Excel Discussion (Misc queries) | |||
| External Links and Multiple Instances | Excel Discussion (Misc queries) | |||
| average if, multiple if, divide if | Excel Worksheet Functions | |||
| How do I remove multiple line feed(s) or carriage return(s)? | Excel Discussion (Misc queries) | |||
| XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
Linear Mode
