• 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.

Calculates difference with next cell with value

deciog

Active Member
Sirs, Good Morning.

I came across a problem that I did not find the solution for, so I ask for help.

You have to do the calculation with the next cell with value according to model

Thank you very much in advance

Decio
 

Attachments

Asheesh

Excel Ninja
Here you go !!
Frankly speaking, I am not happy with this since I know there must be a simpler way of doing this.. I am working on an alternate solution..will share as soon as I am done.
Meanwhile, you can try this:

Acknowledge with CTRL + SHIFT + ENTER

IFERROR(INDEX(LOOKUP(SMALL(IF($J$6:$J$99<>"",ROW($J$6:$J$99)),ROW($A$2:$A$99)),IF($J$6:$J$99<>"",ROW($J$6:$J$99)),$J$6:$J$99)-LOOKUP(SMALL(IF($J$6:$J$99<>"",ROW($J$6:$J$99)),ROW($A$1:$A$99)),IF($J$6:$J$99<>"",ROW($J$6:$J$99)),$J$6:$J$99),MATCH(IF(J6<>"",ROW()),SMALL(IF($J$6:$J$99<>"",ROW($J$6:$J$99)),ROW($A$1:$A$99)),0)),"")
 

Asheesh

Excel Ninja
here is the simpler one ..

Acknowledge with CTRL + SHIFT + ENTER

IFERROR(IF(J6="","",LOOKUP(SMALL(IF($J$6:$J$99<>"",ROW($J$6:$J$99)),1+COUNT($J$6:J6)),ROW($J$6:$J$99),$J$6:$J$99)-J6),"")
 

Marc L

Excel Ninja
Hi !​
I can't understand why not using the direct calculation between cells like =J17-J6 ?​
A VBA code can easily fill formulas in column L but as here it's not the VBA section …​
 

deciog

Active Member
Marc L

I post a template, the original worksheet has more than 20,000 lines, it is difficult to make one by one

Decio
 

Peter Bartholomew

Well-Known Member
At the core of my formula is a search for the next largest record number 'k' corresponding to a non-zero value
= MINIFS( k, k, ">"&k, Valores, "<>" )
The catch is that this is an n-squared operation since every calculated row requires a search over every row of the table. This can get expensive for large values of 'n'. The next step is to return the corresponding value and subtract the current value.
= INDEX( Valores, MINIFS( k, k, ">"&k, Valores, "<>" ) ) - Valores
This calculation only takes place on rows where there is a non-blank value and the final value is excluded.
 

Attachments

deciog

Active Member
Peter

Very good your formula. perfect, I liked it a lot

I like the way you do the formula

Thank you so much

- - - - - - - - - - - - - - -
Peter

I noticed that transforming the non-matrix formula, works wonderfully confer

Using the formula in this way

= IF($L$2:$L$98 * ($K$2:$K$98 < MAXIFS($K$2:$K$98,$L$2:$L$98,"<>")), INDEX($L$2:$L$98, MINIFS($K$2:$K$98,$K$2:$K$98, ">"&$K$2:$K$98,$L$2:$L$98, "<>" ) ) - $L$2:$L$98, "" )

Decio
 
Last edited:

Peter Bartholomew

Well-Known Member
Decio
One issue is that we are using very different versions of Excel; that makes a more profound difference than whether to use names or direct references. When I put your formula into cell R2 it calculated the entire array in column R!

Conversely, to stop my version [Office 365 insider] from creating a spilt array, I had to turn the value parameters of MINIFS into single values by introducing the implicit intersection operator '@'
= IF( @Valores * (@k < MAXIFS(k,Valores,"<>")), INDEX( Valores, MINIFS( k, k, ">"&@k, Valores, "<>" ) ) - @Valores, "" )
This emulates the traditional working of Excel, which inserts an implicit intersection step to reduce the output of calculation to a single value.

In the dynamic array formula version of Excel it is simpler to create an entire array output than it is to create a single output and then, presumably, clone it to give the rest of the output array. Changing to a CSE formula was easiest route for me to export the formula to standard Excel since that works the same in both versions.
 

Peter Bartholomew

Well-Known Member
Hi Guido
Inspired by you I tried a PQ solution but tried to keep it simple in order to stay within my understanding. It worked but I tend to produce 'connection only' queries that are merely steps within an existing query that I will wish to reuse. I cannot help but think that this is simply the product of my failure to reference anything but the immediately previous step whilst building a query. Is there a better way?
 

Attachments

deciog

Active Member
Guido and Peter

Thanks for the Power Query solution

In case my client does not have this option

But I liked it

Decio
 

Chihiro

Excel Ninja
There are multiple avenues that you can take within PQ to arrive at the solution.

Like Guido & Peter did, using index and multiple joins.
Which is the process I'd recommend using. As it does not have high reliance in coding and most operation can be done using GUI.

Another is to use List functions to perform calculation (One advantage of this method is speed of data refresh over above method).

First load the data, then add 1 based index column.

Add custom column with following calculation.
Code:
=if [Valores] <> null then try List.First(List.RemoveNulls(List.LastN(#"Added Index"[Valores],List.Count(#"Added Index"[Valores])-[Index]))) - [Valores] otherwise null else null
Then change data type, and remove unnecessary columns. Load the data back.

Alternately, you could eliminate Index column by using List.Generate... but that's bit more complicated and adds little benefit.
 
Last edited:

p45cal

Well-Known Member
maybe:
Code:
=IF(COUNT(J6),INDEX($J$1:$J$99,MIN(IF((ISBLANK($J7:$J$99)),FALSE,ROW($J7:$J$99))))-J6,"")
array-entered in cell L6 and copied down?
 

bosco_yip

Excel Ninja
maybe:
Code:
=IF(COUNT(J6),INDEX($J$1:$J$99,MIN(IF((ISBLANK($J7:$J$99)),FALSE,ROW($J7:$J$99))))-J6,"")
array-entered in cell L6 and copied down?
The above array formula will return a #VALUE! error in N94.

Perhaps, try this shorter non-array formula.

In N7, copied down :

=IF(J6="","",IFERROR(1/VLOOKUP("*",INDEX(1/J7:J$99&"",),1,)-J6,""))

Regards
Bosco
 

GraH - Guido

Well-Known Member
There are multiple avenues that you can take within PQ to arrive at the solution.
...
Another is to use List functions to perform calculation (One advantage of this method is speed of data refresh over above method).
...
Code:
=if [Valores] <> null then try List.First(List.RemoveNulls(List.LastN(#"Added Index"[Valores],List.Count(#"Added Index"[Valores])-[Index]))) - [Valores] otherwise null else null
...
As I am still discovering list functions - I do like them a lot - I fail to understand how some work though, thus fail to use the most performant one. I tried to use List.Transform(Many) but got nowhere.
 

GraH - Guido

Well-Known Member
Hi Guido
Inspired by you I tried a PQ solution but tried to keep it simple in order to stay within my understanding. It worked but I tend to produce 'connection only' queries that are merely steps within an existing query that I will wish to reuse. I cannot help but think that this is simply the product of my failure to reference anything but the immediately previous step whilst building a query. Is there a better way?
I also do use "connections only" a lot for the same reason, @Peter Bartholomew , and I am by far an expert in the matter. If the result is there via the UI, at least it can be easily understood by many users. For sure, it is not always the most optimal way. Often I cannot say which option would be more efficient. Trial and error is my game then.
One of the main advantages of using referenced queries in my opinion, is that you keep "code" rather short and thus more flexible in case changes would be needed or when being in "debug" mode. I'm clueless to which extend this has an impact on performance though. I wouldn't be surprised if @Chihiro would be more insightful on the matter.
As to referring to earlier steps, it is done by adapting the referenced step in the M-code, like " #"Added Index" ". It is one of the reasons I preach naming the steps like "AddIndex", to avoid the hashtag "#" and quotes in the formula.
Small site note. As I am not a programmer - I am a schooled teacher btw in Dutch, history and (Christian) religion go figure - I dazzle when I see comments like the ones you tend to post my dear. :)
 

GraH - Guido

Well-Known Member
Using List functions only, I finally ended up with this single line of M-code, referring to the first query in which the Table Valores is turned into a list without null values.
Code:
 List.RemoveNulls(List.Transform(LstValores, each _ - (List.Last(List.FirstN(LstValores,(List.PositionOf(LstValores, _  ) ) ) ) ) ) )
I'm sure there is a shorter, faster way.
 

Attachments

deciog

Active Member
Chihiro, Guido

I like it, it works well

Thank you

__________________________________________

p45cal

It did not work, something wrong.

__________________________________________

Bosco,

Wow works very well perfect, fast

Congratulations

___________________________________________
Thank you very much all for the help

Decio
 
Top