CONCATENATE zeros

[Bullocks]

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!

 

[Mike H]

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]

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









- Show quoted text -

Fantastic! Thank you soooooo much.

 

[Bullocks]

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.

 

[JoeU2004]

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.

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 -----

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.

 

[Bullocks]

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.

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 fractionsis
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 -----

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!