IF STATEMENT

[sbain]

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.

 

[T. Valko]

=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?

 

[sbain]

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 #.

 

[sbain]

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.

 

[Rick Rothstein]

Does this do what you want?

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

 

[sbain]

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.

 

[sbain]

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

 

[T. Valko]

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

what does the "*" represent?

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

 

[sbain]

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?

 

[T. Valko]

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.

?????

 

[Steve Dunn]

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]

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.

 

[Steve Dunn]

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)))

 

[Rick Rothstein]

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 Rothstein]

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 Rothstein]

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.

 

[sbain]

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

 

[sbain]

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.

 

[Steve Dunn]

Doh! (regarding 1*)

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

 

[Steve Dunn]

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))))