Dear friends,
I would like to write a simple formula (File as attached).
IF someone enters a value at the reading column, then we will get a deviation value yes?
If the deviation falls within the range of the absolute value, then it will be a Pass, otherwise, it will be a Fail.
Can I know if someone knows how to write the formula?
Thanks for your help in advance!
how and why?then we will get a deviation value yes?
For first example:
Deviation: 0.004, Absolute 0.0005 = Pass ?
What is significance of "Relative"
Hi,
Sorry I think I missed out a few columns in the excel earlier. I have revised the excel sheet by entering the test load column.
the condition remains the same, let say a value is entered at the reading column and we get a deviation ya? If deviation falls within range of the absolute value,
then we get a Pass, if not will be a Fail.
Thanks for your help.
Last edited by joeyjee; 04-29-2018 at 10:18 AM.
Try this formula in H5:
=IF(E5="","Fail",IF(ABS(E5)<=G5,"Pass","Fail"))
then copy down.
Hope this helps.
Pete
Thanks Pete, was trying out the =IF(E6<=G6,"Pass","Fail") function and realize that it shows Pass even if the Reading is empty.
Your formula works! Thanks for the last minutes rescue! Appreciate that!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi there, sure, will do!
Dear All, I have re-open this thread as I have faced some errors when preparing the excel sheet. I am not sure if I am allow to do so or not, please let me know if it is not allow in the forum.
Anyway, I notice that the highlighted red one (attached in excel) always show 'fail' even though it is Pass result. And I am unable to troubleshoot to find the root cause.
Can anyone help to solve this mystery? thanks for your help!!
Regards
Joey
You have a floating point in D4 that introduces an "error". Its floating point value is actually 0.0500000000000007 and not the expected 0.05.
Try
=ROUND(IF(ISNUMBER(C4),ABS(C4-B4)," "),2)
or go a little large than 2 if the precision warrants.
Dave
Thanks for your explanation, Guru! This make sense to me.
But seems like the formula does not work.
By the way, how can I identify if there is a floating error 0.050000000000007 from the D4?
Is there a way to determine the error? Or how to look at it?
Hey Guru, sorry for I have understand you wrongly! The round off should be made at the Deviation cell instead of the Pass/Fail cell.
it works now! anyway, I am still interested how you know it is due to the floating error? any way to identify?
if you try =D4=F4
it will give result as "FALSE", Means D4 and F4 are not equal, so there may be a floating error
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Do what nflsales suggests and to get more specific RE the actual value: With the relevant cell active ... for example D4 ... click in the formula bar and hit the F9 function key. It will show the result of calculation(s) without the "masking" effect of formatting.Originally Posted by joeyjee
it works now! anyway, I am still interested how you know it is due to the floating error? any way to identify?
Unfortunately floating point can occur beyond the visible 15 decimals. nflsales' test may return TRUE in those cases, but functions like MATCH, VLOOKUP, HLOOKUP will not find exact matches. They will "see" the difference.
Floating point is a frequent issue and the only defense is appropriately placed rounding.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks