Simplify formulas

  • Thread starter Thread starter 0-0 Wai Wai ^-^
  • Start date Start date
0

0-0 Wai Wai ^-^

I would like to simplify the following formulas
=SUM( LARGE(A1:A1000,{1,2,3...500}) )

Is it possible to simplify "{1,2,3...500}"?
Currently I need to type each number manually by myself, ie 1,2,3,4,5,6,7,8,9
and so on which is time-consuming.
Is there any expression which tells the computer that it's "from 1 to 500"?
 
Try this *array* formula:

=SUM(LARGE(A1:A1000,ROW(A1:A500)))
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I would like to simplify the following formulas
=SUM( LARGE(A1:A1000,{1,2,3...500}) )

Is it possible to simplify "{1,2,3...500}"?
Currently I need to type each number manually by myself, ie
1,2,3,4,5,6,7,8,9
and so on which is time-consuming.
Is there any expression which tells the computer that it's "from 1 to 500"?
 
Wai Wai,

Cutting your range down (to make testing easier):

=SUMPRODUCT((A1:A10>=LARGE(A1:A10,5))*(A1:A10))

will sum every value from the 5th largest to the largest. However, if you
have more than one value equal to the 5th value then they will all be added
in. If that is a possibility then use:

=SUMPRODUCT((A1:A10>LARGE(A1:A10,5))*(A1:A10))+LARGE(A1:A10,5)

Wich will only add one of the 5th largest values

HTH


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
One way:

=SUM(LARGE(A1:A1000,ROW(INDIRECT("1:500"))))

This must be array-entered (CTRL-SHIFT-ENTER or CMD-RETURN).
 
JE McGimpsey said:
One way:

=SUM(LARGE(A1:A1000,ROW(INDIRECT("1:500"))))

This must be array-entered (CTRL-SHIFT-ENTER or CMD-RETURN).
Click to expand...

Sorry, I can't understand why " ROW(INDIRECT("1:500")) " will work.
It seems the computer will interpret it as "ROW(A1:A500)". Why is it so?
What's the use of using this formula instead?
 
that is simply because ROW(INDIRECT("1:500")) or ROW(A1:A500) returns the
array {1,2,3...500} :-)
to check that, select the cell that contains the formula, go to formula bar,
select only ROW(INDIRECT("1:500")) within the formula and press F9. The
advantage of this approach is that you only need to give Excel the first and
the last numbers, the disadvantage - you use two functions (one relatively
slow) instead of a fixed array.

Regards,
KL
 
Yes, you're correct that the computer will interpret it the same as
"ROW(A1:A500)",
*BUT* ... the difference is that with Indirect(), 1:500 is "Cast In
Concrete".

Insert rows, especially at the beginning, from Row1, and see how the
references change in "ROW(A1:A500)", but with " ROW(INDIRECT("1:500")) ",
the rows remain exactly the same, 1:500.

It's what folks call "more robust", meaning it tries to take many
precautions into consideration,
like row insertion.
However, there are those who might want the range to expand with insertions.
So, it depends on what you want.

All the respondents in these groups are always forced to speculate on what
the OP *may* really want and/or need.

I speculated one way, John the other.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------



that is simply because ROW(INDIRECT("1:500")) or ROW(A1:A500) returns the
array {1,2,3...500} :-)
to check that, select the cell that contains the formula, go to formula bar,
select only ROW(INDIRECT("1:500")) within the formula and press F9. The
advantage of this approach is that you only need to give Excel the first and
the last numbers, the disadvantage - you use two functions (one relatively
slow) instead of a fixed array.

Regards,
KL
 
What I wrote, didn't come out sounding right.
I didn't really mean "range", as in cell range, but was referring to range
as in numbers to calculate.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Yes, you're correct that the computer will interpret it the same as
"ROW(A1:A500)",
*BUT* ... the difference is that with Indirect(), 1:500 is "Cast In
Concrete".

Insert rows, especially at the beginning, from Row1, and see how the
references change in "ROW(A1:A500)", but with " ROW(INDIRECT("1:500")) ",
the rows remain exactly the same, 1:500.

It's what folks call "more robust", meaning it tries to take many
precautions into consideration,
like row insertion.
However, there are those who might want the range to expand with insertions.
So, it depends on what you want.

All the respondents in these groups are always forced to speculate on what
the OP *may* really want and/or need.

I speculated one way, John the other.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------



that is simply because ROW(INDIRECT("1:500")) or ROW(A1:A500) returns the
array {1,2,3...500} :-)
to check that, select the cell that contains the formula, go to formula bar,
select only ROW(INDIRECT("1:500")) within the formula and press F9. The
advantage of this approach is that you only need to give Excel the first and
the last numbers, the disadvantage - you use two functions (one relatively
slow) instead of a fixed array.

Regards,
KL
 
Hi RagDyeR,

Actually, I never disagreed with that and did not compare the two solutions.
I just explained to the OP why either ROW(A1:A500) or ROW(INDIRECT("1:500"))
would work like {1,2,3...500}

:-))

KL
 
Sorry, was meant for the OP.

I HATE when I do that !<bg>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi RagDyeR,

Actually, I never disagreed with that and did not compare the two solutions.
I just explained to the OP why either ROW(A1:A500) or ROW(INDIRECT("1:500"))
would work like {1,2,3...500}

:-))

KL
 
Back
Top