Validating Data Consistency
Pyyhkäise näyttääksesi valikon
After cleaning duplicates, fixing formats, and highlighting errors, the final step is to make sure that your data is logically consistent.
Data consistency means that values across different columns make sense when compared to each other. Even if each column looks correct on its own, the relationship between columns can still be wrong.
Let's look at a simple example:
At first glance, everything looks fine. But if you think about it, there is a problem.
If 2 phones cost 800, then 1 phone should not also cost 800. This indicates that one of the values is incorrect.
How to Validate Consistency
The idea is to compare related columns and check if they follow expected rules.
For example, you might expect:
Total = Quantity × Price;- Values to stay within a realistic range;
- Consistent relationships across rows.
You can use simple formulas to verify this logic.
How It Works
You create a formula that checks whether the relationship between columns is correct.
For example:
=IF(B2*C2=D2, "OK", "Error")
This formula checks whether the total matches the calculation. If it does, it returns OK; if not, it flags the row as an Error.
Task
Create a new column called Validation.
Write a formula that calculates the price per item by dividing Total by Quantity. If the price of one item exceeds 2000, mark it as "Error", otherwise mark it as "OK".
Apply the formula to all rows.
Use a formula that divides Total by Quantity and checks if the result is greater than 2000, for example: =IF(E2/D2>2000;"Error";"OK").
Kiitos palautteestasi!
Kysy tekoälyä
Kysy tekoälyä
Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme
Validating Data Consistency
After cleaning duplicates, fixing formats, and highlighting errors, the final step is to make sure that your data is logically consistent.
Data consistency means that values across different columns make sense when compared to each other. Even if each column looks correct on its own, the relationship between columns can still be wrong.
Let's look at a simple example:
At first glance, everything looks fine. But if you think about it, there is a problem.
If 2 phones cost 800, then 1 phone should not also cost 800. This indicates that one of the values is incorrect.
How to Validate Consistency
The idea is to compare related columns and check if they follow expected rules.
For example, you might expect:
Total = Quantity × Price;- Values to stay within a realistic range;
- Consistent relationships across rows.
You can use simple formulas to verify this logic.
How It Works
You create a formula that checks whether the relationship between columns is correct.
For example:
=IF(B2*C2=D2, "OK", "Error")
This formula checks whether the total matches the calculation. If it does, it returns OK; if not, it flags the row as an Error.
Task
Create a new column called Validation.
Write a formula that calculates the price per item by dividing Total by Quantity. If the price of one item exceeds 2000, mark it as "Error", otherwise mark it as "OK".
Apply the formula to all rows.
Use a formula that divides Total by Quantity and checks if the result is greater than 2000, for example: =IF(E2/D2>2000;"Error";"OK").
Kiitos palautteestasi!
