Thread: IF STATEMENT
View Single Post
  #17  
Old May 25th, 2010, 07:42 PM posted to microsoft.public.excel.worksheet.functions
sbain
external usenet poster
 
Posts: 9
Default IF STATEMENT

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

.



.


.

.