Selection of data in the table that is furthest in time, but correct.

W

Wojciech

New Member
Joined
May 13, 2018
Messages
27
Office Version
  1. 2021
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWX
1I need formulas for columns N,O,P,Q (rows from 6 to 27) - searching for correct, sideways data in the table
2Permissible difference between columns E-C & G-E and K-I & M-K FromFrom01500
3Permissible difference between columns G-C and M-IFromFrom03000
4
5NuberData 1Mileage from a year ago 1Data 2Mileage from a year ago 2Data 3Mileage from a year ago 3Data 4Actual mileage 4Data 5Actual mileage 5Data 6Actual mileage 6Data 1-3 reference to col. C,E,GExpected choice col. C,E,GData 4-6 reference to col. I,K,MExpected choice col. I,K,MDescription 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)
612023-01-13200052023-01-29204802023-02-03207602023-09-01287002023-10-13286662023-10-17290142023-01-13200052023-10-1729014typical, correct data475280-34348755314
722022-11-141449982022-11-301456612022-12-141462792023-06-301497012023-07-191501832023-09-171508202022-11-141449982023-09-17150820typical, correct data66361848263712811119
832022-11-16410002022-11-16410002022-12-08418502023-10-16685802023-10-23698502023-11-02704952022-11-16410002023-11-0270495the same milegage C & D col. - correct data085012706458501915
942022-11-16410002022-11-16410002022-11-16410002023-10-16685802023-10-23698502023-11-02704952022-11-16410002023-11-0270495the same milegage C & D& E col. - correct data00127064501915
1052022-11-181632162022-12-151641652022-12-151641652023-10-041709912023-10-181714502023-11-101719502022-11-181632162023-11-10171950the same milegage D& E col. - correct data9490459500949959
1162022-11-16406002022-11-16412102022-12-06418502023-10-23698502023-10-23698502023-11-02704952022-11-16406002023-11-0270495the same milegage F & G col. - correct data61064006451250645
1272022-11-16406002022-11-16412102022-12-06418502023-10-23698502023-10-23698502023-10-23698502022-11-16406002023-10-2369850the same milegage F & G & H col. - correct data6106400012500
1382022-11-181632162022-12-151641652022-12-151647512023-10-041709912023-11-101714502023-11-101714502022-11-181632162023-11-10171450the same milegage G & H col. - correct data94958645901535459
1492022-11-251122023-04-1826632023-06-1331302023-04-1826632023-06-1331302023-09-2837202023-04-1826632023-09-283720user mistace, col C, value too low255146746759030181057
15102022-11-25140992023-04-1826632023-06-1331302023-04-1826632023-06-1331302023-09-2837202023-04-1826632023-09-283720user mistace, col C, value too high-11436467467590-109691057
16112022-11-151776402022-11-22282802022-11-291790132023-10-041955662023-10-201958222023-10-201963302022-11-151776402023-10-20196330user mistace, col D, value too low-1493601507332565081373764
17122022-11-151776402022-11-227282802022-11-291790132023-10-041955662023-10-201958222023-10-201963302022-11-151776402023-10-20196330user mistace, col D, value too high550640-5492672565081373764
18132022-11-231711972022-12-141718812023-01-02123912023-09-151796292023-10-101803982023-10-261812752022-11-231711972023-10-26181275user mistace, col E, value too low684-159490769877-1588061646
19142022-11-231711972022-12-141718812023-01-021923912023-09-151796292023-10-101803982023-10-261812752022-11-231711972023-10-26181275user mistace, col E, value too high68420510769877211941646
20152022-11-15666762022-11-22671122022-11-25674332023-10-209937072023-10-26942402023-11-06947512022-11-15666762023-11-0694751user mistace, col F, value too low436321-899467511757-898956
21162022-11-15666762022-11-22671122022-11-25674332023-10-20137072023-10-26942402023-11-06947512022-11-15666762023-11-0694751user mistace, col F, value too high4363218053351175781044
22172022-11-192445052022-12-122449582023-03-012455212023-07-042470272023-08-181477582023-10-052483532022-11-192445052023-10-05248353user mistace, col G, value too low453563-9926910059510161326
23182022-11-192445052022-12-122449582023-03-012455212023-07-042470272023-08-183477582023-10-052483532022-11-192445052023-10-05248353user mistace, col G, value too high453563100731-9940510161326
24192022-11-21268102022-11-21272192022-12-08279882023-09-06405832023-09-29412812023-10-2442432022-11-21268102023-09-2941281user mistace, col H, value too low409769698-370381178-36340
25202022-11-21268102022-11-21272192022-12-08279882023-09-06405832023-09-29412812023-10-244442432022-11-21268102023-09-2941281user mistace, col H, value too high4097696984029621178403660
26212022-11-21272192022-11-21272192022-12-08279882023-09-06405832023-09-29412812023-10-244442432022-11-21272192023-09-2941281user mistace & the same milegage can case at the same time0769698402962769403660
27222022-11-151776402022-11-22282802022-11-291790132023-10-041955662023-10-201958222023-10-201958222022-11-151776402023-10-20195822user mistace & the same milegage can case at the same time-14936015073325601373256
28
29Legend:
30144998 - correct data
31171450171450 - the same milegage - correct data
32728280 - user mistake, car mileage entered incorrectly
Arkusz1
Cell Formulas
RangeFormula
N6:O13,N16:O27N6=B6
P6:Q23,P27:Q27P6=L6
N14:O15N14=D14
P24:Q26P24=J24
S6:S27S6=E6-C6
T6:T27T6=G6-E6
U6:U27U6=K6-I6
V6:V27V6=M6-K6
W6:W27W6=G6-C6
X6:X27X6=M6-I6
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Sorry, column R has the wrong description. This is the correct description:

choosing the correct one out of three.xlsx
R
5Description of unusual data (examples)
6typical, correct data
7typical, correct data
8the same milegage C & E col. - correct data
9the same milegage C & E & G col. - correct data
10the same milegage E & G col. - correct data
11the same milegage I & K col. - correct data
12the same milegage I & K & M col. - correct data
13the same milegage K & M col. - correct data
14user mistace, col C, value too low
15user mistace, col C, value too high
16user mistace, col E, value too low
17user mistace, col E, value too high
18user mistace, col G, value too low
19user mistace, col G, value too high
20user mistace, col I, value too low
21user mistace, col I, value too high
22user mistace, col K, value too low
23user mistace, col K, value too high
24user mistace, col M, value too low
25user mistace, col M, value too high
26user mistace & the same milegage can case at the same time
27user mistace & the same milegage can case at the same time
Arkusz1
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
486
Asbestos_Jen
Asbestos_Jen
S
  • Solved
Count of active employees between dates.
Replies
3
Views
666
Fluff
Fluff
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
450
Peter_SSs
Peter_SSs
M
  • Solved
Formula Help.
Replies
3
Views
437
Fluff
Fluff
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
566
Alex Blakenburg
A

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top