A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

IF STATEMENT



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 11:39 PM posted to microsoft.public.excel.worksheet.functions
sbain
external usenet poster
 
Posts: 9
Default IF STATEMENT

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
  #2  
Old May 22nd, 2010, 02:48 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default IF STATEMENT

=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" wrote in message
...
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



  #3  
Old May 24th, 2010, 03:35 AM posted to microsoft.public.excel.worksheet.functions
sbain
external usenet poster
 
Posts: 9
Default IF STATEMENT

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

  #4  
Old May 24th, 2010, 03:41 AM posted to microsoft.public.excel.worksheet.functions
sbain
external usenet poster
 
Posts: 9
Default IF STATEMENT

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" wrote in message
...
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



.

  #5  
Old May 24th, 2010, 06:49 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default IF STATEMENT

Does this do what you want?

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)

--
Rick (MVP - Excel)



"sbain" wrote in message
...
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


  #6  
Old May 24th, 2010, 03:28 PM posted to microsoft.public.excel.worksheet.functions
sbain
external usenet poster
 
Posts: 9
Default IF STATEMENT

I'm afraid not. If you put that statement in, it still doesn't auto update
if you decide to put a character into fields c3:f3. Thank you though.
--
Sierra


"Rick Rothstein" wrote:

Does this do what you want?

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)

--
Rick (MVP - Excel)



"sbain" wrote in message
...
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


.

  #7  
Old May 24th, 2010, 03:35 PM posted to microsoft.public.excel.worksheet.functions
sbain
external usenet poster
 
Posts: 9
Default IF STATEMENT

My mistake Rick, it did work. Thank you soo much. Question though: what does
the "*" represent? Does it represent "every" cell? Thank you again.
--
Sierra


"Rick Rothstein" wrote:

Does this do what you want?

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)

--
Rick (MVP - Excel)



"sbain" wrote in message
...
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


.

  #8  
Old May 24th, 2010, 03:49 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default IF STATEMENT

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)
what does the "*" represent?


The * (asterisk) is a wildcard that means " any TEXT ".


--
Biff
Microsoft Excel MVP


"sbain" wrote in message
...
My mistake Rick, it did work. Thank you soo much. Question though: what
does
the "*" represent? Does it represent "every" cell? Thank you again.
--
Sierra


"Rick Rothstein" wrote:

Does this do what you want?

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)

--
Rick (MVP - Excel)



"sbain" wrote in message
...
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


.



  #9  
Old May 24th, 2010, 07:12 PM posted to microsoft.public.excel.worksheet.functions
sbain
external usenet poster
 
Posts: 9
Default IF STATEMENT

Ok. Now I have the following problem:
The following numbers are in column B:

B3 2.0
B4 1.1
B5 1.02

My formula is =IF(COUNTIF(B9:G9,"*"),ROUNDUP(B9,0),B9+0.1), because I want
the number in B3 & B4 to be 1.2 but for B5 I want it to be 1.03.

How do I change the formula or add on to it to reflect numbers that have an
extra space?
--
Sierra


"T. Valko" wrote:

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)
what does the "*" represent?


The * (asterisk) is a wildcard that means " any TEXT ".


--
Biff
Microsoft Excel MVP


"sbain" wrote in message
...
My mistake Rick, it did work. Thank you soo much. Question though: what
does
the "*" represent? Does it represent "every" cell? Thank you again.
--
Sierra


"Rick Rothstein" wrote:

Does this do what you want?

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)

--
Rick (MVP - Excel)



"sbain" wrote in message
...
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

.



.

  #10  
Old May 25th, 2010, 01:26 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default IF STATEMENT

Ok, I'm not following you on this at all!

Your formula refers to cells on row 9 but then you ask about cells B3, B4
and B5.

Also, you refer to B9 in the COUNTIF and the criteria is "any text" but then
in the IF function you do math calculations on cell B9.

?????

--
Biff
Microsoft Excel MVP


"sbain" wrote in message
...
Ok. Now I have the following problem:
The following numbers are in column B:

B3 2.0
B4 1.1
B5 1.02

My formula is =IF(COUNTIF(B9:G9,"*"),ROUNDUP(B9,0),B9+0.1), because I want
the number in B3 & B4 to be 1.2 but for B5 I want it to be 1.03.

How do I change the formula or add on to it to reflect numbers that have
an
extra space?
--
Sierra


"T. Valko" wrote:

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)
what does the "*" represent?


The * (asterisk) is a wildcard that means " any TEXT ".


--
Biff
Microsoft Excel MVP


"sbain" wrote in message
...
My mistake Rick, it did work. Thank you soo much. Question though:
what
does
the "*" represent? Does it represent "every" cell? Thank you again.
--
Sierra


"Rick Rothstein" wrote:

Does this do what you want?

=IF(COUNTIF(C3:F3,"*"),ROUNDUP(B3,0),B3+0.01)

--
Rick (MVP - Excel)



"sbain" wrote in message
...
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

.



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.