![]() |
If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
![]()
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 . . |
#12
|
|||
|
|||
![]()
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 . . . |
#13
|
|||
|
|||
![]()
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 . . . |
#14
|
|||
|
|||
![]()
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 . . . |
#15
|
|||
|
|||
![]()
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 (MVP - Excel) "Rick Rothstein" wrote in message ... 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 . . . |
#16
|
|||
|
|||
![]()
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 . . . |
#17
|
|||
|
|||
![]()
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 . . . . |
#18
|
|||
|
|||
![]()
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" 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 . . . . |
#19
|
|||
|
|||
![]()
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 . . . |
#20
|
|||
|
|||
![]()
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" wrote in message ... 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" 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 . . . . |
|
Thread Tools | |
Display Modes | |
|
|