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:
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:
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”