Ganesh Vijaykumar
asked on
Updating status based on set criteria
I am trying to compare "ES NEG POS Date" with "PV DETAILS THIS WK-S Date" and "ES NEG POS Amount" with "PV DETAILS THIS WK-S Amount" and update the status("Fixed" or "Unfixed") based on the below mentioned criteria.
Criteria based on Date
ES NEG POS Date PV DETAILS THIS WK-S Date Status
20-Nov-14 20-Nov-14 Fixed
20-Nov-14 5-Nov-14 Fixed
20-Nov-14 25-Nov-14 Unfixed
Criteria based on Amount
ES NEG POS Amount PV DETAILS THIS WK-S Amount Status
50.00 50.00 Fixed
50.00 40.00 Fixed
-50.00 -40.00 Fixed
0.00 50.00 Unfixed
0.00 -50.00 Unfixed
50.00 90.00 Unfixed
-50.00 -90.00 Unfixed
-50.00 30.00 Unfixed
30.00 -50.00 Unfixed
Status after considering date and amount together
Status based on Date Status based on Amount Final status
Fixed Fixed Fixed
Fixed Unfixed Unfixed
Unfixed Fixed Unfixed
Fixed Unfixed Unfixed
One of my team members is doing this manually for at least 10 sheets. I tried to write “If/And/OR” logic as mentioned below, but was not successful completely. I am finding it difficult because of negative (-) number. Please help
For Date: IF(OR(E2=S2,E2=R2,E2<S2,E2 <R2),"Fixe d","Unfixe d")
For Amount: =IF(T2=0,"Unfixed",IF(M2=T 2,"Fixed", IF(AND(T2< 0,M2>0),"U nFixed",IF (AND(M2<0, T2>0),"UnF ixed",IF(A ND(T2<0,M2 <0,T2<M2), "Fixed",IF (AND(T2<0, M2<0,M2<T2 ),"UnFixed ",IF(T2>M2 ,"Fixed",I F(M2>T2,"U nFixed","" ))))))))
Hence curious to know if we can write a VB script for accurate results or any other better method I can use to get the right status.
Any suggestions would be greatly appreciated!
I am looking forward for the immediate and positive response!!!!!!
Thanks,
Ganesh
Criteria based on Date
ES NEG POS Date PV DETAILS THIS WK-S Date Status
20-Nov-14 20-Nov-14 Fixed
20-Nov-14 5-Nov-14 Fixed
20-Nov-14 25-Nov-14 Unfixed
Criteria based on Amount
ES NEG POS Amount PV DETAILS THIS WK-S Amount Status
50.00 50.00 Fixed
50.00 40.00 Fixed
-50.00 -40.00 Fixed
0.00 50.00 Unfixed
0.00 -50.00 Unfixed
50.00 90.00 Unfixed
-50.00 -90.00 Unfixed
-50.00 30.00 Unfixed
30.00 -50.00 Unfixed
Status after considering date and amount together
Status based on Date Status based on Amount Final status
Fixed Fixed Fixed
Fixed Unfixed Unfixed
Unfixed Fixed Unfixed
Fixed Unfixed Unfixed
One of my team members is doing this manually for at least 10 sheets. I tried to write “If/And/OR” logic as mentioned below, but was not successful completely. I am finding it difficult because of negative (-) number. Please help
For Date: IF(OR(E2=S2,E2=R2,E2<S2,E2
For Amount: =IF(T2=0,"Unfixed",IF(M2=T
Hence curious to know if we can write a VB script for accurate results or any other better method I can use to get the right status.
Any suggestions would be greatly appreciated!
I am looking forward for the immediate and positive response!!!!!!
Thanks,
Ganesh
Yes. Please post the actual rules to follow, as well as a sample file showing several inputs and the output expected based on that input.
ASKER
loneieagle2: Oops I am sorry for the confusion. Actually I am trying to compare dates under column PV DETAILS THIS WK-S Date with dates under column ES NEG POS Date and ES OSB NEG POS Date, hence you can see E2, R2 and S2 in the formula. While posting the question thought if I get the logic\condition for one, I can apply the same for another one.
However, to put this question differently:
Let us focus on comparing only two dates and amounts. I mean ES NEG POS Date with PV DETAILS THIS WK-S Date and ES NEG POS Amount with PV DETAILS THIS WK-S Amount.
Firstly we have to check the dates and then later amount and Update the final output
Let:
ES NEG POS Date be ED
ES NEG POS Amount be EA
PV DETAILS THIS WK-S Date be PVD
PV DETAILS THIS WK-S Amount be PVA
Example:
ED=PVD = Fixed EA=PVA=Fixed Final output = Fixed
ED=PVD = Fixed EA>PVA=Unfixed Final output = Unfixed
ED>PVD = Unfixed EA=PVA=Fixed Final Output = Unfixed
I could write the logic, but when its only negative combination and negative positive combination i am not getting the accurate results.
I have updated all the possible criteria’s in the attached spreadsheet. Hopefully this time I am clear on my request.
Will be looking for positive response!
Thanks,
G
Criteria-s.xlsx
However, to put this question differently:
Let us focus on comparing only two dates and amounts. I mean ES NEG POS Date with PV DETAILS THIS WK-S Date and ES NEG POS Amount with PV DETAILS THIS WK-S Amount.
Firstly we have to check the dates and then later amount and Update the final output
Let:
ES NEG POS Date be ED
ES NEG POS Amount be EA
PV DETAILS THIS WK-S Date be PVD
PV DETAILS THIS WK-S Amount be PVA
Example:
ED=PVD = Fixed EA=PVA=Fixed Final output = Fixed
ED=PVD = Fixed EA>PVA=Unfixed Final output = Unfixed
ED>PVD = Unfixed EA=PVA=Fixed Final Output = Unfixed
I could write the logic, but when its only negative combination and negative positive combination i am not getting the accurate results.
I have updated all the possible criteria’s in the attached spreadsheet. Hopefully this time I am clear on my request.
Will be looking for positive response!
Thanks,
G
Criteria-s.xlsx
Still not clear on the rules. I'll just use ES and PV to name the columns. Are these correct?
1. Date: Unfixed if PV>ES, otherwise Fixed
2. Amount: see excel sheet
3. Combination: If both 1 and 2 are fixed then the final is fixed, otherwise unfixed.
If you clear up the ambiguities on the spreadsheet, I can get you and answer.
Criteria-s.xlsx
1. Date: Unfixed if PV>ES, otherwise Fixed
2. Amount: see excel sheet
3. Combination: If both 1 and 2 are fixed then the final is fixed, otherwise unfixed.
If you clear up the ambiguities on the spreadsheet, I can get you and answer.
Criteria-s.xlsx
ASKER
Thanks for your response!
Using ES and PV to name the columns is correct
1. Date: Unfixed if PV>ES, otherwise Fixed = Correct
3. Combination: If both 1 and 2 are fixed then the final is fixed, otherwise unfixed. = Correct
Amount: see updated excel sheet, I have updated the comments and status.
I hope i have cleared the ambiguities on the spreadsheet.
Will be looking for positive response at the earliest possible as i need to complete this ASAP
Thanks,
G
Copy-of-Criteria-s.xlsx
Using ES and PV to name the columns is correct
1. Date: Unfixed if PV>ES, otherwise Fixed = Correct
3. Combination: If both 1 and 2 are fixed then the final is fixed, otherwise unfixed. = Correct
Amount: see updated excel sheet, I have updated the comments and status.
I hope i have cleared the ambiguities on the spreadsheet.
Will be looking for positive response at the earliest possible as i need to complete this ASAP
Thanks,
G
Copy-of-Criteria-s.xlsx
ASKER
@loneieagle2 : I am awaiting your response! Hope you are clear on the rules now, please let me know if you need any further information.
Thanks,
G
Thanks,
G
ASKER
@loneieagle2 : Just wanna inform you, I had to complete this task urgently, hence was trying out of my ways to write the condition/Logic and I think I am successful and logic is working fine. Below is the logic or condition I used, However it would be of great help if you could validate the below mentioned formula and also lemme know if you have a better logic/condition to get the accurate output Or any other way (VB) to get the accurate results or long term solution.
For Date : E2 = ES ; R2 = PV
=IF(E2>R2),"Unfixed","Fixe d")
For Amount : T2 = ES ; M2 = PV
=IF(T2=0,"Unfixed",IF(T2>= M2,"Fixed" ,IF(T2<M2, "Unfixed", IF(AND(IF( AND(SIGN(T 2)=-1,SIGN (M2)=-1)," Neg","")=" Neg",T2<M2 ),"Fixed", IF(AND(IF( AND(SIGN(T 2)=-1,SIGN (M2)=-1)," Neg","")=" Neg",T2>M2 ),"Unfixed ",IF(AND(I F(AND(SIGN (T2)=-1,SI GN(M2)=-1) ,"Neg","") ="Neg",T2= M2),"Fixed ",IF(AND(S IGN(T2)=-1 ,SIGN(M2)= 1),"Unfixe d",IF(AND( SIGN(T2)=1 ,SIGN(M2)= -1),"Unfix ed","")))) ))))
Will be awaiting your response!
Thanks,
G
For Date : E2 = ES ; R2 = PV
=IF(E2>R2),"Unfixed","Fixe
For Amount : T2 = ES ; M2 = PV
=IF(T2=0,"Unfixed",IF(T2>=
Will be awaiting your response!
Thanks,
G
How about:
=IF(OR(E2>R2,T2=0,SIGN(T2) <>SIGN(M2) ,ABS(T2)>= ABS(M2),"U nfixed","F ixed")
I think this gets everything
=IF(OR(E2>R2,T2=0,SIGN(T2)
I think this gets everything
=IF(OR(E2>R2,T2=0,SIGN(T2) <>SIGN(M2) ,ABS(T2)>= ABS(M2))," Unfixed"," Fixed")
Missed a parenthesis.
Missed a parenthesis.
=IF(OR(E2>R2,T2=0,SIGN(T2) <>SIGN(M2) ,ABS(T2)<A BS(M2)),"U nfixed","F ixed")
Sorry, had the ABS comparison backwards. This should do it.
Since the combination of date and amount both have to be fixed, either one being Unfixed makes the result Unfixed. So we just need to find all occurrences that make it unfixed.
1. First date greater than the 2nd date
2. First amount 0
3. Different signs
4. The magnitude of the first number less than the magnitude of the second.
Or we could have found Fixed by using AND with:
1. First date less than or equal to 2nd date.
2. First amount <>0
3. Same Sign
4. The magnitude of the first number greater than or equal to the second.
Hope this helped.
Sorry, had the ABS comparison backwards. This should do it.
Since the combination of date and amount both have to be fixed, either one being Unfixed makes the result Unfixed. So we just need to find all occurrences that make it unfixed.
1. First date greater than the 2nd date
2. First amount 0
3. Different signs
4. The magnitude of the first number less than the magnitude of the second.
Or we could have found Fixed by using AND with:
1. First date less than or equal to 2nd date.
2. First amount <>0
3. Same Sign
4. The magnitude of the first number greater than or equal to the second.
Hope this helped.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the delayed response! The formula worked for me... Thanks for your help!
What determines Fixed and Unfixed by amount?
I think we need a lot more explanation of what you are trying to accomplish.