Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Error?
Hi,
I'm really new to excel and am having a hard time trying to complete an assignment for my teacher. Could anyone tell me what is wrong with this formula? =IF(AND(ISBLANK(F6)),(ISBLANK(G6)),"", (H6+G6-F6)) What I want to do is in a simple accounting worksheet calculate the data in two individual cells to complete a running balance in a third cell, but if the first two are empty, I want the third cell to be blank, too. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Error?
"Jackson" wrote:
Could anyone tell me what is wrong with this formula? =IF(AND(ISBLANK(F6)),(ISBLANK(G6)),"", (H6+G6-F6)) Duh, you tell us! ;-) If you are referring to the syntax error, the problem is some extraneous parentheses. You probably want: =IF(AND(ISBLANK(F6),ISBLANK(G6)),"", (H6+G6-F6)) However, you also have some unnecessary parentheses. And I would recommend that you test for "blank" as follows: =IF(AND(F6="",G6""),"",H6+G6-F6) ISBLANK() is true only if the cell is truly empty; that is, no constant and no formula. But suppose F6 or G6 has a formula like the above, which might return the null string (""). Then ISBLANK() returns false even though the cell appears to be blank. F6="" is true when the cell is truly empty __and__ when the cell contains the null string. It's a "good practice" even if you are sure it will not contain the null string. (There are times when you want to make a distinction between truly empty and containing the null string. That is when you want to use the ISBLANK function.) Jackson wrote: What I want to do is in a simple accounting worksheet calculate the data in two individual cells to complete a running balance in a third cell, but if the first two are empty, I want the third cell to be blank, too. If this formula goes into H6, I'll bet you want: =IF(AND(F6="",G6""),"",H5+G6-F6) That does assume that H5, G6 and F6 do not return the null string. Probably a reasonable assumption. But if they might, the following avoids any problem: =IF(AND(F6="",G6""),"",SUM(H5,G6)-N(F6)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Error?
joeu2004 wrote:
"Jackson" wrote: Could anyone tell me what is wrong with this formula? =IF(AND(ISBLANK(F6)),(ISBLANK(G6)),"", (H6+G6-F6)) Duh, you tell us! ;-) If you are referring to the syntax error, the problem is some extraneous parentheses. You probably want: =IF(AND(ISBLANK(F6),ISBLANK(G6)),"", (H6+G6-F6)) However, you also have some unnecessary parentheses. And I would recommend that you test for "blank" as follows: =IF(AND(F6="",G6""),"",H6+G6-F6) ISBLANK() is true only if the cell is truly empty; that is, no constant and no formula. But suppose F6 or G6 has a formula like the above, which might return the null string (""). Then ISBLANK() returns false even though the cell appears to be blank. F6="" is true when the cell is truly empty __and__ when the cell contains the null string. It's a "good practice" even if you are sure it will not contain the null string. (There are times when you want to make a distinction between truly empty and containing the null string. That is when you want to use the ISBLANK function.) Jackson wrote: What I want to do is in a simple accounting worksheet calculate the data in two individual cells to complete a running balance in a third cell, but if the first two are empty, I want the third cell to be blank, too. If this formula goes into H6, I'll bet you want: =IF(AND(F6="",G6""),"",H5+G6-F6) That does assume that H5, G6 and F6 do not return the null string. Probably a reasonable assumption. But if they might, the following avoids any problem: =IF(AND(F6="",G6""),"",SUM(H5,G6)-N(F6)) Thank you, joeu! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Error?
On Feb 18, 8:16*pm, "joeu2004" wrote:
"Jackson" wrote: Could anyone tell me what is wrong with this formula? =IF(AND(ISBLANK(F6)),(ISBLANK(G6)),"", (H6+G6-F6)) Duh, you tell us! ;-) If you are referring to the syntax error, the problem is some extraneous parentheses. You probably want: =IF(AND(ISBLANK(F6),ISBLANK(G6)),"", (H6+G6-F6)) However, you also have some unnecessary parentheses. *And I would recommend that you test for "blank" as follows: =IF(AND(F6="",G6""),"",H6+G6-F6) ISBLANK() is true only if the cell is truly empty; that is, no constant and no formula. But suppose F6 or G6 has a formula like the above, which might return the null string (""). *Then ISBLANK() returns false even though the cell appears to be blank. F6="" is true when the cell is truly empty __and__ when the cell contains the null string. *It's a "good practice" even if you are sure it will not contain the null string. (There are times when you want to make a distinction between truly empty and containing the null string. *That is when you want to use the ISBLANK function.) Jackson wrote: What I want to do is in a simple accounting worksheet calculate the data in two individual cells to complete a running balance in a third cell, but if the first two are empty, I want the third cell to be blank, too. If this formula goes into H6, I'll bet you want: =IF(AND(F6="",G6""),"",H5+G6-F6) That does assume that H5, G6 and F6 do not return the null string. *Probably a reasonable assumption. *But if they might, the following avoids any problem: =IF(AND(F6="",G6""),"",SUM(H5,G6)-N(F6)) Are we doing homework now? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#value! error in sum function | Excel Worksheet Functions | |||
#value! error in sum function | Excel Worksheet Functions | |||
Function Error | Excel Discussion (Misc queries) | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Function Error | Excel Programming |