|
How can improve this formula?
Hi,
I have a WB, with 3 WSs in it. Let's call them, A,B,C The numbers in C are given by A - B =IF(ISERROR('A-'B),0,'A-B) The problem is that if I have no data in B then I get A numbers into C How can I add a third condition to the formula so that when no data are present in B then it should show Nothing in C? Thank you! Alex |
Hi
P.e. with formula on row 2 =IF(B2="","",(B2<"")*SUM(A2,-B2)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi, I have a WB, with 3 WSs in it. Let's call them, A,B,C The numbers in C are given by A - B =IF(ISERROR('A-'B),0,'A-B) The problem is that if I have no data in B then I get A numbers into C How can I add a third condition to the formula so that when no data are present in B then it should show Nothing in C? Thank you! Alex |
Hi Arvi,
Could you please explain abit the formula? This is what I have as a real example: =IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to 2003'!G9) I should I change this it? Thank you Alex "Arvi Laanemets" wrote: Hi P.e. with formula on row 2 =IF(B2="","",(B2<"")*SUM(A2,-B2)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi, I have a WB, with 3 WSs in it. Let's call them, A,B,C The numbers in C are given by A - B =IF(ISERROR('A-'B),0,'A-B) The problem is that if I have no data in B then I get A numbers into C How can I add a third condition to the formula so that when no data are present in B then it should show Nothing in C? Thank you! Alex |
Hi
=IF('2003'!G9="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp to 2003'!G9)) (there was an error in previous formula, it had to be '=IF(A2="","",(B2<"")*SUM(A2,-B2))') What does the formula do? IF('2003'!G9="","",...) checks the cell G9 on sheet 2003, and when this is empty, nothing is returned. In case you wanted nothing to be returned with cell G9 on either sheet 2003 or sheet '2005 ...' , the formula would be: =IF(OR('2003'!G9="",'2005 Comp to 2003'!G9=""),"",SUM('2003'!G9,-'2005 Comp to 2003'!G9)) You wanted the formula to return 0, when cell G9 on sheet '2005 ...' is empty. The ('2005 Comp to 2003'!G9<"") part returns TRUE or FALSE - which in Excel are interpreted as 1 or 0 , when multiplied with (or added to) numeric values. So whatever value the SUM returns, when there is no entry in cell G9, this value is multiplied with 0, and 0 is returned. Otherwise the sum is multiplied with 1, and the sum is returned. Now the last part of formula. You used ISERROR to check for empty cells, because mathematical operators don't work with empty cells or with cells containing strings. But SUM function simply ignores such cells. So when cell '2005 Comp to 2003'!G9 is empty (or contains p.e. word "empty"), only cell '2003'!G9 is summed. No errors anymore. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi Arvi, Could you please explain abit the formula? This is what I have as a real example: =IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to 2003'!G9) I should I change this it? Thank you Alex "Arvi Laanemets" wrote: Hi P.e. with formula on row 2 =IF(B2="","",(B2<"")*SUM(A2,-B2)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi, I have a WB, with 3 WSs in it. Let's call them, A,B,C The numbers in C are given by A - B =IF(ISERROR('A-'B),0,'A-B) The problem is that if I have no data in B then I get A numbers into C How can I add a third condition to the formula so that when no data are present in B then it should show Nothing in C? Thank you! Alex |
Arvi,
I tried both the formulas, but they still return the value which is in '2003'!G9, while I would expect nothing (0). Can you re-check it? Thank you Alex "Arvi Laanemets" wrote: Hi =IF('2003'!G9="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp to 2003'!G9)) (there was an error in previous formula, it had to be '=IF(A2="","",(B2<"")*SUM(A2,-B2))') What does the formula do? IF('2003'!G9="","",...) checks the cell G9 on sheet 2003, and when this is empty, nothing is returned. In case you wanted nothing to be returned with cell G9 on either sheet 2003 or sheet '2005 ...' , the formula would be: =IF(OR('2003'!G9="",'2005 Comp to 2003'!G9=""),"",SUM('2003'!G9,-'2005 Comp to 2003'!G9)) You wanted the formula to return 0, when cell G9 on sheet '2005 ...' is empty. The ('2005 Comp to 2003'!G9<"") part returns TRUE or FALSE - which in Excel are interpreted as 1 or 0 , when multiplied with (or added to) numeric values. So whatever value the SUM returns, when there is no entry in cell G9, this value is multiplied with 0, and 0 is returned. Otherwise the sum is multiplied with 1, and the sum is returned. Now the last part of formula. You used ISERROR to check for empty cells, because mathematical operators don't work with empty cells or with cells containing strings. But SUM function simply ignores such cells. So when cell '2005 Comp to 2003'!G9 is empty (or contains p.e. word "empty"), only cell '2003'!G9 is summed. No errors anymore. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi Arvi, Could you please explain abit the formula? This is what I have as a real example: =IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to 2003'!G9) I should I change this it? Thank you Alex "Arvi Laanemets" wrote: Hi P.e. with formula on row 2 =IF(B2="","",(B2<"")*SUM(A2,-B2)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi, I have a WB, with 3 WSs in it. Let's call them, A,B,C The numbers in C are given by A - B =IF(ISERROR('A-'B),0,'A-B) The problem is that if I have no data in B then I get A numbers into C How can I add a third condition to the formula so that when no data are present in B then it should show Nothing in C? Thank you! Alex |
Arvi,
Probably I omitted an important information, '2005 Comp to 2003'!G9 is empty in terms that there is no value in it, but that cell contains a formula which is supposed to return a value in case a number in input in another Sheet. Could this be the reason? Thank you Alex "Metallo" wrote: Arvi, I tried both the formulas, but they still return the value which is in '2003'!G9, while I would expect nothing (0). Can you re-check it? Thank you Alex "Arvi Laanemets" wrote: Hi =IF('2003'!G9="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp to 2003'!G9)) (there was an error in previous formula, it had to be '=IF(A2="","",(B2<"")*SUM(A2,-B2))') What does the formula do? IF('2003'!G9="","",...) checks the cell G9 on sheet 2003, and when this is empty, nothing is returned. In case you wanted nothing to be returned with cell G9 on either sheet 2003 or sheet '2005 ...' , the formula would be: =IF(OR('2003'!G9="",'2005 Comp to 2003'!G9=""),"",SUM('2003'!G9,-'2005 Comp to 2003'!G9)) You wanted the formula to return 0, when cell G9 on sheet '2005 ...' is empty. The ('2005 Comp to 2003'!G9<"") part returns TRUE or FALSE - which in Excel are interpreted as 1 or 0 , when multiplied with (or added to) numeric values. So whatever value the SUM returns, when there is no entry in cell G9, this value is multiplied with 0, and 0 is returned. Otherwise the sum is multiplied with 1, and the sum is returned. Now the last part of formula. You used ISERROR to check for empty cells, because mathematical operators don't work with empty cells or with cells containing strings. But SUM function simply ignores such cells. So when cell '2005 Comp to 2003'!G9 is empty (or contains p.e. word "empty"), only cell '2003'!G9 is summed. No errors anymore. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi Arvi, Could you please explain abit the formula? This is what I have as a real example: =IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to 2003'!G9) I should I change this it? Thank you Alex "Arvi Laanemets" wrote: Hi P.e. with formula on row 2 =IF(B2="","",(B2<"")*SUM(A2,-B2)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi, I have a WB, with 3 WSs in it. Let's call them, A,B,C The numbers in C are given by A - B =IF(ISERROR('A-'B),0,'A-B) The problem is that if I have no data in B then I get A numbers into C How can I add a third condition to the formula so that when no data are present in B then it should show Nothing in C? Thank you! Alex |
Arvi,
It works if I substitute "" with 0 Thanks ALex "Metallo" wrote: Arvi, Probably I omitted an important information, '2005 Comp to 2003'!G9 is empty in terms that there is no value in it, but that cell contains a formula which is supposed to return a value in case a number in input in another Sheet. Could this be the reason? Thank you Alex "Metallo" wrote: Arvi, I tried both the formulas, but they still return the value which is in '2003'!G9, while I would expect nothing (0). Can you re-check it? Thank you Alex "Arvi Laanemets" wrote: Hi =IF('2003'!G9="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp to 2003'!G9)) (there was an error in previous formula, it had to be '=IF(A2="","",(B2<"")*SUM(A2,-B2))') What does the formula do? IF('2003'!G9="","",...) checks the cell G9 on sheet 2003, and when this is empty, nothing is returned. In case you wanted nothing to be returned with cell G9 on either sheet 2003 or sheet '2005 ...' , the formula would be: =IF(OR('2003'!G9="",'2005 Comp to 2003'!G9=""),"",SUM('2003'!G9,-'2005 Comp to 2003'!G9)) You wanted the formula to return 0, when cell G9 on sheet '2005 ...' is empty. The ('2005 Comp to 2003'!G9<"") part returns TRUE or FALSE - which in Excel are interpreted as 1 or 0 , when multiplied with (or added to) numeric values. So whatever value the SUM returns, when there is no entry in cell G9, this value is multiplied with 0, and 0 is returned. Otherwise the sum is multiplied with 1, and the sum is returned. Now the last part of formula. You used ISERROR to check for empty cells, because mathematical operators don't work with empty cells or with cells containing strings. But SUM function simply ignores such cells. So when cell '2005 Comp to 2003'!G9 is empty (or contains p.e. word "empty"), only cell '2003'!G9 is summed. No errors anymore. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi Arvi, Could you please explain abit the formula? This is what I have as a real example: =IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to 2003'!G9) I should I change this it? Thank you Alex "Arvi Laanemets" wrote: Hi P.e. with formula on row 2 =IF(B2="","",(B2<"")*SUM(A2,-B2)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi, I have a WB, with 3 WSs in it. Let's call them, A,B,C The numbers in C are given by A - B =IF(ISERROR('A-'B),0,'A-B) The problem is that if I have no data in B then I get A numbers into C How can I add a third condition to the formula so that when no data are present in B then it should show Nothing in C? Thank you! Alex |
Hi
I tried it again with single sheet version =IF(A2="","",(B2<"")*SUM(A2,-B2)) returns nothing, when in A2 is nothing, or when there is a formula which returns "". But it returns -B2, when there is non-empty string, p.e. " ", or when some formula returns such string. You can check for it in your formula, modifying it as follows: =IF(TRIM('2003'!G9)="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp to 2003'!G9)) When this returns nothing with '2003'!G9 without a visible value, then there is a space in cell/returned by formula. The same with another check - when there is (returned, when there is a formula) anything different from "", the check returns true. When it works after "" is replaced by 0 (in which check?), then maybe your formula returns 0, and you have set not to show zeros (in Tools.Options.View the 'Zero values' control is unchecked). Arvi Laanemets "Metallo" wrote in message ... Arvi, It works if I substitute "" with 0 Thanks ALex "Metallo" wrote: Arvi, Probably I omitted an important information, '2005 Comp to 2003'!G9 is empty in terms that there is no value in it, but that cell contains a formula which is supposed to return a value in case a number in input in another Sheet. Could this be the reason? Thank you Alex "Metallo" wrote: Arvi, I tried both the formulas, but they still return the value which is in '2003'!G9, while I would expect nothing (0). Can you re-check it? Thank you Alex "Arvi Laanemets" wrote: Hi =IF('2003'!G9="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp to 2003'!G9)) (there was an error in previous formula, it had to be '=IF(A2="","",(B2<"")*SUM(A2,-B2))') What does the formula do? IF('2003'!G9="","",...) checks the cell G9 on sheet 2003, and when this is empty, nothing is returned. In case you wanted nothing to be returned with cell G9 on either sheet 2003 or sheet '2005 ...' , the formula would be: =IF(OR('2003'!G9="",'2005 Comp to 2003'!G9=""),"",SUM('2003'!G9,-'2005 Comp to 2003'!G9)) You wanted the formula to return 0, when cell G9 on sheet '2005 ...' is empty. The ('2005 Comp to 2003'!G9<"") part returns TRUE or FALSE - which in Excel are interpreted as 1 or 0 , when multiplied with (or added to) numeric values. So whatever value the SUM returns, when there is no entry in cell G9, this value is multiplied with 0, and 0 is returned. Otherwise the sum is multiplied with 1, and the sum is returned. Now the last part of formula. You used ISERROR to check for empty cells, because mathematical operators don't work with empty cells or with cells containing strings. But SUM function simply ignores such cells. So when cell '2005 Comp to 2003'!G9 is empty (or contains p.e. word "empty"), only cell '2003'!G9 is summed. No errors anymore. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi Arvi, Could you please explain abit the formula? This is what I have as a real example: =IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to 2003'!G9) I should I change this it? Thank you Alex "Arvi Laanemets" wrote: Hi P.e. with formula on row 2 =IF(B2="","",(B2<"")*SUM(A2,-B2)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Metallo" wrote in message ... Hi, I have a WB, with 3 WSs in it. Let's call them, A,B,C The numbers in C are given by A - B =IF(ISERROR('A-'B),0,'A-B) The problem is that if I have no data in B then I get A numbers into C How can I add a third condition to the formula so that when no data are present in B then it should show Nothing in C? Thank you! Alex |
| All times are GMT +1. The time now is 12:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com