Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
Hi,
I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
I'm lost when you say that your formula of:
3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
Haha true, I see what you mean, but it returns the correct number I am
after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
Let's try again ! ! !
You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
Hi Sandy,
I dont know if thats what I am after, because I dont want it to always round to 3 sig figures. It changes and so should my formula, but hopefully automatically. I want it to look at Q16 and say "ok that cell has 0.010 in it and my answer in the other cell is 0.007, but it must at least be in line with the minimum division size in Q16, so Ill make it 0.010 by rounding it up." OR "ok that cell has 0.010 in it and my answer in the other cell is 0.012, but it must at least be in line with the minimum division size in Q16, so Ill make it 0.010 by rounding it down." Cheers, Aaron. Sandy Mann wrote: I haven't really been following this thread really but if you want the answer rounded depending on the number of decimal places in Q6 then should the formula not actually be: =ROUND(I36-I37,LEN(Q16)-3) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
In that case does:
=ROUND(I36-I37,LEN(Q16)-2) do what you are looking for? -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk wrote in message oups.com... Hi Sandy, I dont know if thats what I am after, because I dont want it to always round to 3 sig figures. It changes and so should my formula, but hopefully automatically. I want it to look at Q16 and say "ok that cell has 0.010 in it and my answer in the other cell is 0.007, but it must at least be in line with the minimum division size in Q16, so Ill make it 0.010 by rounding it up." OR "ok that cell has 0.010 in it and my answer in the other cell is 0.012, but it must at least be in line with the minimum division size in Q16, so Ill make it 0.010 by rounding it down." Cheers, Aaron. Sandy Mann wrote: I haven't really been following this thread really but if you want the answer rounded depending on the number of decimal places in Q6 then should the formula not actually be: =ROUND(I36-I37,LEN(Q16)-3) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
No it just returns 0.000 as an answer. I tried changing the -2 to -1 -3
-4 etc but all 0.000 for answer. Cheers, Aaron. Sandy Mann wrote: In that case does: =ROUND(I36-I37,LEN(Q16)-2) do what you are looking for? -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk wrote in message oups.com... Hi Sandy, I dont know if thats what I am after, because I dont want it to always round to 3 sig figures. It changes and so should my formula, but hopefully automatically. I want it to look at Q16 and say "ok that cell has 0.010 in it and my answer in the other cell is 0.007, but it must at least be in line with the minimum division size in Q16, so Ill make it 0.010 by rounding it up." OR "ok that cell has 0.010 in it and my answer in the other cell is 0.012, but it must at least be in line with the minimum division size in Q16, so Ill make it 0.010 by rounding it down." Cheers, Aaron. Sandy Mann wrote: I haven't really been following this thread really but if you want the answer rounded depending on the number of decimal places in Q6 then should the formula not actually be: =ROUND(I36-I37,LEN(Q16)-3) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
Is this just Aaron winding you all up ??
Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
You know, I never look at the name of the OP, except what's in the Name
column. Maybe I should pay more attention. Thanks Pete!<g "Pete_UK" wrote in message oups.com... Is this just Aaron winding you all up ?? Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
I hope Im not sounding like someone on a wind up you know.
If you paste the formula into a cell you will see what I mean. I also have precision as displayed on also if that affects anything. Really need a solution on this one. Doesent sound too hard compared to all the other stuff I see ppl posting about. That stuff is indepth!!! Cheers, Aaron. RagDyer wrote: You know, I never look at the name of the OP, except what's in the Name column. Maybe I should pay more attention. Thanks Pete!<g "Pete_UK" wrote in message oups.com... Is this just Aaron winding you all up ?? Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
I dont know how to make it more clear RD, it isnt about the significant
figures, but about it intelligently rounding up or down to the number, or multiples of the number in Q16. So, 0.0003 or 0.0002 or 0.0001 would all go to 0.0000 and 0.0005 or 0.0007 would all round up to 0.0010 and 0.0017 or 0.0015 would round up to 0.0020 and 0.0011 or 0.0014 would round down to 0.0010 This is exactly how I posted the two examples you quoted. Same applies regardless of DP settings. Cheers, Aaron. RagDyer wrote: You know, I never look at the name of the OP, except what's in the Name column. Maybe I should pay more attention. Thanks Pete!<g "Pete_UK" wrote in message oups.com... Is this just Aaron winding you all up ?? Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
Personally I believe that it is a wind up but it makes no-never-mind to me
because to me it is just another learning exercise. =ROUND((I36-I37)/Q16,0)*Q16 Does what you are asking for. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Slashman" wrote in message ps.com... I dont know how to make it more clear RD, it isnt about the significant figures, but about it intelligently rounding up or down to the number, or multiples of the number in Q16. So, 0.0003 or 0.0002 or 0.0001 would all go to 0.0000 and 0.0005 or 0.0007 would all round up to 0.0010 and 0.0017 or 0.0015 would round up to 0.0020 and 0.0011 or 0.0014 would round down to 0.0010 This is exactly how I posted the two examples you quoted. Same applies regardless of DP settings. Cheers, Aaron. RagDyer wrote: You know, I never look at the name of the OP, except what's in the Name column. Maybe I should pay more attention. Thanks Pete!<g "Pete_UK" wrote in message oups.com... Is this just Aaron winding you all up ?? Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
Hi Sandy,
I am in Auckland NZ so prob not the "Aaron" others are referring to. There must be something I am missing here caus for ppl to think it a wind up the solution must be a really easy one but after trying alot of different ways to achieve what I am after, I cant see it hence why I posted for all you experts to lend your knowledge. I copied and pasted =ROUND((I36-I37)/Q16,0)*Q16 into my ssheet and it returns 0.000, so that still doesent work. Cheers, Aaron. Sandy Mann wrote: Personally I believe that it is a wind up but it makes no-never-mind to me because to me it is just another learning exercise. =ROUND((I36-I37)/Q16,0)*Q16 Does what you are asking for. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Slashman" wrote in message ps.com... I dont know how to make it more clear RD, it isnt about the significant figures, but about it intelligently rounding up or down to the number, or multiples of the number in Q16. So, 0.0003 or 0.0002 or 0.0001 would all go to 0.0000 and 0.0005 or 0.0007 would all round up to 0.0010 and 0.0017 or 0.0015 would round up to 0.0020 and 0.0011 or 0.0014 would round down to 0.0010 This is exactly how I posted the two examples you quoted. Same applies regardless of DP settings. Cheers, Aaron. RagDyer wrote: You know, I never look at the name of the OP, except what's in the Name column. Maybe I should pay more attention. Thanks Pete!<g "Pete_UK" wrote in message oups.com... Is this just Aaron winding you all up ?? Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
The formula is one of the standard ways of rounding to a multiple of a
selected amount, so the formula works. There must be something I am missing here caus for ppl to think it a wind up Perhaps it is because your Google profile shows that you posted an almost identical question on April 11 2006. The *From* shows you as Waikato300zcC but you sign yourself as Slash. Slashman, Waikato, Aaron? Multiple personalities? and other posts of *Slashman* are decrying Aaron as being an idiot. Troubled multiple personalities? For the archives, in case some other poster has the same return the Aaron says he is getting, the most common problem is that the *number* are not numbers at all but are text representations of numbers. -- Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Slashman" wrote in message ups.com... Hi Sandy, I am in Auckland NZ so prob not the "Aaron" others are referring to. There must be something I am missing here caus for ppl to think it a wind up the solution must be a really easy one but after trying alot of different ways to achieve what I am after, I cant see it hence why I posted for all you experts to lend your knowledge. I copied and pasted =ROUND((I36-I37)/Q16,0)*Q16 into my ssheet and it returns 0.000, so that still doesent work. Cheers, Aaron. Sandy Mann wrote: Personally I believe that it is a wind up but it makes no-never-mind to me because to me it is just another learning exercise. =ROUND((I36-I37)/Q16,0)*Q16 Does what you are asking for. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Slashman" wrote in message ps.com... I dont know how to make it more clear RD, it isnt about the significant figures, but about it intelligently rounding up or down to the number, or multiples of the number in Q16. So, 0.0003 or 0.0002 or 0.0001 would all go to 0.0000 and 0.0005 or 0.0007 would all round up to 0.0010 and 0.0017 or 0.0015 would round up to 0.0020 and 0.0011 or 0.0014 would round down to 0.0010 This is exactly how I posted the two examples you quoted. Same applies regardless of DP settings. Cheers, Aaron. RagDyer wrote: You know, I never look at the name of the OP, except what's in the Name column. Maybe I should pay more attention. Thanks Pete!<g "Pete_UK" wrote in message oups.com... Is this just Aaron winding you all up ?? Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
I fail to see what the Slash Slashman Aaron or Waikato300ZXclub has to
do with anything or whether I posted a near identical question in the past. What other posts are you talking about? Decrying Aaron as an idiot? What are you on about. I ask questions because I need the help of ppl in the groups. All the time so far I have got the answer I needed and problem solved from the different problems I have had. The spreadsheet I am working on is fairly involved and there are things I dont know how to do. After 15 messages in this thread, I find it amazing that after I explained myself perfectly in the first post, I am still not getting anywhere other than being accused of winding ppl up. The bottom line is no one has come up with the answer that works. Everything that has been posted by people doesent work. Perhaps I need to send the ssheet page so you can see exactly what I need, as explaining it in clear full terms isnt good enough. Show me the post on Apr11, in all my past posts I cant find any like you refer to. Thi9s is the only thread I have started on this problem. The numbers in the cells are numbers, not text representations. Cheers, Aaron, Slash, Slashman, Waikato300ZXclub or whoever you prefer. Sandy Mann wrote: The formula is one of the standard ways of rounding to a multiple of a selected amount, so the formula works. There must be something I am missing here caus for ppl to think it a wind up Perhaps it is because your Google profile shows that you posted an almost identical question on April 11 2006. The *From* shows you as Waikato300zcC but you sign yourself as Slash. Slashman, Waikato, Aaron? Multiple personalities? and other posts of *Slashman* are decrying Aaron as being an idiot. Troubled multiple personalities? For the archives, in case some other poster has the same return the Aaron says he is getting, the most common problem is that the *number* are not numbers at all but are text representations of numbers. -- Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Slashman" wrote in message ups.com... Hi Sandy, I am in Auckland NZ so prob not the "Aaron" others are referring to. There must be something I am missing here caus for ppl to think it a wind up the solution must be a really easy one but after trying alot of different ways to achieve what I am after, I cant see it hence why I posted for all you experts to lend your knowledge. I copied and pasted =ROUND((I36-I37)/Q16,0)*Q16 into my ssheet and it returns 0.000, so that still doesent work. Cheers, Aaron. Sandy Mann wrote: Personally I believe that it is a wind up but it makes no-never-mind to me because to me it is just another learning exercise. =ROUND((I36-I37)/Q16,0)*Q16 Does what you are asking for. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Slashman" wrote in message ps.com... I dont know how to make it more clear RD, it isnt about the significant figures, but about it intelligently rounding up or down to the number, or multiples of the number in Q16. So, 0.0003 or 0.0002 or 0.0001 would all go to 0.0000 and 0.0005 or 0.0007 would all round up to 0.0010 and 0.0017 or 0.0015 would round up to 0.0020 and 0.0011 or 0.0014 would round down to 0.0010 This is exactly how I posted the two examples you quoted. Same applies regardless of DP settings. Cheers, Aaron. RagDyer wrote: You know, I never look at the name of the OP, except what's in the Name column. Maybe I should pay more attention. Thanks Pete!<g "Pete_UK" wrote in message oups.com... Is this just Aaron winding you all up ?? Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Len Function Help
Ok I found the thread from Apr11, in that thread I asked for a totally
different thing. In that thread I wanted to change dp in cells with a button click over multiple cells referencing the dp in a key cell. JMB solved that one nicely, I forgot to reply, so Cheers JMB for that. This problem is similar but its not the DP amount that I want to change, its the number in a cell that I need to round either up or down based on a key cell's number. The normal formula works, =ROUND(I36-I37,3-LEN(INT(Q16))) but how do I automate it so it looks at the key cell and alters the formula to suit? Cheers, Slash. Slashman wrote: I fail to see what the Slash Slashman Aaron or Waikato300ZXclub has to do with anything or whether I posted a near identical question in the past. What other posts are you talking about? Decrying Aaron as an idiot? What are you on about. I ask questions because I need the help of ppl in the groups. All the time so far I have got the answer I needed and problem solved from the different problems I have had. The spreadsheet I am working on is fairly involved and there are things I dont know how to do. After 15 messages in this thread, I find it amazing that after I explained myself perfectly in the first post, I am still not getting anywhere other than being accused of winding ppl up. The bottom line is no one has come up with the answer that works. Everything that has been posted by people doesent work. Perhaps I need to send the ssheet page so you can see exactly what I need, as explaining it in clear full terms isnt good enough. Show me the post on Apr11, in all my past posts I cant find any like you refer to. Thi9s is the only thread I have started on this problem. The numbers in the cells are numbers, not text representations. Cheers, Aaron, Slash, Slashman, Waikato300ZXclub or whoever you prefer. Sandy Mann wrote: The formula is one of the standard ways of rounding to a multiple of a selected amount, so the formula works. There must be something I am missing here caus for ppl to think it a wind up Perhaps it is because your Google profile shows that you posted an almost identical question on April 11 2006. The *From* shows you as Waikato300zcC but you sign yourself as Slash. Slashman, Waikato, Aaron? Multiple personalities? and other posts of *Slashman* are decrying Aaron as being an idiot. Troubled multiple personalities? For the archives, in case some other poster has the same return the Aaron says he is getting, the most common problem is that the *number* are not numbers at all but are text representations of numbers. -- Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Slashman" wrote in message ups.com... Hi Sandy, I am in Auckland NZ so prob not the "Aaron" others are referring to. There must be something I am missing here caus for ppl to think it a wind up the solution must be a really easy one but after trying alot of different ways to achieve what I am after, I cant see it hence why I posted for all you experts to lend your knowledge. I copied and pasted =ROUND((I36-I37)/Q16,0)*Q16 into my ssheet and it returns 0.000, so that still doesent work. Cheers, Aaron. Sandy Mann wrote: Personally I believe that it is a wind up but it makes no-never-mind to me because to me it is just another learning exercise. =ROUND((I36-I37)/Q16,0)*Q16 Does what you are asking for. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Slashman" wrote in message ps.com... I dont know how to make it more clear RD, it isnt about the significant figures, but about it intelligently rounding up or down to the number, or multiples of the number in Q16. So, 0.0003 or 0.0002 or 0.0001 would all go to 0.0000 and 0.0005 or 0.0007 would all round up to 0.0010 and 0.0017 or 0.0015 would round up to 0.0020 and 0.0011 or 0.0014 would round down to 0.0010 This is exactly how I posted the two examples you quoted. Same applies regardless of DP settings. Cheers, Aaron. RagDyer wrote: You know, I never look at the name of the OP, except what's in the Name column. Maybe I should pay more attention. Thanks Pete!<g "Pete_UK" wrote in message oups.com... Is this just Aaron winding you all up ?? Pete RagDyer wrote: Let's try again ! ! ! You *JUST* posted: <<<" If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 " What the heck is the difference between the 2 examples? They *both* have *6* decimal places! Is this a typo? How about you put a pseudo code into words, describing *exactly* what you want to happen. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ups.com... Haha true, I see what you mean, but it returns the correct number I am after, 0.010 from 0.007 I tested it by changing "len" to 2-LEN and sure enough the number returned changes to 0.000 and with 4-LEN I get no change, with the returned result as 0.007. 3-LEN returns the result 0.010. Try to copy and paste it into a ssheet to see what I mean. But essentially Q16 automatically changes based on what the ssheet is doing that day. It might be 0.000010 (the DP is important) but a calculated answer in another cell could come back as 0.000003 and I must have the answers automatically round to the figure in Q16. If I got 0.000003 I would expect it to just round to zero. If I got 0.000005 I would expect it to round to 0.000010 Any help appreciated greatly, Cheers, Aaron. RagDyer wrote: I'm lost when you say that your formula of: 3-LEN(INT(Q16)) really works ! ! ! 0.1 0.01 0.00001 in Q16 all equate to the same number ... 2 ... since the integer length is always 1. Care to re-phrase your question so that a not overly bright old man (been saying that a lot lately), might understand? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... Hi, I have this formula which works well, but the 3-len part needs to be dependent on another cells content. Is it possible to have the formula automatically accommodate for this? Eg =ROUND(I36-I37,3-LEN(INT(Q16))) in cell Q16 is a number 0.010 as displayed, and the sum of i36-i37= 0.007 I need the formula to say ok the minimum division size is in multiples of Q16 (0.010) and change the 0.007 to either up or down depending on the calculation. This works currently, but the figure in Q16 can be 0.1 or 0.00001 etc, and the sum in i36-i37 could be anything also as it is a calculated error figure. I need the formula to be able to adapt automatically to the reference DP in Q16. Namely the "3-" part in the formula needs to accomodate for the Q16 number format changing. Is this achievable? Thanks in advance, Aaron. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round function in If statement | Excel Discussion (Misc queries) | |||
An Inquirey on the Round function | Excel Worksheet Functions | |||
how do i set up round function | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) |