![]() |
|
|
|||||||
| FlashChat | Actuarial Discussion | Preliminary Exams | CAS/SOA Exams | Cyberchat | Around the World | Suggestions |
![]() |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
|
I just used the formula =SUM(LARGE(C4:C16,{1,2}))
just curious if i wanted to sum more than the top 2 values if there is a much better way of doing this. say the top 1000 values. Potential ties exist at the cutoff point exist.
__________________
John 3:16 |
|
#2
|
||||
|
||||
|
Making no claim that this is the most efficient way...
=SUMIF($D$17:$D$1141,">"&LARGE($D$17:$D$1141,1000) ,$D$17:$D$1141)+LARGE($D$17:$D$1141,1000)*(1000-COUNTIF($D$17:$D$1141,">"&LARGE($D$17:$D$1141,1000 )))
__________________
It's not that easy, Marcy. First you have to get money orders under an assumed name to pay the rent ... then, where do you keep the extra key? ... and just try getting phone service turned on using an alias these days ... not that I've given it much thought. We have to return to some very common-sense principles that everyday Americans live by every time they go to the grocery store or want to go to the movies or cash their paycheck, and that is you can't spend more than you have. -- Robert Gibbs ![]() מַרְבֶּה נְכָסִים מַרְבֶּה דְאָגָה |
|
#3
|
|||
|
|||
|
ah found something similar to mine....
entered as an array formula (ctrl shift enter) =sum(large(a1:a10000,row(indirect("1:1000"))))
__________________
John 3:16 |
|
#4
|
|||
|
|||
|
I have seen some creative use of sumproduct in here. Here's another way to go about it getting the result.
=SUM(A1:A10000)-SUMPRODUCT(A1:A10000,--(A1:A10000<LARGE(A1:A10000,1000))) I take it you want to add everything greater and everything equal to the 1000 Largest. I summed everything and subtracted everything smaller.
__________________
I'm not an actuary, but I play one on TV. |
|
#5
|
|||
|
|||
|
Quote:
=SUMPRODUCT(A1:A10000,--(A1:A10000>=LARGE(A1:A10000,1000))) Just use the Greater than or equal to, I guess I was over thinking it.
__________________
I'm not an actuary, but I play one on TV. |
|
#6
|
|||
|
|||
|
Not really i'm looking to add up 1000 numbers, if the is a 5 way tie at the 1000th number and i have already added 998 numbers i want 2 of the 5 tie.
__________________
John 3:16 |
|
#7
|
|||
|
|||
|
Here's my solution:
=SUMIF($A$1.$A$10000, ">"&N(LARGE($A$1.$A$10000,1000))) +(1000-COUNTIF($A$1.$A$10000, ">"&N(LARGE($A$1.$A$10000,1000)))) *LARGE($A$1.$A$10000,1000) |
![]() |
| Thread Tools | |
| Display Modes | |
|
|