| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONCATENATE zeros
I am trying to calculate only change and not dollars and then see if
the change balances... This works…. K6 = 4 Q:12 sum(A1:A12) - this yields $10.40 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 4 Q14 =IF(K6<Q13,"Balance","Out") this yields Balance BUT This does not with whole numbers… K6 = 0 Q:12 sum(A1:A12) - this yields $16.00 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 6 Q14 =IF(K6<Q13,"Balance","Out") this yields Out where it should balance as there are no cents. It seems that Excel does not seeing zero cents and thus jumps a decimal place. Any help anyone could give me would be greatly appreciated! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE zeros
Hi,
Your using text manipulation to work on numbers try these formula instead Q13 =(Q12-INT(Q12))*10 Q14 =IF(K6=Q13,"Balance","Out") Mike "Bullocks" wrote: I am trying to calculate only change and not dollars and then see if the change balances... This works€¦. K6 = 4 Q:12 sum(A1:A12) - this yields $10.40 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 4 Q14 =IF(K6<Q13,"Balance","Out") this yields Balance BUT This does not with whole numbers€¦ K6 = 0 Q:12 sum(A1:A12) - this yields $16.00 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 6 Q14 =IF(K6<Q13,"Balance","Out") this yields Out where it should balance as there are no cents. It seems that Excel does not seeing zero cents and thus jumps a decimal place. Any help anyone could give me would be greatly appreciated! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE zeros
On Jul 7, 3:47*pm, Mike H wrote:
Hi, Your using text manipulation to work on numbers try these formula instead Q13 *=(Q12-INT(Q12))*10 Q14 *=IF(K6=Q13,"Balance","Out") Mike "Bullocks" wrote: I am trying to calculate only change and not dollars and then see if the change balances... This works…. K6 = 4 Q:12 sum(A1:A12) *- this yields $10.40 Q13 =CONCATENATE(RIGHT(Q12,1)) *- this yields the 4 Q14 =IF(K6<Q13,"Balance","Out") this yields *Balance BUT This does not with whole numbers… K6 = 0 Q:12 sum(A1:A12) *- this yields $16.00 Q13 =CONCATENATE(RIGHT(Q12,1)) *- this yields the 6 Q14 =IF(K6<Q13,"Balance","Out") this yields *Out where it should balance as there are no cents. It seems that Excel does not seeing zero cents and thus jumps a decimal place. Any help anyone could give me would be greatly appreciated!- Hide quoted text - - Show quoted text - Fantastic! Thank you soooooo much. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE zeros
On Jul 7, 4:57*pm, Bullocks wrote:
On Jul 7, 3:47*pm, Mike H wrote: Hi, Your using text manipulation to work on numbers try these formula instead Q13 *=(Q12-INT(Q12))*10 Q14 *=IF(K6=Q13,"Balance","Out") Mike "Bullocks" wrote: I am trying to calculate only change and not dollars and then see if the change balances... This works…. K6 = 4 Q:12 sum(A1:A12) *- this yields $10.40 Q13 =CONCATENATE(RIGHT(Q12,1)) *- this yields the 4 Q14 =IF(K6<Q13,"Balance","Out") this yields *Balance BUT This does not with whole numbers… K6 = 0 Q:12 sum(A1:A12) *- this yields $16.00 Q13 =CONCATENATE(RIGHT(Q12,1)) *- this yields the 6 Q14 =IF(K6<Q13,"Balance","Out") this yields *Out where it should balance as there are no cents. It seems that Excel does not seeing zero cents and thus jumps a decimal place. Any help anyone could give me would be greatly appreciated!- Hide quoted text - - Show quoted text - Fantastic! Thank you soooooo much.- Hide quoted text - - Show quoted text - Argh... spoke to soon. The magic numer 38.90 does not work. Every other number I tried has without fail but this one. Even when I mess with the number it is being balanced against... from 0-9 it aways reads "out". Strange. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE zeros
"Bullocks" wrote:
I am trying to calculate only change and not dollars and then see if the change balances So let's work with the example 12.34 in Q12 and make sure the solution does exactly what you intend. Frankly, that is unclear to me from your example. K6 = 4 Q:12 sum(A1:A12) - this yields $10.40 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 4 Q14 =IF(K6<Q13,"Balance","Out") this yields Balance One approach: Q13: =mod(round(Q12*100,0),100) Q14: =if(K6=Q13, "balances", "does not balance") Note: That assumes that K6 is 34 in my example; 40, not 4, in your example. If you are truly trying to limit the comparison to only the tenths digit, please post back with that clarification, and we can adjust my solution. "Bullocks" wrote: On Jul 7, 3:47 pm, Mike H wrote: Q13 =(Q12-INT(Q12))*10 Q14 =IF(K6=Q13,"Balance","Out") [....] The magic numer 38.90 does not work. Because Mike forgot that the internal representation of decimal fractions is not always what they appear to be. We should always use ROUND diligently. ((Q12-INT(Q12))*10 is really about 8.99999999999999. Applying Mike's solution -- just another way to write MOD, albeit more reliable for some numbers -- to my assumption (you want to compare cents, as you say, not just the tenths digit): Q13: =round((Q12-int(Q12)*100,0) Q14: =if(K6=Q13, "balances", "does not balance") ----- original message ----- "Bullocks" wrote in message ... On Jul 7, 4:57 pm, Bullocks wrote: On Jul 7, 3:47 pm, Mike H wrote: Hi, Your using text manipulation to work on numbers try these formula instead Q13 =(Q12-INT(Q12))*10 Q14 =IF(K6=Q13,"Balance","Out") Mike "Bullocks" wrote: I am trying to calculate only change and not dollars and then see if the change balances... This works…. K6 = 4 Q:12 sum(A1:A12) - this yields $10.40 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 4 Q14 =IF(K6<Q13,"Balance","Out") this yields Balance BUT This does not with whole numbers… K6 = 0 Q:12 sum(A1:A12) - this yields $16.00 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 6 Q14 =IF(K6<Q13,"Balance","Out") this yields Out where it should balance as there are no cents. It seems that Excel does not seeing zero cents and thus jumps a decimal place. Any help anyone could give me would be greatly appreciated!- Hide quoted text - - Show quoted text - Fantastic! Thank you soooooo much.- Hide quoted text - - Show quoted text - Argh... spoke to soon. The magic numer 38.90 does not work. Every other number I tried has without fail but this one. Even when I mess with the number it is being balanced against... from 0-9 it aways reads "out". Strange. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE zeros
On Jul 7, 8:33*pm, "JoeU2004" wrote:
"Bullocks" wrote: I am trying to calculate only change and not dollars and then see if the change balances So let's work with the example 12.34 in Q12 and make sure the solution does exactly what you intend. *Frankly, that is unclear to me from your example. K6 = 4 Q:12 sum(A1:A12) *- this yields $10.40 Q13 =CONCATENATE(RIGHT(Q12,1)) *- this yields the 4 Q14 =IF(K6<Q13,"Balance","Out") this yields *Balance One approach: Q13: *=mod(round(Q12*100,0),100) Q14: *=if(K6=Q13, "balances", "does not balance") Note: *That assumes that K6 is 34 in my example; 40, not 4, in your example. If you are truly trying to limit the comparison to only the tenths digit, please post back with that clarification, and we can adjust my solution. "Bullocks" wrote: On Jul 7, 3:47 pm, Mike H wrote: Q13 =(Q12-INT(Q12))*10 Q14 =IF(K6=Q13,"Balance","Out") [....] The magic numer 38.90 does not work. Because Mike forgot that the internal representation of decimal fractions is not always what they appear to be. *We should always use ROUND diligently. ((Q12-INT(Q12))*10 is really about 8.99999999999999. Applying Mike's solution -- just another way to write MOD, albeit more reliable for some numbers -- to my assumption (you want to compare cents, as you say, not just the tenths digit): Q13: *=round((Q12-int(Q12)*100,0) Q14: *=if(K6=Q13, "balances", "does not balance") ----- original message ----- "Bullocks" wrote in message ... On Jul 7, 4:57 wrote: On Jul 7, 3:47 pm, Mike H wrote: Hi, Your using text manipulation to work on numbers try these formula instead Q13 =(Q12-INT(Q12))*10 Q14 =IF(K6=Q13,"Balance","Out") Mike "Bullocks" wrote: I am trying to calculate only change and not dollars and then see if the change balances... This works…. K6 = 4 Q:12 sum(A1:A12) - this yields $10.40 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 4 Q14 =IF(K6<Q13,"Balance","Out") this yields Balance BUT This does not with whole numbers… K6 = 0 Q:12 sum(A1:A12) - this yields $16.00 Q13 =CONCATENATE(RIGHT(Q12,1)) - this yields the 6 Q14 =IF(K6<Q13,"Balance","Out") this yields Out where it should balance as there are no cents. It seems that Excel does not seeing zero cents and thus jumps a decimal place. Any help anyone could give me would be greatly appreciated!- Hide quoted text - - Show quoted text - Fantastic! Thank you soooooo much.- Hide quoted text - - Show quoted text - Argh... spoke to soon. *The magic numer 38.90 does not work. *Every other number I tried has without fail but this one. *Even when I mess with the number it is being balanced against... from 0-9 it aways reads "out". *Strange.- Hide quoted text - - Show quoted text - Many thanks again! What if I now did only want the 10ths? Eg. I had $38.24 and I only wated to balance the .2 with a 2. Cheers! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenate with leading zeros | Excel Worksheet Functions | |||
| How can I concatenate text with leading zeros | Excel Worksheet Functions | |||
| Concatenate Text & Numbers Keeping zeros.. | Excel Worksheet Functions | |||
| Concatenate and keep preceeding zeros | Excel Worksheet Functions | |||
| save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) | |||
Linear Mode
