|
Help to Modify these guys
I need these three functions which work well to not display any thing or ""
if there is no data to calculate against.: ========== =IF(AND(H18=4.5),0,H18-$I$2) =IF(AND(H3<=5.5),0,H3-$J$2) =IF(AND(I3=0,J3=0),"Pass","Fail") ========== Can some one show me how to modify these three functions to not show any value if there is no data in the refferenced fields? If there is, HOW? |
Hi Jay
do you mean this :- =IF(ISBLANK(H18),"",IF(H18=4.5,0,H18-$I$2)) or =IF(H18="","",IF(H18=4.5,0,H18-$I$2)) basically use the same method for the other two. (in the first & second formula you posted the AND is redundant) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jay" wrote in message ... I need these three functions which work well to not display any thing or "" if there is no data to calculate against.: ========== =IF(AND(H18=4.5),0,H18-$I$2) =IF(AND(H3<=5.5),0,H3-$J$2) =IF(AND(I3=0,J3=0),"Pass","Fail") ========== Can some one show me how to modify these three functions to not show any value if there is no data in the refferenced fields? If there is, HOW? |
Julie:
Seems like that should work. Now I need these guys to the same: =IF(AND(I19=0,J19=0),"Pass","Fail") If there is no refferenced cell data, show nothing or "" only. How would one accomplish that? Thanks, "JulieD" wrote: Hi Jay do you mean this :- =IF(ISBLANK(H18),"",IF(H18=4.5,0,H18-$I$2)) or =IF(H18="","",IF(H18=4.5,0,H18-$I$2)) basically use the same method for the other two. (in the first & second formula you posted the AND is redundant) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jay" wrote in message ... I need these three functions which work well to not display any thing or "" if there is no data to calculate against.: ========== =IF(AND(H18=4.5),0,H18-$I$2) =IF(AND(H3<=5.5),0,H3-$J$2) =IF(AND(I3=0,J3=0),"Pass","Fail") ========== Can some one show me how to modify these three functions to not show any value if there is no data in the refferenced fields? If there is, HOW? |
Hi Jay
=IF(AND(ISBLANK(I19),ISBLANK(J19)),"",IF(AND(I19=0 ,J19=0),"Pass","Fail")) which will return "" if both are blank if you want "" returned if either one is blank change the AND to an OR -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jay" wrote in message ... Julie: Seems like that should work. Now I need these guys to the same: =IF(AND(I19=0,J19=0),"Pass","Fail") If there is no refferenced cell data, show nothing or "" only. How would one accomplish that? Thanks, "JulieD" wrote: Hi Jay do you mean this :- =IF(ISBLANK(H18),"",IF(H18=4.5,0,H18-$I$2)) or =IF(H18="","",IF(H18=4.5,0,H18-$I$2)) basically use the same method for the other two. (in the first & second formula you posted the AND is redundant) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jay" wrote in message ... I need these three functions which work well to not display any thing or "" if there is no data to calculate against.: ========== =IF(AND(H18=4.5),0,H18-$I$2) =IF(AND(H3<=5.5),0,H3-$J$2) =IF(AND(I3=0,J3=0),"Pass","Fail") ========== Can some one show me how to modify these three functions to not show any value if there is no data in the refferenced fields? If there is, HOW? |
Julie:
Thanks for that one too. However, if both of the refferenced cell contains "", the formula returns a "Fail". Which is okay, if things were not what they are. If the refferenced cell do contain "" for a value, then, ALL I want to see is a "", and not "FAIL" or "PASS". Just BLANK! Maybe, I did not explain myself clearly enough before. Thanks for ALL your assistance on this! "JulieD" wrote: Hi Jay =IF(AND(ISBLANK(I19),ISBLANK(J19)),"",IF(AND(I19=0 ,J19=0),"Pass","Fail")) which will return "" if both are blank if you want "" returned if either one is blank change the AND to an OR -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jay" wrote in message ... Julie: Seems like that should work. Now I need these guys to the same: =IF(AND(I19=0,J19=0),"Pass","Fail") If there is no refferenced cell data, show nothing or "" only. How would one accomplish that? Thanks, "JulieD" wrote: Hi Jay do you mean this :- =IF(ISBLANK(H18),"",IF(H18=4.5,0,H18-$I$2)) or =IF(H18="","",IF(H18=4.5,0,H18-$I$2)) basically use the same method for the other two. (in the first & second formula you posted the AND is redundant) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jay" wrote in message ... I need these three functions which work well to not display any thing or "" if there is no data to calculate against.: ========== =IF(AND(H18=4.5),0,H18-$I$2) =IF(AND(H3<=5.5),0,H3-$J$2) =IF(AND(I3=0,J3=0),"Pass","Fail") ========== Can some one show me how to modify these three functions to not show any value if there is no data in the refferenced fields? If there is, HOW? |
| All times are GMT +1. The time now is 10:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com