Thread: IF STATEMENT
View Single Post
  #19  
Old May 26th, 2010, 07:45 AM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default IF STATEMENT

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

.



.


.