IF STATEMENT
in [Functions]
|
From: sbain on 21 May 2010 18:39 I have an if statement that says =if(ISBLANK(C3:F3),ROUNDUP(B3,0),b3+.01) First off, the statement does not seem to evaluate the "roundup" part, meaning that the statement is backwards. What I am trying to accomplish is that if c3:f3 are blank, then I want the number in b3 to be added to .01. If there is a character in c3:f3, then I want the number in b3 to be rounded up to the next whole number and the answer reflected in g3. If I were to do the formula as is, and left the row blank then it would add ..01 to b3, but if later I wanted to change the outcome and put a character into c3:f3, shouldn't the answer automatically round b3 to the next whole number or would I have to re-write the formula. -- Sierra
From: T. Valko on 21 May 2010 21:48 >=if(ISBLANK(C3:F3),ROUNDUP(B3,0),b3+.01) It depends upon what your intentions are. The ISBLANK function is referencing an ARRAY of cells but it will only execute based on the first cell of the referenced range, C3. Also, if you didn't array enter the formula as written then ISBLANK will *always* be FALSE causing the IF to return B3+0.01. So, you need to clarify what you want WRT to ISBLANK(C3:F3). Do you want to test that *every* cell is blank or do you want to test that *any* cell is blank? -- Biff Microsoft Excel MVP "sbain" <sbain(a)discussions.microsoft.com> wrote in message news:8D7D1837-F04E-4FEF-A823-2123F9D32EBE(a)microsoft.com... >I have an if statement that says =if(ISBLANK(C3:F3),ROUNDUP(B3,0),b3+.01) > > First off, the statement does not seem to evaluate the "roundup" part, > meaning that the statement is backwards. What I am trying to accomplish > is > that if c3:f3 are blank, then I want the number in b3 to be added to .01. > If > there is a character in c3:f3, then I want the number in b3 to be rounded > up > to the next whole number and the answer reflected in g3. > > If I were to do the formula as is, and left the row blank then it would > add > .01 to b3, but if later I wanted to change the outcome and put a character > into c3:f3, shouldn't the answer automatically round b3 to the next whole > number or would I have to re-write the formula. > -- > Sierra
From: sbain on 23 May 2010 22:35 What I am trying to accomplish is that if c3:f3 are blank, then I want the number in b3 to be added to .01. If there is a character in c3:f3, then I want the number in b3 to be rounded up to the next whole number and the answer reflected in g3. I want it to determine that if *any* cell (c3:f3) has a character in it, then b3 needs to be rounded up by a whole #. -- Sierra "sbain" wrote: > I have an if statement that says =if(ISBLANK(C3:F3),ROUNDUP(B3,0),b3+.01) > > First off, the statement does not seem to evaluate the "roundup" part, > meaning that the statement is backwards. What I am trying to accomplish is > that if c3:f3 are blank, then I want the number in b3 to be added to .01. If > there is a character in c3:f3, then I want the number in b3 to be rounded up > to the next whole number and the answer reflected in g3. > > If I were to do the formula as is, and left the row blank then it would add > .01 to b3, but if later I wanted to change the outcome and put a character > into c3:f3, shouldn't the answer automatically round b3 to the next whole > number or would I have to re-write the formula. > -- > Sierra
From: sbain on 23 May 2010 22:41 I want to test that *every* cell is blank in that row and then do b3+.01, and if *every* cell in that row has any character in it then b3 needs to be rounded to the nearest whole number. -- Sierra "T. Valko" wrote: > >=if(ISBLANK(C3:F3),ROUNDUP(B3,0),b3+.01) > > It depends upon what your intentions are. The ISBLANK function is > referencing an ARRAY of cells but it will only execute based on the first > cell of the referenced range, C3. Also, if you didn't array enter the > formula as written then ISBLANK will *always* be FALSE causing the IF to > return B3+0.01. > > So, you need to clarify what you want WRT to ISBLANK(C3:F3). Do you want to > test that *every* cell is blank or do you want to test that *any* cell is > blank? > > -- > Biff > Microsoft Excel MVP > > > "sbain" <sbain(a)discussions.microsoft.com> wrote in message > news:8D7D1837-F04E-4FEF-A823-2123F9D32EBE(a)microsoft.com... > >I have an if statement that says =if(ISBLANK(C3:F3),ROUNDUP(B3,0),b3+.01) > > > > First off, the statement does not seem to evaluate the "roundup" part, > > meaning that the statement is backwards. What I am trying to accomplish > > is > > that if c3:f3 are blank, then I want the number in b3 to be added to .01. > > If > > there is a character in c3:f3, then I want the number in b3 to be rounded > > up > > to the next whole number and the answer reflected in g3. > > > > If I were to do the formula as is, and left the row blank then it would > > add > > .01 to b3, but if later I wanted to change the outcome and put a character > > into c3:f3, shouldn't the answer automatically round b3 to the next whole > > number or would I have to re-write the formula. > > -- > > Sierra > > > . >
From: Rick Rothstein on 24 May 2010 01:49 Does this do what you want? =IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01) -- Rick (MVP - Excel) "sbain" <sbain(a)discussions.microsoft.com> wrote in message news:757F32EC-784F-488A-9B1D-51FF83A18B77(a)microsoft.com... > What I am trying to accomplish is > that if c3:f3 are blank, then I want the number in b3 to be added to .01. > If > there is a character in c3:f3, then I want the number in b3 to be rounded > up > to the next whole number and the answer reflected in g3. > > I want it to determine that if *any* cell (c3:f3) has a character in it, > then b3 needs to be rounded up by a whole #. > > > -- > Sierra > > > "sbain" wrote: > >> I have an if statement that says =if(ISBLANK(C3:F3),ROUNDUP(B3,0),b3+.01) >> >> First off, the statement does not seem to evaluate the "roundup" part, >> meaning that the statement is backwards. What I am trying to accomplish >> is >> that if c3:f3 are blank, then I want the number in b3 to be added to .01. >> If >> there is a character in c3:f3, then I want the number in b3 to be rounded >> up >> to the next whole number and the answer reflected in g3. >> >> If I were to do the formula as is, and left the row blank then it would >> add >> .01 to b3, but if later I wanted to change the outcome and put a >> character >> into c3:f3, shouldn't the answer automatically round b3 to the next whole >> number or would I have to re-write the formula. >> -- >> Sierra
|
Next
|
Last
Pages: 1 2 3 4 Prev: How to sum by month Next: Average a column containing numbers and text |