LinkBack URL
About LinkBacks
Hello:
I have number in cell A2=1.14.
How would i split it up so that B2 = 1, c2=14
Let me Know if you have any questions.
Thanks.
Riz
Hello:
I have number in cell A2=1.14.
How would i split it up so that B2 = 1, c2=14
Let me Know if you have any questions.
Thanks.
Riz
Last edited by rizmomin; 02-16-2014 at 11:07 AM.
1 way would be to use Text2Columns - use . as the delimter
or
B2=--left(A2,1)
C2=--mid(a2,3,2)
I think you would need to provide additional samples for a better suggestion though
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
INT(A2)
would give the integer
Mode(A2,1)
would give you the fraction decimal part
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
I think based on OP requirements the MOD should be
=MOD(A2,1)*100
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hello Guys:
Thanks formula works.
Riz
Appreciate your feedback!
Which formula worked for you?
I saw a point "." at the end and figured we were working with text
Hello Guys:
It seems to be not working..please help
A2=12.14, please help split this up B2=12 and C2=14
Thanks again
Riz
what does
=MOD(A2,1)*100
give ?
maybe attach the spreadsheet
Hello etaf:
Please refer to attached sheet.
I have created formula in cell c17:C19 and also correct answers are shown in cell E17:E19.
Let me Know if you have any questions.
Thanks.
Riz
Originally Posted by rizmomin
Fro the first part
=INT(A2) will give you 12
For the second part:
=MOD(A2,1)*100 will give you 14
if you need 0.14
=MOD(A2,1)
i dont understand how the question of splitting up a number - works in the spreadsheet you posted ?
Hi etaf:
If the number is lets say 1.00, 1.14 then it would work.
But if the number is 12.00, 12.14 then it seems to work.
Let me Know if you have any questions.
Thanks.
Riz
Please see attached file
Try:
=IF(MOD(A17,1)=0,SUMPRODUCT((INT($C$3:$C$8)=A17)*$I$3:$I$8),SUMPRODUCT(($C$3:$C$8=A17)*$I$3:$I$8))
Quang PT
Hi Guys/Alkey:
Hello:
Please refer to attached sheet.
Let me explain you what i am after.
In cell C3:C8 i have code for Sub Group of an item.
Two left digit (Main Item):
Code 12 is Cheeseburger
Code 9 is Burger
Two right digit(sub items):
Code 10 is Kids Meal
Code 14 is Basket Deal
Now what i want know is to find how many of each caterogy as i have in listed in cell B17:B20.
Let me Know if you have any questions.
Thanks.
Riz
Hi Bebo:
I have used your formula and works for 2 items and does not for other 2.
Please have a look at attached sheet.
Let me Know if you have any questions.
Thanks.
Riz
Maybe:
Anyway, coding by numbers is not a good choice.
Please Login or Register to view this content.
mod(12.10,1) returns 0.099999999999999600000000000
must be something to do with precision so try rounding
=IF(MOD(A17,1),SUMPRODUCT((ROUND(MOD($C$3:$C$8,1),2)=A17)*($I$3:$I$8)),SUMPRODUCT((INT($C$3:$C$8)=A17)*($I$3:$I$8)))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Maybe:
=IF(SUMPRODUCT(--(LEFT($C$3:$C$8,2)=LEFT(A17,2)),--$I$3:$I$8)=0,SUMPRODUCT(--(RIGHT($C$3:$C$8,2)=RIGHT(A17,2)),--$I$3:$I$8),SUMPRODUCT(--(LEFT($C$3:$C$8,2)=LEFT(A17,2)),--($I$3:$I$8)))
Hello Guys:
It looks like i will use bebo's formula and it seems to be working for me.
I am very thankful to all for the help.
Thanks
Riz
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks