Car users have fuel cards. When paying for refueling and purchasing other products at a gas station, they provide the mileage of the car on the fuel cards. Sometimes users make mistakes - they provide wrong mileage, too small or too large. I have a table that shows the last 3 mileage and date readings and, similarly, a year ago, 3 mileage and date readings. When the data is correct, it is obviously best to read the most distant data available in the annual table, but when the data is incorrect, things get more complicated. Please suggest functions in the N, O, P, Q columns. The table shows most of the possible cases that may occur. I think that valuable information for the person writing the formula is that the difference between the readings should not be more than 1,500 (km) and 3,000 (km) when comparing 2 extreme readings with 3. I think that's enough description - the table will clearly show what goes.
| choosing the correct one out of three.xlsx | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
| 1 | I need formulas for columns N,O,P,Q (rows from 6 to 27) - searching for correct, sideways data in the table | |||||||||||||||||||||||||
| 2 | Permissible difference between columns E-C & G-E and K-I & M-K | From | From | 0 | 1500 | |||||||||||||||||||||
| 3 | Permissible difference between columns G-C and M-I | From | From | 0 | 3000 | |||||||||||||||||||||
| 4 | ||||||||||||||||||||||||||
| 5 | Nuber | Data 1 | Mileage from a year ago 1 | Data 2 | Mileage from a year ago 2 | Data 3 | Mileage from a year ago 3 | Data 4 | Actual mileage 4 | Data 5 | Actual mileage 5 | Data 6 | Actual mileage 6 | Data 1-3 reference to col. C,E,G | Expected choice col. C,E,G | Data 4-6 reference to col. I,K,M | Expected choice col. I,K,M | Description of unusual data (examples) | E-C (from 0 to 1500) | G-E (from 0 to 1500) | K-I (from 0 to 1500) | M-K (from 0 to 1500) | G-C (from 0 to 3000) | M-I (from 0 to 3000) | ||
| 6 | 1 | 2023-01-13 | 20005 | 2023-01-29 | 20480 | 2023-02-03 | 20760 | 2023-09-01 | 28700 | 2023-10-13 | 28666 | 2023-10-17 | 29014 | 2023-01-13 | 20005 | 2023-10-17 | 29014 | typical, correct data | 475 | 280 | -34 | 348 | 755 | 314 | ||
| 7 | 2 | 2022-11-14 | 144998 | 2022-11-30 | 145661 | 2022-12-14 | 146279 | 2023-06-30 | 149701 | 2023-07-19 | 150183 | 2023-09-17 | 150820 | 2022-11-14 | 144998 | 2023-09-17 | 150820 | typical, correct data | 663 | 618 | 482 | 637 | 1281 | 1119 | ||
| 8 | 3 | 2022-11-16 | 41000 | 2022-11-16 | 41000 | 2022-12-08 | 41850 | 2023-10-16 | 68580 | 2023-10-23 | 69850 | 2023-11-02 | 70495 | 2022-11-16 | 41000 | 2023-11-02 | 70495 | the same milegage C & D col. - correct data | 0 | 850 | 1270 | 645 | 850 | 1915 | ||
| 9 | 4 | 2022-11-16 | 41000 | 2022-11-16 | 41000 | 2022-11-16 | 41000 | 2023-10-16 | 68580 | 2023-10-23 | 69850 | 2023-11-02 | 70495 | 2022-11-16 | 41000 | 2023-11-02 | 70495 | the same milegage C & D& E col. - correct data | 0 | 0 | 1270 | 645 | 0 | 1915 | ||
| 10 | 5 | 2022-11-18 | 163216 | 2022-12-15 | 164165 | 2022-12-15 | 164165 | 2023-10-04 | 170991 | 2023-10-18 | 171450 | 2023-11-10 | 171950 | 2022-11-18 | 163216 | 2023-11-10 | 171950 | the same milegage D& E col. - correct data | 949 | 0 | 459 | 500 | 949 | 959 | ||
| 11 | 6 | 2022-11-16 | 40600 | 2022-11-16 | 41210 | 2022-12-06 | 41850 | 2023-10-23 | 69850 | 2023-10-23 | 69850 | 2023-11-02 | 70495 | 2022-11-16 | 40600 | 2023-11-02 | 70495 | the same milegage F & G col. - correct data | 610 | 640 | 0 | 645 | 1250 | 645 | ||
| 12 | 7 | 2022-11-16 | 40600 | 2022-11-16 | 41210 | 2022-12-06 | 41850 | 2023-10-23 | 69850 | 2023-10-23 | 69850 | 2023-10-23 | 69850 | 2022-11-16 | 40600 | 2023-10-23 | 69850 | the same milegage F & G & H col. - correct data | 610 | 640 | 0 | 0 | 1250 | 0 | ||
| 13 | 8 | 2022-11-18 | 163216 | 2022-12-15 | 164165 | 2022-12-15 | 164751 | 2023-10-04 | 170991 | 2023-11-10 | 171450 | 2023-11-10 | 171450 | 2022-11-18 | 163216 | 2023-11-10 | 171450 | the same milegage G & H col. - correct data | 949 | 586 | 459 | 0 | 1535 | 459 | ||
| 14 | 9 | 2022-11-25 | 112 | 2023-04-18 | 2663 | 2023-06-13 | 3130 | 2023-04-18 | 2663 | 2023-06-13 | 3130 | 2023-09-28 | 3720 | 2023-04-18 | 2663 | 2023-09-28 | 3720 | user mistace, col C, value too low | 2551 | 467 | 467 | 590 | 3018 | 1057 | ||
| 15 | 10 | 2022-11-25 | 14099 | 2023-04-18 | 2663 | 2023-06-13 | 3130 | 2023-04-18 | 2663 | 2023-06-13 | 3130 | 2023-09-28 | 3720 | 2023-04-18 | 2663 | 2023-09-28 | 3720 | user mistace, col C, value too high | -11436 | 467 | 467 | 590 | -10969 | 1057 | ||
| 16 | 11 | 2022-11-15 | 177640 | 2022-11-22 | 28280 | 2022-11-29 | 179013 | 2023-10-04 | 195566 | 2023-10-20 | 195822 | 2023-10-20 | 196330 | 2022-11-15 | 177640 | 2023-10-20 | 196330 | user mistace, col D, value too low | -149360 | 150733 | 256 | 508 | 1373 | 764 | ||
| 17 | 12 | 2022-11-15 | 177640 | 2022-11-22 | 728280 | 2022-11-29 | 179013 | 2023-10-04 | 195566 | 2023-10-20 | 195822 | 2023-10-20 | 196330 | 2022-11-15 | 177640 | 2023-10-20 | 196330 | user mistace, col D, value too high | 550640 | -549267 | 256 | 508 | 1373 | 764 | ||
| 18 | 13 | 2022-11-23 | 171197 | 2022-12-14 | 171881 | 2023-01-02 | 12391 | 2023-09-15 | 179629 | 2023-10-10 | 180398 | 2023-10-26 | 181275 | 2022-11-23 | 171197 | 2023-10-26 | 181275 | user mistace, col E, value too low | 684 | -159490 | 769 | 877 | -158806 | 1646 | ||
| 19 | 14 | 2022-11-23 | 171197 | 2022-12-14 | 171881 | 2023-01-02 | 192391 | 2023-09-15 | 179629 | 2023-10-10 | 180398 | 2023-10-26 | 181275 | 2022-11-23 | 171197 | 2023-10-26 | 181275 | user mistace, col E, value too high | 684 | 20510 | 769 | 877 | 21194 | 1646 | ||
| 20 | 15 | 2022-11-15 | 66676 | 2022-11-22 | 67112 | 2022-11-25 | 67433 | 2023-10-20 | 993707 | 2023-10-26 | 94240 | 2023-11-06 | 94751 | 2022-11-15 | 66676 | 2023-11-06 | 94751 | user mistace, col F, value too low | 436 | 321 | -899467 | 511 | 757 | -898956 | ||
| 21 | 16 | 2022-11-15 | 66676 | 2022-11-22 | 67112 | 2022-11-25 | 67433 | 2023-10-20 | 13707 | 2023-10-26 | 94240 | 2023-11-06 | 94751 | 2022-11-15 | 66676 | 2023-11-06 | 94751 | user mistace, col F, value too high | 436 | 321 | 80533 | 511 | 757 | 81044 | ||
| 22 | 17 | 2022-11-19 | 244505 | 2022-12-12 | 244958 | 2023-03-01 | 245521 | 2023-07-04 | 247027 | 2023-08-18 | 147758 | 2023-10-05 | 248353 | 2022-11-19 | 244505 | 2023-10-05 | 248353 | user mistace, col G, value too low | 453 | 563 | -99269 | 100595 | 1016 | 1326 | ||
| 23 | 18 | 2022-11-19 | 244505 | 2022-12-12 | 244958 | 2023-03-01 | 245521 | 2023-07-04 | 247027 | 2023-08-18 | 347758 | 2023-10-05 | 248353 | 2022-11-19 | 244505 | 2023-10-05 | 248353 | user mistace, col G, value too high | 453 | 563 | 100731 | -99405 | 1016 | 1326 | ||
| 24 | 19 | 2022-11-21 | 26810 | 2022-11-21 | 27219 | 2022-12-08 | 27988 | 2023-09-06 | 40583 | 2023-09-29 | 41281 | 2023-10-24 | 4243 | 2022-11-21 | 26810 | 2023-09-29 | 41281 | user mistace, col H, value too low | 409 | 769 | 698 | -37038 | 1178 | -36340 | ||
| 25 | 20 | 2022-11-21 | 26810 | 2022-11-21 | 27219 | 2022-12-08 | 27988 | 2023-09-06 | 40583 | 2023-09-29 | 41281 | 2023-10-24 | 444243 | 2022-11-21 | 26810 | 2023-09-29 | 41281 | user mistace, col H, value too high | 409 | 769 | 698 | 402962 | 1178 | 403660 | ||
| 26 | 21 | 2022-11-21 | 27219 | 2022-11-21 | 27219 | 2022-12-08 | 27988 | 2023-09-06 | 40583 | 2023-09-29 | 41281 | 2023-10-24 | 444243 | 2022-11-21 | 27219 | 2023-09-29 | 41281 | user mistace & the same milegage can case at the same time | 0 | 769 | 698 | 402962 | 769 | 403660 | ||
| 27 | 22 | 2022-11-15 | 177640 | 2022-11-22 | 28280 | 2022-11-29 | 179013 | 2023-10-04 | 195566 | 2023-10-20 | 195822 | 2023-10-20 | 195822 | 2022-11-15 | 177640 | 2023-10-20 | 195822 | user mistace & the same milegage can case at the same time | -149360 | 150733 | 256 | 0 | 1373 | 256 | ||
| 28 | ||||||||||||||||||||||||||
| 29 | Legend: | |||||||||||||||||||||||||
| 30 | 144998 | - correct data | ||||||||||||||||||||||||
| 31 | 171450 | 171450 | - the same milegage - correct data | |||||||||||||||||||||||
| 32 | 728280 | - user mistake, car mileage entered incorrectly | ||||||||||||||||||||||||
Arkusz1 | ||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| N6:O13,N16:O27 | N6 | =B6 |
| P6:Q23,P27:Q27 | P6 | =L6 |
| N14:O15 | N14 | =D14 |
| P24:Q26 | P24 | =J24 |
| S6:S27 | S6 | =E6-C6 |
| T6:T27 | T6 | =G6-E6 |
| U6:U27 | U6 | =K6-I6 |
| V6:V27 | V6 | =M6-K6 |
| W6:W27 | W6 | =G6-C6 |
| X6:X27 | X6 | =M6-I6 |
