• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shorter formula to return the difference between the lines

B

bines53

Active Member
Hello friends !

Is it possible to shorten the formulas in column G ,
One formula with the function AGGREGATE ?

Thank you !
 

Attachments

  • test 6..xlsx
    8.3 KB · Views: 10
Hi:
=AGGREGATE(15,6,(ROW($A$2:$A$31)-ROW($A$2)+1)/($A$2:$A$31=E2),1)-AGGREGATE(15,6,(ROW($A$2:$A$31)-ROW($A$2)+1)/($A$2:$A$31=D2),1), This part can be removed and the formula can be shortened by =AGGREGATE(15,6,ROW($A$1:$A$31)/($A$2:$A$31=E2),1)-AGGREGATE(15,6,ROW($A$1:$A$31)/($A$2:$A$31=D2),1)

Thanks
 
Hello friends !

Hi,

Explain what I mean, G2, what is the gap position, the first appearance of the two numbers 9, and 8.
And, G5 What is the difference of the first two instances of the number 7.
Now,Is it possible to put these two parts,($A$2:$A$31=E2) and ($A$2:$A$31=D2),

When the formula, the function AGGREGATE appears only once, not twice.


Thank you !
 
Hi.

If the numbers are different, then you can use simply:

=ABS(MMULT(MATCH(D2:E2,$A$2:$A$31,0),{-1;1}))

If the numbers are the same, then, to simplify your expression you can include an array of values for AGGREGATE's k parameter, and then pass to MMULT to perform the subtraction:

=MMULT(AGGREGATE(15,6,(ROW($A$2:$A$31)-ROW($A$2)+1)/($A$2:$A$31=D5),{1,2}),{-1;1})

Regards
 
NARAYANK991 said:
Hi Nebu ,

A slight correction :

The segment :

ROW($A$2:$A$31)-ROW($A$2)+1

is equivalent to :

ROW($A$1:$A$30)

Narayan
Click to expand...

Although this is true, it's preferable in general to use the construction as the OP has it.

If your data is in A2:A31, it's a little more convenient to be able to reference that in a formula, rather than first manually calculating the equivalent array which begins in row 1. What's more, if you're using Named Ranges for your data, then it's useful to be be able to reference these as they are in the formula. I actually always use the alternative construction:

=ROW($A$2:$A$31)-MIN(ROW($A$2:$A$31))+1

since, although it uses an extra function call, I almost always use Named Ranges for my data, and so the above is simply:

=ROW(Range)-MIN(ROW(Range))+1

With the alternative construction, i.e.:

ROW($A$2:$A$31)-ROW($A$2)+1

it's not quite as straightforward to give the equivalent version using the Named Range, since the part ROW($A$2) must first be obtained from it, which you could do using:

ROW(Range)-ROW(INDEX(Range,1))+1

though that's perhaps a tiny a bit more convoluted than the MIN version.

Regards
 
Hi ,

Known and agreed ; I was pointing out that the shortened formula posted by Nebu was not equivalent to the original version.

Narayan
 
@Narayan

I'd missed that part of the OP's query, so I thought you were recommending it yourself as an alternative. My apologies.

Regards
 
Hi ,

No apologies needed ; I am sorry if I sounded brusque. Your detailed explanation is welcome , and I am sure will be appreciated by everyone. I admire your detailed breakdown of intricate formulae on your website.

Narayan
 
@bines53

Thanks! Very kind! :)

I am honoured by these comments, and glad that my work there has been of interest to some.

Regards
 
Last edited:
@Asheesh

Sure. Just tried to add it to my profile, though it appears that I do not have sufficient privileges yet to edit that part of my account here.

http://excelxor.com/

Edit: I see that bines53 has already done that for me! Thanks!

Regards
 
You must log in or register to reply here.
Back
Top