Prev: How to sum by month
Next: Average a column containing numbers and text
From: sbain on 24 May 2010 10:28
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" <sbain(a)discussions.microsoft.com> wrote in message
> news:757F32EC-784F-488A-9B1D-51FF83A18B77(a)microsoft.com...
> > 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
>
> .
>
From: sbain on 24 May 2010 10:35
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" <sbain(a)discussions.microsoft.com> wrote in message
> news:757F32EC-784F-488A-9B1D-51FF83A18B77(a)microsoft.com...
> > 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
>
> .
>
From: T. Valko on 24 May 2010 10:49
>=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" <sbain(a)discussions.microsoft.com> wrote in message
news:74C97C51-0371-4229-A6CF-EA7792B8D6B6(a)microsoft.com...
> 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" <sbain(a)discussions.microsoft.com> wrote in message
>> news:757F32EC-784F-488A-9B1D-51FF83A18B77(a)microsoft.com...
>> > 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
>>
>> .
>>


From: sbain on 24 May 2010 14:12
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" <sbain(a)discussions.microsoft.com> wrote in message
> news:74C97C51-0371-4229-A6CF-EA7792B8D6B6(a)microsoft.com...
> > 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" <sbain(a)discussions.microsoft.com> wrote in message
> >> news:757F32EC-784F-488A-9B1D-51FF83A18B77(a)microsoft.com...
> >> > 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
> >>
> >> .
> >>
>
>
> .
>
From: T. Valko on 24 May 2010 20:26
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" <sbain(a)discussions.microsoft.com> wrote in message
news:1D528050-8C8D-4FF2-A42A-38E9DA7D34C3(a)microsoft.com...
> 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" <sbain(a)discussions.microsoft.com> wrote in message
>> news:74C97C51-0371-4229-A6CF-EA7792B8D6B6(a)microsoft.com...
>> > 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" <sbain(a)discussions.microsoft.com> wrote in message
>> >> news:757F32EC-784F-488A-9B1D-51FF83A18B77(a)microsoft.com...
>> >> > 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
>> >>
>> >> .
>> >>
>>
>>
>> .
>>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: How to sum by month
Next: Average a column containing numbers and text