Question aobut splitting a number
 

If there a way to do this:

I want to make a sheet that when I input a number it breaks out into to
cells, for example:

I input 1200 in to A1

I want B1 to show 1000 of that and
B2 to show the remaining 200.

Thanks for the help

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile: http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

I don't know if you've given us enough information to cover all of your
possible scenarios.

Maybe something like this can get you started:

A1: 1234

B1: =INT(A1/1000)*1000
C1: =INT(MOD(A1,1000)/100)*100
D1: =INT(MOD(A1,100)/10)*10
E1: =MOD(A1,10)

Those formulas return 1000, 200, 30, and 4, respectively.

These also return those same values:
B1: =FLOOR(A1,1000)
C1: =FLOOR(MOD(A1,1000),100)
D1: =FLOOR(MOD(A1,100),10)
E1: =MOD(A1,10)


Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

That did not work but thank you for input, I'm sorry for not explaiing
enough.

Here is excatly what i am trying to do

I want to input a number into say in a1 of that number I want up to
1000 go to say b1 and everything over 1000 go into b2.
So if I input 1234, 1000 goes to b1 and 234 goes to b2.
If I input 989, 989 goes to b1 nothing goes to b2
etc...

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile: http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

OK, Felix....try working with this:

A1: (any number)

B1: =IF(A1=1000,FLOOR(A1,1000),MOD(A1,1000))
C1: =IF(A1=1000,MOD(A1,1000),"")

Does that fit your situation better?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

Adjustment:

C1: =IF(A1=1000,MOD(A1,1000),"")
should be
B2: =IF(A1=1000,MOD(A1,1000),"")

Regards,
Ron

"nyrfan" wrote in
message ...

That did not work but thank you for input, I'm sorry for not explaiing
enough.

Here is excatly what i am trying to do

I want to input a number into say in a1 of that number I want up to
1000 go to say b1 and everything over 1000 go into b2.
So if I input 1234, 1000 goes to b1 and 234 goes to b2.
If I input 989, 989 goes to b1 nothing goes to b2
etc...

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile:
http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

Perfect! Thank you so much Ron!
Wait not excatly.

When I input 2210 2000 is going to b1 and only 210 to c1

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile: http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

OK sorry but I'm now really confused.

I need upto 1000 to be in one cell, everything over in another. the
way itis not if I enter 2210, 2000 is going to one cell and the the
210, I need 1000 and 1210.

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile: http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

Got it (finally)

A1: (any number)

B1: =MIN(A1,1000)
B2: =IF(A11000,A1-1000,"")

I hope that helps.

Regards,
Ron

"nyrfan" wrote in
message ...

OK sorry but I'm now really confused.

I need upto 1000 to be in one cell, everything over in another. the
way itis not if I enter 2210, 2000 is going to one cell and the the
210, I need 1000 and 1210.

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile:
http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

Hi!

Enter this in cell B1:

=IF(ISNUMBER(A1),MIN(1000,A1),"")

Enter this in cell C1:

=IF(A11000,A1-B1,"")

Biff

"nyrfan" wrote in
message ...

OK sorry but I'm now really confused.

I need upto 1000 to be in one cell, everything over in another. the
way itis not if I enter 2210, 2000 is going to one cell and the the
210, I need 1000 and 1210.

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile:
http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

Perfect Ron, thank you so much.

And thank you Biff.

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile: http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

Ron,

I still have a bit of a problem.

When entering a # over 1001 everthing is aok, when a number of less
then or = to 1000 there is still a problem.


Hope I explain this in a way that makes sence. Both cells, the one
with 1000 and over 1000 thousand are tied in to formulas. When a #
less then 1000 is entered the cell (b2) has nothing in it and the all
the cells tied to that with a formula gets a "value" statement. How do
I correct that?

For instance:

888 entered
888 in B1
B2 s blank

c5 tied to b2 with a formula has a "value" statement. The formula in
c5 similar to: is b2*a2 (which is 4.5)


Thanks,
Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile: http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

Hi!

Which formulas did you use?

If any of the formulas you used have "" in them, just replace that with 0.

For example:

=IF(A11000,A1-B1,"")

Change that to:

=IF(A11000,A1-B1,0)

Biff

"nyrfan" wrote in
message ...

Ron,

I still have a bit of a problem.

When entering a # over 1001 everthing is aok, when a number of less
then or = to 1000 there is still a problem.


Hope I explain this in a way that makes sence. Both cells, the one
with 1000 and over 1000 thousand are tied in to formulas. When a #
less then 1000 is entered the cell (b2) has nothing in it and the all
the cells tied to that with a formula gets a "value" statement. How do
I correct that?

For instance:

888 entered
888 in B1
B2 s blank

c5 tied to b2 with a formula has a "value" statement. The formula in
c5 similar to: is b2*a2 (which is 4.5)


Thanks,
Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile:
http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606

Question aobut splitting a number
 

That did it Biff, thanks a LOT.

Felix


--
nyrfan
------------------------------------------------------------------------
nyrfan's Profile: http://www.excelforum.com/member.php...o&userid=31192
View this thread: http://www.excelforum.com/showthread...hreadid=508606