IF STATEMENT

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when there are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.

Steve:
You are correct in assuming that I need to account for numbers that have one
decimal place and two decimal places, but does your formula still do the
basis of what I need, in that if the "if" statement is true it will roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when there are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

Sierra,

what I gave you was only a part of the overall formula, your finished
formula would be:

=IF(COUNTIF(B9:G9,"*"),ROUNDUP(B9,0),
B9+1*10^-(LEN(B9)-FIND(".",B9)))



"sbain" wrote in message
...
Steve:
You are correct in assuming that I need to account for numbers that have
one
decimal place and two decimal places, but does your formula still do the
basis of what I need, in that if the "if" statement is true it will
roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when there
are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

Here is what Steve posted placed inside the IF function call that you
originally asked for....

=IF(COUNTIF(C9:G9,"*"),ROUNDUP(B9,0),B9+10^-(LEN(B9)-FIND(".",B9)))

However, there is one possible problem with this formula... it will error
out if the value in B9 is a whole number with no decimal part (and if there
is something in C9:G9). I would have patched it for Steve, but you never
told us what should happen when B9 contains a whole number... should it have
1 added to it (that is, if B9 contained, say, 5, should it become 6)?

--
Rick (MVP - Excel)



"sbain" wrote in message
...
Steve:
You are correct in assuming that I need to account for numbers that have
one
decimal place and two decimal places, but does your formula still do the
basis of what I need, in that if the "if" statement is true it will
roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when there
are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

By the way, notice that I made the range for the COUNTIF function C9:G9, and
NOT the range B9:G9 that you said you were using in your formula. If you
include B9, then the COUNTIF will always return at least a value of 1 which
means the true part of the IF statement will always execute.

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Here is what Steve posted placed inside the IF function call that you
originally asked for....

=IF(COUNTIF(C9:G9,"*"),ROUNDUP(B9,0),B9+10^-(LEN(B9)-FIND(".",B9)))

However, there is one possible problem with this formula... it will error
out if the value in B9 is a whole number with no decimal part (and if
there is something in C9:G9). I would have patched it for Steve, but you
never told us what should happen when B9 contains a whole number... should
it have 1 added to it (that is, if B9 contained, say, 5, should it become
6)?

--
Rick (MVP - Excel)



"sbain" wrote in message
...
Steve:
You are correct in assuming that I need to account for numbers that have
one
decimal place and two decimal places, but does your formula still do the
basis of what I need, in that if the "if" statement is true it will
roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when there
are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

Just an observation... you do not need to multiply the power of 10 by 1
(that is, you can leave out the 1* from your formula...

=IF(COUNTIF(B9:G9,"*"),ROUNDUP(B9,0),B9+10^-(LEN(B9)-FIND(".",B9)))

I also noted changed the range in the COUNTIF function to C9:G9... if B9 is
included, the IF function will always be TRUE. I posted this formula in my
own response to this thread and noted that the formula will not work if both
B9 contains a whole number and one or more of the cells in C9:G9 have values
in them... unfortunately, the OP never said what he wants added to whole
numbers, so I didn't know how to patch the formula to avoid the error.

--
Rick (MVP - Excel)



"Steve Dunn" wrote in message
...
Sierra,

what I gave you was only a part of the overall formula, your finished
formula would be:

=IF(COUNTIF(B9:G9,"*"),ROUNDUP(B9,0),
B9+1*10^-(LEN(B9)-FIND(".",B9)))



"sbain" wrote in message
...
Steve:
You are correct in assuming that I need to account for numbers that have
one
decimal place and two decimal places, but does your formula still do the
basis of what I need, in that if the "if" statement is true it will
roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when there
are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

Even if there is a whole number it is still written as a decmial. Ex: 1 will
be written as 1.00
--
Sierra


"Rick Rothstein" wrote:

Here is what Steve posted placed inside the IF function call that you
originally asked for....

=IF(COUNTIF(C9:G9,"*"),ROUNDUP(B9,0),B9+10^-(LEN(B9)-FIND(".",B9)))

However, there is one possible problem with this formula... it will error
out if the value in B9 is a whole number with no decimal part (and if there
is something in C9:G9). I would have patched it for Steve, but you never
told us what should happen when B9 contains a whole number... should it have
1 added to it (that is, if B9 contained, say, 5, should it become 6)?

--
Rick (MVP - Excel)



"sbain" wrote in message
...
Steve:
You are correct in assuming that I need to account for numbers that have
one
decimal place and two decimal places, but does your formula still do the
basis of what I need, in that if the "if" statement is true it will
roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when there
are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

.

If B9 is a whole number (1.00) and if C9:G9 are blank, then H9 should be
B9+.1 or 1.1. If any column between c9:g9 contain a character, then H9
should round up to next whole number. Only if a number in column B has two
decimal places (1.03) and C9:G9 are blank, then H9 needs to be B9+.01.
--
Sierra


"Rick Rothstein" wrote:

Here is what Steve posted placed inside the IF function call that you
originally asked for....

=IF(COUNTIF(C9:G9,"*"),ROUNDUP(B9,0),B9+10^-(LEN(B9)-FIND(".",B9)))

However, there is one possible problem with this formula... it will error
out if the value in B9 is a whole number with no decimal part (and if there
is something in C9:G9). I would have patched it for Steve, but you never
told us what should happen when B9 contains a whole number... should it have
1 added to it (that is, if B9 contained, say, 5, should it become 6)?

--
Rick (MVP - Excel)



"sbain" wrote in message
...
Steve:
You are correct in assuming that I need to account for numbers that have
one
decimal place and two decimal places, but does your formula still do the
basis of what I need, in that if the "if" statement is true it will
roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when there
are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

.

Doh! (regarding 1*)

As far as the range goes, it was the OP who changed it, along with
practically everything else...


"Rick Rothstein" wrote in message
...
Just an observation... you do not need to multiply the power of 10 by 1
(that is, you can leave out the 1* from your formula...

=IF(COUNTIF(B9:G9,"*"),ROUNDUP(B9,0),B9+10^-(LEN(B9)-FIND(".",B9)))

I also noted changed the range in the COUNTIF function to C9:G9... if B9
is included, the IF function will always be TRUE. I posted this formula in
my own response to this thread and noted that the formula will not work if
both B9 contains a whole number and one or more of the cells in C9:G9 have
values in them... unfortunately, the OP never said what he wants added to
whole numbers, so I didn't know how to patch the formula to avoid the
error.

--
Rick (MVP - Excel)



"Steve Dunn" wrote in message
...
Sierra,

what I gave you was only a part of the overall formula, your finished
formula would be:

=IF(COUNTIF(B9:G9,"*"),ROUNDUP(B9,0),
B9+1*10^-(LEN(B9)-FIND(".",B9)))



"sbain" wrote in message
...
Steve:
You are correct in assuming that I need to account for numbers that have
one
decimal place and two decimal places, but does your formula still do the
basis of what I need, in that if the "if" statement is true it will
roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when
there are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

Sorry about the delay, I was just checking back through my posts and noticed
I'd missed this one.

=IF(COUNTIF(C9:G9,"*"),ROUNDUP(B9,0)+(INT(B9)=B9),
B9+10^-(LEN(B9)-IF(INT(B9)=B9,0,FIND(".",B9))))



"sbain" wrote in message
...
If B9 is a whole number (1.00) and if C9:G9 are blank, then H9 should be
B9+.1 or 1.1. If any column between c9:g9 contain a character, then H9
should round up to next whole number. Only if a number in column B has
two
decimal places (1.03) and C9:G9 are blank, then H9 needs to be B9+.01.
--
Sierra


"Rick Rothstein" wrote:

Here is what Steve posted placed inside the IF function call that you
originally asked for....

=IF(COUNTIF(C9:G9,"*"),ROUNDUP(B9,0),B9+10^-(LEN(B9)-FIND(".",B9)))

However, there is one possible problem with this formula... it will error
out if the value in B9 is a whole number with no decimal part (and if
there
is something in C9:G9). I would have patched it for Steve, but you never
told us what should happen when B9 contains a whole number... should it
have
1 added to it (that is, if B9 contained, say, 5, should it become 6)?

--
Rick (MVP - Excel)



"sbain" wrote in message
...
Steve:
You are correct in assuming that I need to account for numbers that
have
one
decimal place and two decimal places, but does your formula still do
the
basis of what I need, in that if the "if" statement is true it will
roundup
to the next whole number? Please let me know. THanks.
--
Sierra


"Steve Dunn" wrote:

Sierra, you've moved all the goal posts!

But, I think/hope, what you are asking here is how to add .01 when
there
are
2 decimal places, .001 when there are 3 decimal places, etc., is that
correct?

If so, then you need something like:

B9+1*10^-(LEN(B9)-FIND(".",B9))

instead of B9+0.1

HTH
Steve D.



"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

.



.


.

.