How to filter out rows with formula?

English
, ,
#21

Thanks you so much for that elaborate reply. I will make an effort to absorb everything! It might be upp to 300 rows that builds up gradually . In some dokument there will be less.

Because it’s late here I don’t have time to work on this problem right now however. But would like to through out another question regarding and other problem in this same document that are driving me nuts!

As I mentioned before I’m using this formula in my dokument:

=IF(B21="RRR";G20+D21*E21;IF(C21="RRR";G20-D21*E21))

Sometimes it generates a negative results. When this happens I would like 0 in that cell instead of the negative number.

I have tried to add many different additional condition and has found two that works on there own namly:

=OM(D20-D21*E21<0;0;D20-D21*E21)
=OM(20-D21*E21<0;D20-D21*E21;0

I have tried the two formulas abow with other preseeding conditions with no problem. But when combined with the first formula I mentiond I simply cant make it work but see no reason why it wouldent.

Edit: I cant se the multiplications in the formulas I refere to. They are soposed på be betwean D21 and E21.

#22

Sorry, I still don’t know which localization uses OM() instead of IF(). Therefore, I can not translate the formula for your Calc. And for the English-speaking Calc, it should be something like:

=MAX(0; IF(B21="RRR";G20+D21*E21;IF(C21="RRR";G20-D21* E21 ;G20 )))

Please note the third parameter of the inner IF() - I have bolded it on purpose.
You are missing it, always. Yes, you are entitled to it. However, if you had indicated it, you most likely would not have encountered this problem:

#23

sv Swedish. (and MAX() is MAX()).

1 Like
#24

Yes its Sweden.

#25

Brilliant it works! There is only one problem and that’s is when a new negative value is generated below the first one. By this piece of the formula: G20-D21 “Times” E21 (I add Times here because I cant see the multiply sign). In this case the the resulting calculation would look like this: 0-X “Times” X. Hence first time it works and there will be a 0 instead of a negative number. But when RRR occurs in the B column a second time The formula cant deal with it.

Is there away around this? ideally the formula should generate a new 0 in this case as well. And continue doing so until the calculation shifts to G20+D21*E21 and once again generates a positive value.

The full formula looks of course looks like this: =MAX(0; IF(B21=“RRR”;G20+D21*E21;IF(C21=“RRR”;G20-D21"Times"E21)

Also I bin reading about the suggestions you made on filtering (I’m overwhelmed by the helpfulness by the way). Only importing rows with particular values in it is brilliant and will extremely useful in some cases for instance where there are a large data sett.

In some cases however where there is only a smaller sett of data. I think “skipping” rows or rather not including them in calculation would be the best solution. That is because the other rows can be of value in some cases.

Such a solution would have to add an element to this formula;

=MAX(0; IF(B21=“RRR”;G20+D21*E21;IF(C21=“RRR”;G20-D21 “Times” E21

That would enable that the formula skip every row that dosent have RRR in it. This must be fairly complex.

I guess on can say the formula do skip rows already but run in to an obstacles because this G20+D21 “Times” E21 and this G20-D21"Times"E21 piece of the formula will automatically deal with the value in precising row. And this roll will repeat it self when filling up the cells.

In order to work with in an sett och mixed rows where not very row has RRR in A or B. It would require that the formula to take the preceding row with RRR in A or B in to account but don’t include values from intermediate rows in the same column. Lets say the flowing calculation or its negative counterpart is preformed:

G20+D21*E21

And that row 21 is followed by 3 rows that doesn’t have RRR in A or B. Next time this peace of the calculation (if ts even relevant at this point) would have to do this:

G20+D24*E24.

I cant figure out how to realize this. I suppose an other IF value that has RRR and A and B in it would have to be added? Possibly there’s and other operator is this case?

I cant see the Multiply sign i some pieces of the text added this instead “Times”

#26

I was wrong this one actually skips rows progressively when rows are negative. It also skip’s rows that dosent have RRR in B or C!

=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))

There one problem how ever when a new positive value is generated insted of a negative one the formula should add the the last positive latest value to the new one. Rather then just starting allover again. This Illustrates the principle again:

30000

So in G7 there sould be 21170 (G3+G7) not 20868.

Is there a way to achieve this?

#27

Yes, there is such a way and it is not difficult. Just add the formula to cell H2

=SUM($G$1:$G2)
(Be careful with the $ sign - $G$1 is two characters, and $G2 is only one, before the letter, before the number it is not!)
Stretch the formula down - for each row you have a running total.
If you want to show values not for all rows, but only where the sum has changed, then complicate the formula a little:
=IF(G2;SUM($G$1:$G2);"")

#28

Thanks! And since column G can be hidden it looks perfect to even tough its not in one formula.

#29

Because the * asterisk is used for inline formatting of text in italics or bold and so for literal code you have to format that using either single ` backticks before and after a short portion of code, or use ``` triple backticks on separate lines before and after code. Formatting as code also prevents automatic typographic quotation marks instead of ASCII quote characters in the display of text. See also This is the guide - How to use the Ask site? - #6 by erAck .

#30

Thanks for the information

Actually this function: =SUM($G$1:$G2)

Did not do what I hopped for. As it add up the entire G. column it will not provide a relevant sum because every new row in the G column is already a calculation of preceding rows .This is not apparent by the picture I put up. But but can be seen in this function.

=MAX(0; IF(B21="RRR";G20+D21*E21;IF(C21="RRR";G20-D21* E21

I suppose the formal abow could also be alters to work along with this one: =SUM($G$1:$G2). But that might case other problems. So hopefully there more elegant solution that solves he problem by altering one of the functions below?

=SUM($G$1:$G2)
=MAX(0; IF(B21="RRR";G20+D21*E21;IF(C21="RRR";G20-D21* E21

#31

I returnerad to this lite projekt after a brak and have now spent a all nigit trying to fix it.

The objektive is as explaind abow to make a formula or a combination of formulas that skipps irrelevant rows (that doset have RRR in A or B), that adds or subtracts the rows with RRR in A or B.
Last a 0 should be generated in the palace of a negative number:

The problem here is that the gap betwean the rows mess up the chain of adding or subtracting the rows with the preceding one.

So far the sugestion whas to add the fist formula below to the secound one

=MAX(0;SUM($G$1:$G2)
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))

This will not work however becouse this will creat an irrelevant sum becose the first formula already sums up values (it will be a sum of a sum if you like).

My attempt to deal with this is to replace the secound formula with this one:

=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))

This formula will unlike the first one creat positiv or negativ value and not add upp the numbers meaning that it might work with this one:

=MAX(0;SUM($G$1:$G2)

And this combination do work in many ways. As it adds upp relvant number from the first row and genrates a zero insted of neagtiv value. The remaning probklem is that this formula: =MAX(0;SUM($H$1:$H2)). Vill not start over agin once the 0 is genreated but will keep using the entiere row. as shown in te example.

what it sould do in the case is to start over and the numbers 4678,4 and 2694,8 but it will not becous the row as a total is still negativ

I tried more or less silly solutions alll night including this one:

MAX(0;SUM(H2:H3;IF(I3=0;I2))

Is there solution to this problmen. It would be great if some one could provide it. At this point after hours of attempts am even curious for more than practical reasons. Affcourse other formulas is just as good as long as it salves the problem.

#32

I returnerad to this lite projekt after a brak and have now spent a all nigit trying to fix it.

The objektive is as explaind abow to make a formula or a combination of formulas that skipps irrelevant rows (that doset have RRR in A or B), that adds or subtracts the rows with RRR in A or B.
Last a 0 should be generated in the palace of a negative number:

The problem here is that the gap betwean the rows mess up the chain of adding or subtracting the rows with the preceding one.

So far the sugestion whas to add the fist formula below to the secound one

=MAX(0;SUM($G$1:$G2)
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))

This will not work however becouse this will creat an irrelevant sum becose the first formula already sums up values (it will be a sum of a sum if you like).

My attempt to deal with this is to replace the secound formula with this one:

=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))

This formula will unlike the first one creat positiv or negativ value and not add upp the numbers meaning that it might work with this one:

=MAX(0;SUM($G$1:$G2)

And this combination do work in many ways. As it adds upp relvant number from the first row and genrates a zero insted of neagtiv value. The remaning probklem is that this formula: =MAX(0;SUM($H$1:$H2)). Vill not start over agin once the 0 is genreated but will keep using the entiere row. as shown in te example.

what it sould do in the case is to start over and the numbers 4678,4 and 2694,8 but it will not becous the row as a total is still negativ

I tried more or less silly solutions alll night including this one:

MAX(0;SUM(H2:H3;IF(I3=0;I2))

Is there solution to this problmen. It would be great if some one could provide it. At this point after hours of attempts am even curious for more than practical reasons. Affcourse other formulas is just as good as long as it salves the problem.

#33

=MAX(0;SUM($H$1:$H2))

sorry I forgoth to uppload. This exampel ilustrates the problem.

formula: =MAX(0;SUM($H$1:$H2)). Vill not start over agin once the 0 is genreated but will keep using the entiere row. as shown in te example.

what it sould do in the case is to start over and the numbers 4678,4 and 2694,8 but it will not becous the row as a total is still negativ

There might be a completely different solution to this that doesn’t involve the formulas:

=MAX(0;SUM($G$1:$G2)
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))

#34

Please read the last sentence again - “summation with the previous line creates a problem”. This is already the answer to your question: just remove the previous value from the formula, throw out G1 altogether.
Just write the simple phrase in the Calc function language
“If RRR is in column B of the current row, then the value from column D of the same row multiplied by the value from column E of the same row, otherwise if RRR is in column C of the current row, then the same product with a minus sign ELSE 0”.
That’s all, this is enough to get one column with values positive, negative and zero. Now, in the next column, do the summation with accumulation, hide the auxiliary column.

#35

Im sure you saying somthing smart that I dont get. I acturly made a typ error but it seams as if youre in bord any way.

This will work:

“=IF(B69=“SSWM”;D69 * E69;IF(C69=“SSWM”;D69 * E69*-1))”
“=MAX(0;SUM($H$1:$H2)”

I tride this before.

But it works only as long as colum I in the picture abow is possitve. When a 0 is generated it sould start over again and not use the entier row agin. For exampel In the H colum in the picture there is one cell with -100 0000 in it. this would normaly generat a negativ number in the I colum but the fomrula deals with this and insted provide a 0.

Thats is exactly what I whant. However i the cell in colum I that coresponds to the cell in colum H with 4678,8 in it. There soud not be a 0 but 4678,8 . And in the next cell below that value the formula soul add 4678,4 and 2694,8.

Insted there are 0 all the way untill the H comum as a whole is positiv agin. The reson is ofcource that the formula uses the intire H comum in every new calculatuon. When it sould start over after a 0 has bin generated.

Is there any way to fix this?

Perhaps thers a wat to compensat so that colum H as a whole never getts negativ. Thats seams to be a a strange solution tough.

#36

Tride to deleat this post.

#37

Just would like to add that this one actually works perfectly:

=MAX(0;$L58+$K59)

It whas a unexpected soultion to me.

In conjunction with this one offcouce:

=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.