IF STATEMENT
in [Functions]
|
From: Rick Rothstein on 25 May 2010 12:39 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" <stunn(a)sky.com> wrote in message news:3E791FFE-5AD3-4F84-9FCF-0E75C67A3A6B(a)microsoft.com... > 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" <sbain(a)discussions.microsoft.com> wrote in message > news:46CE9E77-A997-4ED8-8D85-A7CE3CFE6D68(a)microsoft.com... >> 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" <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 >>> >> >> >>> >> >> . >>> >> >> >>> >> >>> >> >>> >> . >>> >> >>> >>> . >>> >
From: sbain on 25 May 2010 14:42 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" <sbain(a)discussions.microsoft.com> wrote in message > news:46CE9E77-A997-4ED8-8D85-A7CE3CFE6D68(a)microsoft.com... > > 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" <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 > >> >> >> > >> >> >> . > >> >> >> > >> >> > >> >> > >> >> . > >> >> > >> > >> . > >> > . >
From: sbain on 25 May 2010 14:58 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" <sbain(a)discussions.microsoft.com> wrote in message > news:46CE9E77-A997-4ED8-8D85-A7CE3CFE6D68(a)microsoft.com... > > 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" <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 > >> >> >> > >> >> >> . > >> >> >> > >> >> > >> >> > >> >> . > >> >> > >> > >> . > >> > . >
From: Steve Dunn on 26 May 2010 02:45 Doh! (regarding 1*) As far as the range goes, it was the OP who changed it, along with practically everything else... "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:%23MCCMjC$KHA.348(a)TK2MSFTNGP06.phx.gbl... > 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" <stunn(a)sky.com> wrote in message > news:3E791FFE-5AD3-4F84-9FCF-0E75C67A3A6B(a)microsoft.com... >> 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" <sbain(a)discussions.microsoft.com> wrote in message >> news:46CE9E77-A997-4ED8-8D85-A7CE3CFE6D68(a)microsoft.com... >>> 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" <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 >>>> >> >> >>>> >> >> . >>>> >> >> >>>> >> >>>> >> >>>> >> . >>>> >> >>>> >>>> . >>>> >>
From: Steve Dunn on 3 Jun 2010 05:35 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" <sbain(a)discussions.microsoft.com> wrote in message news:5E56DADE-0DB7-4243-A7C2-80ABEB6D2575(a)microsoft.com... > 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" <sbain(a)discussions.microsoft.com> wrote in message >> news:46CE9E77-A997-4ED8-8D85-A7CE3CFE6D68(a)microsoft.com... >> > 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" <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
|
Pages: 1 2 3 4 Prev: How to sum by month Next: Average a column containing numbers and text |