Help with IF formula Jim 05.07.2006 15:39:02 Hi everyone. Someone on here always has an answer for my question, so thanks in advance. I am running XP Pro SP2, and Office Pro 2003. Column A is for quantity, and the quantity can be positive or negative. Column H is for individual pricing, and Column I is for the price extension (i.e. A*H). Column I is blank unless there is a number to be displayed. I have the following formula set up in I4 now: =IF(A4>0,A4*H4,"") which works OK if A4 is a positive number. What should the formula be so it also works with a negative number? Rember that if A4 is blank, then I4 is also blank. In other words, I need an operator that will only work if A4 is not blank. Thanks for your help. -- Jim

Re: Help with IF formula "msnews.microsoft.com" <ardus.petus[ at ]laposte.net> 05.07.2006 15:49:14 Try: =IF(A4="","",A4*H4) Is this too obvoius? -- AP "Jim" <Jim[ at ]discussions.microsoft.com> a écrit dans le message de news: B533DB78-4BA5-45BB-8953-82D1902724CC[ at ]microsoft.com... [Quoted Text] > Hi everyone. Someone on here always has an answer for my question, so > thanks > in advance. > > I am running XP Pro SP2, and Office Pro 2003. > > Column A is for quantity, and the quantity can be positive or negative. > > Column H is for individual pricing, and Column I is for the price > extension > (i.e. A*H). > > Column I is blank unless there is a number to be displayed. > > I have the following formula set up in I4 now: > > =IF(A4>0,A4*H4,"") > > which works OK if A4 is a positive number. What should the formula be so > it > also works with a negative number? Rember that if A4 is blank, then I4 is > also blank. > > In other words, I need an operator that will only work if A4 is not blank. > > Thanks for your help. > -- > Jim

Re: Help with IF formula SteveW <sj_walton[ at ]nothotmail.com> 05.07.2006 15:51:40 try =3Dif(a4<>"",a4*h4,"") Steve On Wed, 05 Jul 2006 16:39:02 +0100, Jim <Jim[ at ]discussions.microsoft.com> = = wrote: [Quoted Text] > Hi everyone. Someone on here always has an answer for my question, so = = > thanks > in advance. > > I am running XP Pro SP2, and Office Pro 2003. > > Column A is for quantity, and the quantity can be positive or negative= .. > > Column H is for individual pricing, and Column I is for the price = > extension > (i.e. A*H). > > Column I is blank unless there is a number to be displayed. > > I have the following formula set up in I4 now: > > =3DIF(A4>0,A4*H4,"") > > which works OK if A4 is a positive number. What should the formula be = so = > it > also works with a negative number? Rember that if A4 is blank, then I4= is > also blank. > > In other words, I need an operator that will only work if A4 is not = > blank. > > Thanks for your help.

Re: Help with IF formula mattjenkins <mattjenkins.2ahbxp_1152114911.0578[ at ]excelforum-nospam.com> 05.07.2006 15:53:06 how about =IF(A4="","",A4*H4) ? -- mattjenkins ------------------------------------------------------------------------ mattjenkins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36049 View this thread: http://www.excelforum.com/showthread.php?threadid=558492

Re: Help with IF formula "flummi" <hansyt[ at ]gmx.de> 05.07.2006 15:55:32 Hi, =IF(ISNUMBER(A4);A4*H4);"") Hans Jim schrieb: [Quoted Text] > Hi everyone. Someone on here always has an answer for my question, so thanks > in advance. > > I am running XP Pro SP2, and Office Pro 2003. > > Column A is for quantity, and the quantity can be positive or negative. > > Column H is for individual pricing, and Column I is for the price extension > (i.e. A*H). > > Column I is blank unless there is a number to be displayed. > > I have the following formula set up in I4 now: > > =IF(A4>0,A4*H4,"") > > which works OK if A4 is a positive number. What should the formula be so it > also works with a negative number? Rember that if A4 is blank, then I4 is > also blank. > > In other words, I need an operator that will only work if A4 is not blank. > > Thanks for your help. > -- > Jim

Re: Help with IF formula "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 06.07.2006 16:46:27 Hi Jim In addition to the other replies you have received, you could just use the formula without any IF() statement, and use Tools>Options>General>untick Zero values to prevent anything displayed if the result is 0 -- Regards Roger Govier "Jim" <Jim[ at ]discussions.microsoft.com> wrote in message news:B533DB78-4BA5-45BB-8953-82D1902724CC[ at ]microsoft.com... [Quoted Text] > Hi everyone. Someone on here always has an answer for my question, so > thanks > in advance. > > I am running XP Pro SP2, and Office Pro 2003. > > Column A is for quantity, and the quantity can be positive or > negative. > > Column H is for individual pricing, and Column I is for the price > extension > (i.e. A*H). > > Column I is blank unless there is a number to be displayed. > > I have the following formula set up in I4 now: > > =IF(A4>0,A4*H4,"") > > which works OK if A4 is a positive number. What should the formula be > so it > also works with a negative number? Rember that if A4 is blank, then I4 > is > also blank. > > In other words, I need an operator that will only work if A4 is not > blank. > > Thanks for your help. > -- > Jim

Re: Help with IF formula SteveW <sj_walton[ at ]nothotmail.com> 07.07.2006 16:09:41 On Thu, 06 Jul 2006 17:46:27 +0100, Roger Govier = <roger[ at ]technologyNOSPAM4u.co.uk> wrote: [Quoted Text] > Hi Jim > > In addition to the other replies you have received, you could just use= > the formula without any IF() statement, and use > Tools>Options>General>untick Zero values > to prevent anything displayed if the result is 0 > Too general, imo, but custom format of #;-#; changes 0 to nothing for = specific cells -- = Steve (3)