I have a data like below: (where I have A column and B column; A column has sequence same as given in data table i.e 1+,1,2+,2,2-,3+ and so on. B column has also different values, (for example purpose i used 2 values only.) I have 3 asks.
1. want to retain the sequence of A column as it is; and with 1 cell moving downward (from 1+ to 1, 1 to 2+, 2+ to 2 and so on), considering B column value 1+,0.5% ; 1,0.5% and so on, want to compare C column data. Data in C column must be equal or incremental as compared to previous cell.
2. Same condition is applicable for B Column. With the incremental value of B column 0.5% to 25%, value for 1+,25% must be greater or equal to 1+,0.5%.....need to check this for all combinations. 1,0.5% must be equal or lesser than 1,25%.
3. Generate output column as comments/flag
Input Data:
S | A column | B Column | C Column |
a | 1+ | 0.5% | 250 |
a | 1 | 0.5% | 254 |
a | 2+ | 0.5% | 260 |
a | 2 | 0.5% | 260 |
a | 2- | 0.5% | 260 |
a | 3+ | 0.5% | 260 |
a | 3 | 0.5% | 270 |
a | 3- | 0.5% | 270 |
a | 4+ | 0.5% | 270 |
a | 4 | 0.5% | 270 |
a | 4- | 0.5% | 270 |
a | 5+ | 0.5% | 280 |
a | 5 | 0.5% | 282 |
a | 5- | 0.5% | 284 |
a | 6+ | 0.5% | 286 |
a | 6 | 0.5% | 288 |
a | 6- | 0.5% | 290 |
a | 7 | 0.5% | 292 |
a | 8 | 0.5% | 300 |
a | 9 | 0.5% | 350 |
a | 10 | 0.5% | 500 |
a | 1+ | 25% | 300 |
a | 1 | 25% | 305 |
a | 2+ | 25% | 310 |
a | 2 | 25% | 320 |
a | 2- | 25% | 340 |
a | 3+ | 25% | 340 |
a | 3 | 25% | 353 |
a | 3- | 25% | 360 |
a | 4+ | 25% | 372 |
a | 4 | 25% | 381 |
a | 4- | 25% | 391 |
a | 5+ | 25% | 400 |
a | 5 | 25% | 410 |
a | 5- | 25% | 420 |
a | 6+ | 25% | 429 |
a | 6 | 25% | 440 |
a | 6- | 25% | 448 |
a | 7 | 25% | 460 |
a | 8 | 25% | 467 |
a | 9 | 25% | 475 |
a | 10 | 25% | 480 |
Output Data
A column | B Column | Value | Check2 |
1+ | 0.5% | 250 | Correct |
1 | 0.5% | 254 | Correct |
2+ | 0.5% | 260 | Correct |
2 | 0.5% | 260 | Correct |
2- | 0.5% | 260 | Correct |
3+ | 0.5% | 260 | Correct |
3 | 0.5% | 270 | Correct |
3- | 0.5% | 270 | Correct |
4+ | 0.5% | 270 | Correct |
4 | 0.5% | 270 | Correct |
4- | 0.5% | 270 | Correct |
5+ | 0.5% | 280 | Correct |
5 | 0.5% | 282 | Correct |
5- | 0.5% | 284 | Correct |
6+ | 0.5% | 286 | Correct |
6 | 0.5% | 288 | Correct |
6- | 0.5% | 290 | Correct |
7 | 0.5% | 292 | Correct |
8 | 0.5% | 300 | Correct |
9 | 0.5% | 350 | Correct |
10 | 0.5% | 500 | Correct |
1+ | 25% | 300 | Correct |
1 | 25% | 305 | Correct |
2+ | 25% | 310 | Correct |
2 | 25% | 320 | Correct |
2- | 25% | 340 | Correct |
3+ | 25% | 340 | Correct |
3 | 25% | 353 | Correct |
3- | 25% | 360 | Correct |
4+ | 25% | 372 | Correct |
4 | 25% | 381 | Correct |
4- | 25% | 391 | Correct |
5+ | 25% | 400 | Correct |
5 | 25% | 410 | Correct |
5- | 25% | 420 | Correct |
6+ | 25% | 429 | Correct |
6 | 25% | 440 | Correct |
6- | 25% | 448 | Correct |
7 | 25% | 460 | Correct |
8 | 25% | 467 | Correct |
9 | 25% | 475 | Correct |
10 | 25% | 480 | Incorrect |
I am not following what is going on in the data set but it sounds like you can use a Multi Row formula to compare the Row0 to Row-1 or Row+1. Perform the calculation with an IF/THEN statement and then have it either be 'Correct' or 'Incorrect' based on the condition you need. It would be a little easier to figure out what's going on if you included a formula of what the test is between each row.