 |
 |
|
|
|
|
Welcome to the { mindfrost82.com } forums. You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join our community today! If you have any problems with the registration process or your account login, please contact contact us. |
|
|||||||
 |
|
|
LinkBack | Thread Tools | Search this Thread | Display Modes |
11-26-2007, 05:12 PM
|
|||
|
|||
|
Counting negative values
Hi All,
I'd be very grateful if someone could help me with this. I have a following table: No, Company,Var1 12, ZXC, 6 12, ZXC, 4 12, ZXC, -3 34, AAC, 2 34, AAC, 4 56, BBR, -5 78, CCV, -4 78, CCV, -12 What I need is another column with some variable that would turn e.g. 1 when company has at least one row with negative value of Var1 and 0 if not. In other words, i'd like to get: No, Company,Var1, Var2 12, ZXC, 6, 1 12, ZXC, 4, 1 12, ZXC, -3, 1 34, AAC, 2, 0 34, AAC, 4, 0 56, BBR, -5, 1 78, CCV, -4, 1 78, CCV, -12, 1 I have to add that I have about 9000 rows and i'm using excel 2003. Thanks Maciek 
|
|
11-26-2007, 05:39 PM
|
|||
|
|||
|
Re: Counting negative values
If that's the case why do you get 1 for the first 2 values in your example?
=-1*(C2<0) where C2 would be the first Var1 copy down -- Regards, Peo Sjoblom "Maciej Grzywna" <relever@wp.pl> wrote in message news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... > Hi All, > > I'd be very grateful if someone could help me with this. > I have a following table: > > No, Company,Var1 > 12, ZXC, 6 > 12, ZXC, 4 > 12, ZXC, -3 > 34, AAC, 2 > 34, AAC, 4 > 56, BBR, -5 > 78, CCV, -4 > 78, CCV, -12 > > What I need is another column with some variable that would turn e.g. 1 > when company has at least one row with negative value of Var1 and 0 if > not. In other words, i'd like to get: > > No, Company,Var1, Var2 > 12, ZXC, 6, 1 > 12, ZXC, 4, 1 > 12, ZXC, -3, 1 > 34, AAC, 2, 0 > 34, AAC, 4, 0 > 56, BBR, -5, 1 > 78, CCV, -4, 1 > 78, CCV, -12, 1 > > I have to add that I have about 9000 rows and i'm using excel 2003. > > Thanks > Maciek >
|
|
11-26-2007, 06:25 PM
|
|||
|
|||
|
Re: Counting negative values
Hi Peo,
in my example first 3 values concern the same company, and that's the case. When at least one value "within" the company is negative then I want all three rows to have 1 in the fourth column. I hope that will help. Thanks Maciek "Peo Sjoblom" <terre08@mvps.org> wrote in message news:%23gM2ZNFMIHA.4308@TK2MSFTNGP05.phx.gbl... > If that's the case why do you get 1 for the first 2 values in your > example? > > =-1*(C2<0) > > where C2 would be the first Var1 > > copy down > > > -- > > > Regards, > > > Peo Sjoblom > > > > "Maciej Grzywna" <relever@wp.pl> wrote in message > news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... >> Hi All, >> >> I'd be very grateful if someone could help me with this. >> I have a following table: >> >> No, Company,Var1 >> 12, ZXC, 6 >> 12, ZXC, 4 >> 12, ZXC, -3 >> 34, AAC, 2 >> 34, AAC, 4 >> 56, BBR, -5 >> 78, CCV, -4 >> 78, CCV, -12 >> >> What I need is another column with some variable that would turn e.g. 1 >> when company has at least one row with negative value of Var1 and 0 if >> not. In other words, i'd like to get: >> >> No, Company,Var1, Var2 >> 12, ZXC, 6, 1 >> 12, ZXC, 4, 1 >> 12, ZXC, -3, 1 >> 34, AAC, 2, 0 >> 34, AAC, 4, 0 >> 56, BBR, -5, 1 >> 78, CCV, -4, 1 >> 78, CCV, -12, 1 >> >> I have to add that I have about 9000 rows and i'm using excel 2003. >> >> Thanks >> Maciek >> > > >
|
|
11-26-2007, 08:01 PM
|
|||
|
|||
|
Re: Counting negative values
Give this formula a try...
=--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0) Rick "Maciej Grzywna" <relever@wp.pl> wrote in message news:OH7q2mFMIHA.4228@TK2MSFTNGP02.phx.gbl... > Hi Peo, > > in my example first 3 values concern the same company, and that's the > case. When at least one value "within" the company is negative then I want > all three rows to have 1 in the fourth column. > I hope that will help. > > Thanks > Maciek > > "Peo Sjoblom" <terre08@mvps.org> wrote in message > news:%23gM2ZNFMIHA.4308@TK2MSFTNGP05.phx.gbl... >> If that's the case why do you get 1 for the first 2 values in your >> example? >> >> =-1*(C2<0) >> >> where C2 would be the first Var1 >> >> copy down >> >> >> -- >> >> >> Regards, >> >> >> Peo Sjoblom >> >> >> >> "Maciej Grzywna" <relever@wp.pl> wrote in message >> news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... >>> Hi All, >>> >>> I'd be very grateful if someone could help me with this. >>> I have a following table: >>> >>> No, Company,Var1 >>> 12, ZXC, 6 >>> 12, ZXC, 4 >>> 12, ZXC, -3 >>> 34, AAC, 2 >>> 34, AAC, 4 >>> 56, BBR, -5 >>> 78, CCV, -4 >>> 78, CCV, -12 >>> >>> What I need is another column with some variable that would turn e.g. 1 >>> when company has at least one row with negative value of Var1 and 0 if >>> not. In other words, i'd like to get: >>> >>> No, Company,Var1, Var2 >>> 12, ZXC, 6, 1 >>> 12, ZXC, 4, 1 >>> 12, ZXC, -3, 1 >>> 34, AAC, 2, 0 >>> 34, AAC, 4, 0 >>> 56, BBR, -5, 1 >>> 78, CCV, -4, 1 >>> 78, CCV, -12, 1 >>> >>> I have to add that I have about 9000 rows and i'm using excel 2003. >>> >>> Thanks >>> Maciek >>> >> >> >> > >
|
|
11-26-2007, 08:14 PM
|
|||
|
|||
|
Re: Counting negative values
Actually, use this equation instead (it suppresses the 0 if there is no
number in column C)... =IF(C1="","",--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0)) And, of course, you can change the ranges as required by your data (but note that you can copy the formula down passed your last piece of data to account for future entries). Rick "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in message news:O6UkHcGMIHA.4880@TK2MSFTNGP03.phx.gbl... > Give this formula a try... > > =--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0) > > Rick > > > "Maciej Grzywna" <relever@wp.pl> wrote in message > news:OH7q2mFMIHA.4228@TK2MSFTNGP02.phx.gbl... >> Hi Peo, >> >> in my example first 3 values concern the same company, and that's the >> case. When at least one value "within" the company is negative then I >> want all three rows to have 1 in the fourth column. >> I hope that will help. >> >> Thanks >> Maciek >> >> "Peo Sjoblom" <terre08@mvps.org> wrote in message >> news:%23gM2ZNFMIHA.4308@TK2MSFTNGP05.phx.gbl... >>> If that's the case why do you get 1 for the first 2 values in your >>> example? >>> >>> =-1*(C2<0) >>> >>> where C2 would be the first Var1 >>> >>> copy down >>> >>> >>> -- >>> >>> >>> Regards, >>> >>> >>> Peo Sjoblom >>> >>> >>> >>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>> news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... >>>> Hi All, >>>> >>>> I'd be very grateful if someone could help me with this. >>>> I have a following table: >>>> >>>> No, Company,Var1 >>>> 12, ZXC, 6 >>>> 12, ZXC, 4 >>>> 12, ZXC, -3 >>>> 34, AAC, 2 >>>> 34, AAC, 4 >>>> 56, BBR, -5 >>>> 78, CCV, -4 >>>> 78, CCV, -12 >>>> >>>> What I need is another column with some variable that would turn e.g. 1 >>>> when company has at least one row with negative value of Var1 and 0 if >>>> not. In other words, i'd like to get: >>>> >>>> No, Company,Var1, Var2 >>>> 12, ZXC, 6, 1 >>>> 12, ZXC, 4, 1 >>>> 12, ZXC, -3, 1 >>>> 34, AAC, 2, 0 >>>> 34, AAC, 4, 0 >>>> 56, BBR, -5, 1 >>>> 78, CCV, -4, 1 >>>> 78, CCV, -12, 1 >>>> >>>> I have to add that I have about 9000 rows and i'm using excel 2003. >>>> >>>> Thanks >>>> Maciek >>>> >>> >>> >>> >> >> >
|
|
11-26-2007, 09:00 PM
|
|||
|
|||
|
Re: Counting negative values
Rick
Thank you very much for your help. Maciek "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in message news:eiMlSjGMIHA.1184@TK2MSFTNGP04.phx.gbl... > Actually, use this equation instead (it suppresses the 0 if there is no > number in column C)... > > =IF(C1="","",--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0)) > > And, of course, you can change the ranges as required by your data (but > note that you can copy the formula down passed your last piece of data to > account for future entries). > > Rick > > > "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in > message news:O6UkHcGMIHA.4880@TK2MSFTNGP03.phx.gbl... >> Give this formula a try... >> >> =--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0) >> >> Rick >> >> >> "Maciej Grzywna" <relever@wp.pl> wrote in message >> news:OH7q2mFMIHA.4228@TK2MSFTNGP02.phx.gbl... >>> Hi Peo, >>> >>> in my example first 3 values concern the same company, and that's the >>> case. When at least one value "within" the company is negative then I >>> want all three rows to have 1 in the fourth column. >>> I hope that will help. >>> >>> Thanks >>> Maciek >>> >>> "Peo Sjoblom" <terre08@mvps.org> wrote in message >>> news:%23gM2ZNFMIHA.4308@TK2MSFTNGP05.phx.gbl... >>>> If that's the case why do you get 1 for the first 2 values in your >>>> example? >>>> >>>> =-1*(C2<0) >>>> >>>> where C2 would be the first Var1 >>>> >>>> copy down >>>> >>>> >>>> -- >>>> >>>> >>>> Regards, >>>> >>>> >>>> Peo Sjoblom >>>> >>>> >>>> >>>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>>> news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... >>>>> Hi All, >>>>> >>>>> I'd be very grateful if someone could help me with this. >>>>> I have a following table: >>>>> >>>>> No, Company,Var1 >>>>> 12, ZXC, 6 >>>>> 12, ZXC, 4 >>>>> 12, ZXC, -3 >>>>> 34, AAC, 2 >>>>> 34, AAC, 4 >>>>> 56, BBR, -5 >>>>> 78, CCV, -4 >>>>> 78, CCV, -12 >>>>> >>>>> What I need is another column with some variable that would turn e.g. >>>>> 1 when company has at least one row with negative value of Var1 and 0 >>>>> if not. In other words, i'd like to get: >>>>> >>>>> No, Company,Var1, Var2 >>>>> 12, ZXC, 6, 1 >>>>> 12, ZXC, 4, 1 >>>>> 12, ZXC, -3, 1 >>>>> 34, AAC, 2, 0 >>>>> 34, AAC, 4, 0 >>>>> 56, BBR, -5, 1 >>>>> 78, CCV, -4, 1 >>>>> 78, CCV, -12, 1 >>>>> >>>>> I have to add that I have about 9000 rows and i'm using excel 2003. >>>>> >>>>> Thanks >>>>> Maciek >>>>> >>>> >>>> >>>> >>> >>> >> > >
|
|
11-26-2007, 09:10 PM
|
|||
|
|||
|
Re: Counting negative values
Rick really meant to include the absolutes:
=IF(C1="","",--(SUMPRODUCT(($C$1:$C$100<0)*($B$1:$B$100=B1))>0)) -- Regards, RD ---------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in message news:eiMlSjGMIHA.1184@TK2MSFTNGP04.phx.gbl... > Actually, use this equation instead (it suppresses the 0 if there is no > number in column C)... > > =IF(C1="","",--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0)) > > And, of course, you can change the ranges as required by your data (but > note that you can copy the formula down passed your last piece of data to > account for future entries). > > Rick > > > "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in > message news:O6UkHcGMIHA.4880@TK2MSFTNGP03.phx.gbl... >> Give this formula a try... >> >> =--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0) >> >> Rick >> >> >> "Maciej Grzywna" <relever@wp.pl> wrote in message >> news:OH7q2mFMIHA.4228@TK2MSFTNGP02.phx.gbl... >>> Hi Peo, >>> >>> in my example first 3 values concern the same company, and that's the >>> case. When at least one value "within" the company is negative then I >>> want all three rows to have 1 in the fourth column. >>> I hope that will help. >>> >>> Thanks >>> Maciek >>> >>> "Peo Sjoblom" <terre08@mvps.org> wrote in message >>> news:%23gM2ZNFMIHA.4308@TK2MSFTNGP05.phx.gbl... >>>> If that's the case why do you get 1 for the first 2 values in your >>>> example? >>>> >>>> =-1*(C2<0) >>>> >>>> where C2 would be the first Var1 >>>> >>>> copy down >>>> >>>> >>>> -- >>>> >>>> >>>> Regards, >>>> >>>> >>>> Peo Sjoblom >>>> >>>> >>>> >>>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>>> news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... >>>>> Hi All, >>>>> >>>>> I'd be very grateful if someone could help me with this. >>>>> I have a following table: >>>>> >>>>> No, Company,Var1 >>>>> 12, ZXC, 6 >>>>> 12, ZXC, 4 >>>>> 12, ZXC, -3 >>>>> 34, AAC, 2 >>>>> 34, AAC, 4 >>>>> 56, BBR, -5 >>>>> 78, CCV, -4 >>>>> 78, CCV, -12 >>>>> >>>>> What I need is another column with some variable that would turn e.g. >>>>> 1 when company has at least one row with negative value of Var1 and 0 >>>>> if not. In other words, i'd like to get: >>>>> >>>>> No, Company,Var1, Var2 >>>>> 12, ZXC, 6, 1 >>>>> 12, ZXC, 4, 1 >>>>> 12, ZXC, -3, 1 >>>>> 34, AAC, 2, 0 >>>>> 34, AAC, 4, 0 >>>>> 56, BBR, -5, 1 >>>>> 78, CCV, -4, 1 >>>>> 78, CCV, -12, 1 >>>>> >>>>> I have to add that I have about 9000 rows and i'm using excel 2003. >>>>> >>>>> Thanks >>>>> Maciek >>>>> >>>> >>>> >>>> >>> >>> >> >
|
|
11-26-2007, 10:12 PM
|
|||
|
|||
|
Re: Counting negative values
Yes, you are right... I did forget about that; although the formula would
still work as I posted it provided there were no gaps in the data and the data wasn't more than half the number of cells in a column. Of course, the absolute ranges are more practical. I would point out, however, that the columns do not really need to be absolute as the formulas are being copied down (it doesn't hurt to make them absolute, but it isn't mandatory). Also, because the first cell in the range is being compared to itself, the first row of the range does not really need to be absolute either. Had I realized my mistake in leaving out the absolute ranges, this is how I would have posted the formula... =IF(C1="","",--(SUMPRODUCT((C1:C$100<0)*(B1:B$100=B1))>0)) But, of course, your posted correction would work fine also. With all that said, thank you for catching my omission and alerting me to it... I really appreciate that. Rick "RAGdyer" <RAGdyer@CUTOUTmsn.com> wrote in message news:%23h7O9CHMIHA.4228@TK2MSFTNGP02.phx.gbl... > Rick really meant to include the absolutes: > > =IF(C1="","",--(SUMPRODUCT(($C$1:$C$100<0)*($B$1:$B$100=B1))>0)) > -- > Regards, > > RD > ---------------------------------------------------------------------------------- > Please keep all correspondence within the Group, so all may benefit ! > ---------------------------------------------------------------------------------- > > > "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in > message news:eiMlSjGMIHA.1184@TK2MSFTNGP04.phx.gbl... >> Actually, use this equation instead (it suppresses the 0 if there is no >> number in column C)... >> >> =IF(C1="","",--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0)) >> >> And, of course, you can change the ranges as required by your data (but >> note that you can copy the formula down passed your last piece of data to >> account for future entries). >> >> Rick >> >> >> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in >> message news:O6UkHcGMIHA.4880@TK2MSFTNGP03.phx.gbl... >>> Give this formula a try... >>> >>> =--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0) >>> >>> Rick >>> >>> >>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>> news:OH7q2mFMIHA.4228@TK2MSFTNGP02.phx.gbl... >>>> Hi Peo, >>>> >>>> in my example first 3 values concern the same company, and that's the >>>> case. When at least one value "within" the company is negative then I >>>> want all three rows to have 1 in the fourth column. >>>> I hope that will help. >>>> >>>> Thanks >>>> Maciek >>>> >>>> "Peo Sjoblom" <terre08@mvps.org> wrote in message >>>> news:%23gM2ZNFMIHA.4308@TK2MSFTNGP05.phx.gbl... >>>>> If that's the case why do you get 1 for the first 2 values in your >>>>> example? >>>>> >>>>> =-1*(C2<0) >>>>> >>>>> where C2 would be the first Var1 >>>>> >>>>> copy down >>>>> >>>>> >>>>> -- >>>>> >>>>> >>>>> Regards, >>>>> >>>>> >>>>> Peo Sjoblom >>>>> >>>>> >>>>> >>>>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>>>> news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... >>>>>> Hi All, >>>>>> >>>>>> I'd be very grateful if someone could help me with this. >>>>>> I have a following table: >>>>>> >>>>>> No, Company,Var1 >>>>>> 12, ZXC, 6 >>>>>> 12, ZXC, 4 >>>>>> 12, ZXC, -3 >>>>>> 34, AAC, 2 >>>>>> 34, AAC, 4 >>>>>> 56, BBR, -5 >>>>>> 78, CCV, -4 >>>>>> 78, CCV, -12 >>>>>> >>>>>> What I need is another column with some variable that would turn e.g. >>>>>> 1 when company has at least one row with negative value of Var1 and 0 >>>>>> if not. In other words, i'd like to get: >>>>>> >>>>>> No, Company,Var1, Var2 >>>>>> 12, ZXC, 6, 1 >>>>>> 12, ZXC, 4, 1 >>>>>> 12, ZXC, -3, 1 >>>>>> 34, AAC, 2, 0 >>>>>> 34, AAC, 4, 0 >>>>>> 56, BBR, -5, 1 >>>>>> 78, CCV, -4, 1 >>>>>> 78, CCV, -12, 1 >>>>>> >>>>>> I have to add that I have about 9000 rows and i'm using excel 2003. >>>>>> >>>>>> Thanks >>>>>> Maciek >>>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >> > >
|
|
11-27-2007, 01:37 PM
|
|||
|
|||
|
Re: Counting negative values
Ahhh ... Yes ... BUT ... It's so much more easier to simply hit <F4> once in
each relevant reference!<bg> -- Regards, RD ---------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in message news:%23TzhdlHMIHA.820@TK2MSFTNGP06.phx.gbl... > Yes, you are right... I did forget about that; although the formula would > still work as I posted it provided there were no gaps in the data and the > data wasn't more than half the number of cells in a column. Of course, the > absolute ranges are more practical. I would point out, however, that the > columns do not really need to be absolute as the formulas are being copied > down (it doesn't hurt to make them absolute, but it isn't mandatory). > Also, because the first cell in the range is being compared to itself, the > first row of the range does not really need to be absolute either. Had I > realized my mistake in leaving out the absolute ranges, this is how I > would have posted the formula... > > =IF(C1="","",--(SUMPRODUCT((C1:C$100<0)*(B1:B$100=B1))>0)) > > But, of course, your posted correction would work fine also. > > With all that said, thank you for catching my omission and alerting me to > it... I really appreciate that. > > Rick > > > "RAGdyer" <RAGdyer@CUTOUTmsn.com> wrote in message > news:%23h7O9CHMIHA.4228@TK2MSFTNGP02.phx.gbl... >> Rick really meant to include the absolutes: >> >> =IF(C1="","",--(SUMPRODUCT(($C$1:$C$100<0)*($B$1:$B$100=B1))>0)) >> -- >> Regards, >> >> RD >> ---------------------------------------------------------------------------------- >> Please keep all correspondence within the Group, so all may benefit ! >> ---------------------------------------------------------------------------------- >> >> >> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in >> message news:eiMlSjGMIHA.1184@TK2MSFTNGP04.phx.gbl... >>> Actually, use this equation instead (it suppresses the 0 if there is no >>> number in column C)... >>> >>> =IF(C1="","",--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0)) >>> >>> And, of course, you can change the ranges as required by your data (but >>> note that you can copy the formula down passed your last piece of data >>> to account for future entries). >>> >>> Rick >>> >>> >>> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in >>> message news:O6UkHcGMIHA.4880@TK2MSFTNGP03.phx.gbl... >>>> Give this formula a try... >>>> >>>> =--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0) >>>> >>>> Rick >>>> >>>> >>>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>>> news:OH7q2mFMIHA.4228@TK2MSFTNGP02.phx.gbl... >>>>> Hi Peo, >>>>> >>>>> in my example first 3 values concern the same company, and that's the >>>>> case. When at least one value "within" the company is negative then I >>>>> want all three rows to have 1 in the fourth column. >>>>> I hope that will help. >>>>> >>>>> Thanks >>>>> Maciek >>>>> >>>>> "Peo Sjoblom" <terre08@mvps.org> wrote in message >>>>> news:%23gM2ZNFMIHA.4308@TK2MSFTNGP05.phx.gbl... >>>>>> If that's the case why do you get 1 for the first 2 values in your >>>>>> example? >>>>>> >>>>>> =-1*(C2<0) >>>>>> >>>>>> where C2 would be the first Var1 >>>>>> >>>>>> copy down >>>>>> >>>>>> >>>>>> -- >>>>>> >>>>>> >>>>>> Regards, >>>>>> >>>>>> >>>>>> Peo Sjoblom >>>>>> >>>>>> >>>>>> >>>>>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>>>>> news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... >>>>>>> Hi All, >>>>>>> >>>>>>> I'd be very grateful if someone could help me with this. >>>>>>> I have a following table: >>>>>>> >>>>>>> No, Company,Var1 >>>>>>> 12, ZXC, 6 >>>>>>> 12, ZXC, 4 >>>>>>> 12, ZXC, -3 >>>>>>> 34, AAC, 2 >>>>>>> 34, AAC, 4 >>>>>>> 56, BBR, -5 >>>>>>> 78, CCV, -4 >>>>>>> 78, CCV, -12 >>>>>>> >>>>>>> What I need is another column with some variable that would turn >>>>>>> e.g. 1 when company has at least one row with negative value of Var1 >>>>>>> and 0 if not. In other words, i'd like to get: >>>>>>> >>>>>>> No, Company,Var1, Var2 >>>>>>> 12, ZXC, 6, 1 >>>>>>> 12, ZXC, 4, 1 >>>>>>> 12, ZXC, -3, 1 >>>>>>> 34, AAC, 2, 0 >>>>>>> 34, AAC, 4, 0 >>>>>>> 56, BBR, -5, 1 >>>>>>> 78, CCV, -4, 1 >>>>>>> 78, CCV, -12, 1 >>>>>>> >>>>>>> I have to add that I have about 9000 rows and i'm using excel 2003. >>>>>>> >>>>>>> Thanks >>>>>>> Maciek >>>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>> >> >> >
|
|
11-27-2007, 03:56 PM
|
|||
|
|||
|
Re: Counting negative values
Well, if you are going to be using F4 while typing the formula in (rather
than going back to use it to edit the ranges afterwards), then there is no "extra" work involved... simply don't hit F4 for the first cell reference and do so twice on the second cell reference of each range... same two key presses of F4 per range, just saved up for use on one instead of both cell references within it.<vbg> Rick "RAGdyer" <RAGdyer@CUTOUTmsn.com> wrote in message news:eP5UzrPMIHA.5224@TK2MSFTNGP02.phx.gbl... > Ahhh ... Yes ... BUT ... It's so much more easier to simply hit <F4> once > in each relevant reference!<bg> > -- > Regards, > > RD > ---------------------------------------------------------------------------------- > Please keep all correspondence within the Group, so all may benefit ! > ---------------------------------------------------------------------------------- > > "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in > message news:%23TzhdlHMIHA.820@TK2MSFTNGP06.phx.gbl... >> Yes, you are right... I did forget about that; although the formula would >> still work as I posted it provided there were no gaps in the data and the >> data wasn't more than half the number of cells in a column. Of course, >> the absolute ranges are more practical. I would point out, however, that >> the columns do not really need to be absolute as the formulas are being >> copied down (it doesn't hurt to make them absolute, but it isn't >> mandatory). Also, because the first cell in the range is being compared >> to itself, the first row of the range does not really need to be absolute >> either. Had I realized my mistake in leaving out the absolute ranges, >> this is how I would have posted the formula... >> >> =IF(C1="","",--(SUMPRODUCT((C1:C$100<0)*(B1:B$100=B1))>0)) >> >> But, of course, your posted correction would work fine also. >> >> With all that said, thank you for catching my omission and alerting me to >> it... I really appreciate that. >> >> Rick >> >> >> "RAGdyer" <RAGdyer@CUTOUTmsn.com> wrote in message >> news:%23h7O9CHMIHA.4228@TK2MSFTNGP02.phx.gbl... >>> Rick really meant to include the absolutes: >>> >>> =IF(C1="","",--(SUMPRODUCT(($C$1:$C$100<0)*($B$1:$B$100=B1))>0)) >>> -- >>> Regards, >>> >>> RD >>> ---------------------------------------------------------------------------------- >>> Please keep all correspondence within the Group, so all may benefit ! >>> ---------------------------------------------------------------------------------- >>> >>> >>> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in >>> message news:eiMlSjGMIHA.1184@TK2MSFTNGP04.phx.gbl... >>>> Actually, use this equation instead (it suppresses the 0 if there is no >>>> number in column C)... >>>> >>>> =IF(C1="","",--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0)) >>>> >>>> And, of course, you can change the ranges as required by your data (but >>>> note that you can copy the formula down passed your last piece of data >>>> to account for future entries). >>>> >>>> Rick >>>> >>>> >>>> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in >>>> message news:O6UkHcGMIHA.4880@TK2MSFTNGP03.phx.gbl... >>>>> Give this formula a try... >>>>> >>>>> =--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0) >>>>> >>>>> Rick >>>>> >>>>> >>>>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>>>> news:OH7q2mFMIHA.4228@TK2MSFTNGP02.phx.gbl... >>>>>> Hi Peo, >>>>>> >>>>>> in my example first 3 values concern the same company, and that's the >>>>>> case. When at least one value "within" the company is negative then I >>>>>> want all three rows to have 1 in the fourth column. >>>>>> I hope that will help. >>>>>> >>>>>> Thanks >>>>>> Maciek >>>>>> >>>>>> "Peo Sjoblom" <terre08@mvps.org> wrote in message >>>>>> news:%23gM2ZNFMIHA.4308@TK2MSFTNGP05.phx.gbl... >>>>>>> If that's the case why do you get 1 for the first 2 values in your >>>>>>> example? >>>>>>> >>>>>>> =-1*(C2<0) >>>>>>> >>>>>>> where C2 would be the first Var1 >>>>>>> >>>>>>> copy down >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> >>>>>>> Regards, >>>>>>> >>>>>>> >>>>>>> Peo Sjoblom >>>>>>> >>>>>>> >>>>>>> >>>>>>> "Maciej Grzywna" <relever@wp.pl> wrote in message >>>>>>> news:OqX9T%23EMIHA.1184@TK2MSFTNGP04.phx.gbl... >>>>>>>> Hi All, >>>>>>>> >>>>>>>> I'd be very grateful if someone could help me with this. >>>>>>>> I have a following table: >>>>>>>> >>>>>>>> No, Company,Var1 >>>>>>>> 12, ZXC, 6 >>>>>>>> 12, ZXC, 4 >>>>>>>> 12, ZXC, -3 >>>>>>>> 34, AAC, 2 >>>>>>>> 34, AAC, 4 >>>>>>>> 56, BBR, -5 >>>>>>>> 78, CCV, -4 >>>>>>>> 78, CCV, -12 >>>>>>>> >>>>>>>> What I need is another column with some variable that would turn >>>>>>>> e.g. 1 when company has at least one row with negative value of >>>>>>>> Var1 and 0 if not. In other words, i'd like to get: >>>>>>>> >>>>>>>> No, Company,Var1, Var2 >>>>>>>> 12, ZXC, 6, 1 >>>>>>>> 12, ZXC, 4, 1 >>>>>>>> 12, ZXC, -3, 1 >>>>>>>> 34, AAC, 2, 0 >>>>>>>> 34, AAC, 4, 0 >>>>>>>> 56, BBR, -5, 1 >>>>>>>> 78, CCV, -4, 1 >>>>>>>> 78, CCV, -12, 1 >>>>>>>> >>>>>>>> I have to add that I have about 9000 rows and i'm using excel 2003. >>>>>>>> >>>>>>>> Thanks >>>>>>>> Maciek >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>> >>> >> > >
|
|
|
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
