From: sbain on
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
>=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
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
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
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