check if any values on row
in [Functions]
|
From: Norvascom on 28 Feb 2010 15:37 Hi, I am trying to have a formula on cell A1 for example that would look on cells D1:CT1 to see if there are any values different than 0 (in other words not empty or equal to 0). If there are any values different than 0, cell A1 would indicate "Row with data" if none, it would say "Empty row". Any help would be appreciated. Thanks
From: Lars-�ke Aspelin on 28 Feb 2010 16:01 On Sun, 28 Feb 2010 12:37:37 -0800 (PST), Norvascom <norvascom(a)gmail.com> wrote: >Hi, > >I am trying to have a formula on cell A1 for example that would look >on cells D1:CT1 to see if there are any values different than 0 (in >other words not empty or equal to 0). >If there are any values different than 0, cell A1 would indicate "Row >with data" if none, it would say "Empty row". > >Any help would be appreciated. >Thanks Try this formula: =IF(OR(D1:CT1<>0),"Row with data","Empty row") Note: This is an array formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-�ke
From: Norvascom on 28 Feb 2010 18:55 On Feb 28, 4:01 pm, Lars-Åke Aspelin <lar...(a)REMOOVEtelia.com> wrote: > On Sun, 28 Feb 2010 12:37:37 -0800 (PST), Norvascom > > <norvas...(a)gmail.com> wrote: > >Hi, > > >I am trying to have a formula on cell A1 for example that would look > >on cells D1:CT1 to see if there are any values different than 0 (in > >other words not empty or equal to 0). > >If there are any values different than 0, cell A1 would indicate "Row > >with data" if none, it would say "Empty row". > > >Any help would be appreciated. > >Thanks > > Try this formula: > > =IF(OR(D1:CT1<>0),"Row with data","Empty row") > > Note: This is an array formula that should be confirmed with > CTRL+SHIFT+ENTER rather than just ENTER. > > Hope this helps / Lars-Åke Thanks it works, but would there be any formula that would calculate automatically the result without having to press CTRL+SHIFT+ENTER.
From: Teethless mama on 28 Feb 2010 20:02 Try this: =IF(SUM(D1:CT1),"Row with data","Empty row") "Norvascom" wrote: > On Feb 28, 4:01 pm, Lars-Åke Aspelin <lar...(a)REMOOVEtelia.com> wrote: > > On Sun, 28 Feb 2010 12:37:37 -0800 (PST), Norvascom > > > > <norvas...(a)gmail.com> wrote: > > >Hi, > > > > >I am trying to have a formula on cell A1 for example that would look > > >on cells D1:CT1 to see if there are any values different than 0 (in > > >other words not empty or equal to 0). > > >If there are any values different than 0, cell A1 would indicate "Row > > >with data" if none, it would say "Empty row". > > > > >Any help would be appreciated. > > >Thanks > > > > Try this formula: > > > > =IF(OR(D1:CT1<>0),"Row with data","Empty row") > > > > Note: This is an array formula that should be confirmed with > > CTRL+SHIFT+ENTER rather than just ENTER. > > > > Hope this helps / Lars-Åke > > > Thanks it works, but would there be any formula that would calculate > automatically the result without having to press CTRL+SHIFT+ENTER. > . >
From: Norvascom on 28 Feb 2010 20:46
On Feb 28, 8:02 pm, Teethless mama <Teethlessm...(a)discussions.microsoft.com> wrote: > Try this: > > =IF(SUM(D1:CT1),"Row with data","Empty row") > > > > "Norvascom" wrote: > > On Feb 28, 4:01 pm, Lars-Åke Aspelin <lar...(a)REMOOVEtelia.com> wrote: > > > On Sun, 28 Feb 2010 12:37:37 -0800 (PST), Norvascom > > > > <norvas...(a)gmail.com> wrote: > > > >Hi, > > > > >I am trying to have a formula on cell A1 for example that would look > > > >on cells D1:CT1 to see if there are any values different than 0 (in > > > >other words not empty or equal to 0). > > > >If there are any values different than 0, cell A1 would indicate "Row > > > >with data" if none, it would say "Empty row". > > > > >Any help would be appreciated. > > > >Thanks > > > > Try this formula: > > > > =IF(OR(D1:CT1<>0),"Row with data","Empty row") > > > > Note: This is an array formula that should be confirmed with > > > CTRL+SHIFT+ENTER rather than just ENTER. > > > > Hope this helps / Lars-Åke > > > Thanks it works, but would there be any formula that would calculate > > automatically the result without having to press CTRL+SHIFT+ENTER. > > .- Hide quoted text - > > - Show quoted text - The sum formula won't work, because if for instance on one cell there is a +10 and on another a -10 it will indicate "row with no data" while there are values. Please let me know any other formula that would work. |