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

W

Wojciech

New Member
Joined
May 13, 2018
Messages
26
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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

D
  • Solved
IF, IF(And(countif)) formula not working correctly
Replies
5
Views
352
SanjayGMusafir
SanjayGMusafir
J
  • Solved
Help with calculation between work days based on condition
Replies
7
Views
262
Fluff
Fluff
K
  • Question
conditional formatting question
Replies
2
Views
161
kerm007
K
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
314
awoohaw
awoohaw
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
320
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,077
Messages
6,122,995
Members
449,094
Latest member
masterms

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