 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2005Formula Problems
Hi can anyone tell me please, why this formula: =ROUND(7/(C3-D3),5) gives an incorrect answer, when I extend it to: =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,C3,""),"") the answer should be, 604799.85862 and is when I use the first small formula. It becomes 36891.00001 thanks, Ted. > Hi can anyone tell me please, why this formula: > [quoted text clipped - 10 lines] > > Ted. Going to ask Wizard of Oz for contents of C3, D3... Bruno If I understand correctly, try =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),"")) The formula you posted just displays the contents of C3 if all conditions are met, Regards, Alan. > Hi can anyone tell me please, why this formula: > [quoted text clipped - 10 lines] > > Ted. that worked grate Alan, thanks $;-D I was helped with the formula earlier but couldnt see what was going wrong - much appriciated. Ted. > If I understand correctly, try > =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),"")) [quoted text clipped - 16 lines] > > > > Ted. Alan, I have just noticed that it returns the ERROR message if there is no data in the C3,D3 (etc) cells that the sums are based on - part of the formula is to allow further calculations to not be effected by zeros appering in cells and stuff like that. something that may be affecting is the C3-D3 cells contain dates - he subtraction is one date from another. Any ideas please?? > If I understand correctly, try > =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),"")) [quoted text clipped - 16 lines] > > > > Ted. perhaps if the zero in "C3-D3<>0" was altered to something that symbolises a blank space (such as the "") it may cure it?? is there a symbol that means 'blank cell'? Ted > If I understand correctly, try > =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),"")) [quoted text clipped - 16 lines] > > > > Ted. >Hi can anyone tell me please, why this formula: > [quoted text clipped - 10 lines] > >Ted. Your formula, in words says: If there are numbers in C3 and D3; and if C3-D3 is not zero, then if (your_small_formula) is greater than 0.5 output the value in C3 else output a null string else output a null string. The only values that you are outputting is either the contents of C3, or a null string. Your long formula never outputs the value of the formula. Try this (untested) to get the same result as your_small_formula if your tests are passed: =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),""),"") or, slightly shorter: =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3<>D3),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),""),"") --ron [...] > =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),""),"") > > or, slightly shorter: > > =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3<>D3),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),""),"") Or, a couple of byte shorter: =IF(NOT(ISERROR(R3*S3))*(R3<>S3)*(NOT(ISERROR(ROUND(7/(R3-S3),5)))),ROUND(7/(R3-S3),5),"") Bruno >[...] >> =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),""),"") [quoted text clipped - 8 lines] > >Bruno Actually, as is, your formula has more functions than mine. In addition, it will return values less than or equal to 0.5, so you need another IF statement. --ron Hi Ron, Bruno, and others(?) I am now using the formula: =IF(AND(C3-D3>0,COUNT(C3:D3)=2),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),""),"") If either of you spot a problem with that selection, please let me know?? It appears to be working ok, but welcome suggestions? Many thanks, Ted. > >[...] > >> =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<>0),IF(ROUND(7/(C3-D3),5)>0.5,ROUND(7/(C3-D3),5),""),"") [quoted text clipped - 15 lines] > > --ron >Hi Ron, Bruno, and others(?) I am now using the formula: > [quoted text clipped - 5 lines] > >Many thanks, Ted. Your formula will give a #VALUE error if there happens to be text in C3 or D3. If this is not desirable behavior, then change: C3-D3>0 to C3=D3 --ron > Actually, as is, your formula has more functions than mine. > > In addition, it will return values less than or equal to 0.5, so you need > another IF statement. > > --ron Hi Ron, =IF(ISERROR(ROUND(7/(C3-D3),5)*C3*D3),"", IF((ROUND(7/(C3-D3),5)>0.5),ROUND(7/(C3-D3),5),"")) definitely I was unable to miss 2nd IF clause. Ciao Bruno where does the additional IF need to go!?? also, how can I get sums to perform as normal, even when it is confronted by empty cells? I realise that if I am computing, say, A1-B1 and A2-B2 and there is no data in one of them (e.g. A2-B2) then I will get a #VALUE! error message because of no data etc. How can I get around this please, and have the answer cell display a zero for the result, when there is no data to compute E.G. << using the fake data of 5-6=1 and 0-3=#VALUE! >> from: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = #VALUE! to: A1[5] - B1[6] = C1[1] A2[empty cell] - B2[3] = 0 I cant have zeros appear in the dependant cells because it alters other formula, so just neet it to class a blank/empty cell as a zero. Any ideas please?? Ted. > > Actually, as is, your formula has more functions than mine. > > [quoted text clipped - 12 lines] > Ciao > Bruno [...] > from: > A1[5] - B1[6] = C1[1] [quoted text clipped - 3 lines] > A1[5] - B1[6] = C1[1] > A2[empty cell] - B2[3] = 0 =IF(ISERROR(R3*S3),0,R3+S3) =IF(ISERROR(R3*S3),0,R3-S3) =IF(ISERROR(R3*S3),0,R3*S3) =IF(ISERROR(R3*S3),0,R3/S3) In the 4th formula S3 should also be checked for value #0. Bruno |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.