spliting numbers

[mly3]

I would have a preliminary data, a number, on sheet1. The final data would be on sheet2.
s
In the case where there is a whole number as the preliminary data, the data would remain the same as the final output. (eg. 248 --> 248)

In the case where there is a number with a decimal, I need a function where it looks at the original number as two sets of WHOLE numbers. (Eg. 248.30 --> 248 and 30). Where the digits to the left of the decimal remains the same and the digits to the right would be divided by x (in this case, 10). The final output would be 2483 (where 248 is the same and the 30 is divided by 10.

Since I am using an older version of excel, I cannot use quotient. I know that I would be using the vlookup function and perhaps an if function. however, I do not know how to separate the numbers in regards to the decimal.

Please help.

[shg]

The final output would be 2481 (where 248 is the same and the 30 is divided by 10.

30 divided by 10 is 3, not 1 ...

[mly3]

sorry. correction made.

perhaps i can use a round function or the right/left function...?

[Ron Coderre]

With
A1 containing a number that may have a decimal point.

This formula returns that number with the decimal point removed:

Please Login or Register  to view this content.

With that formula....
248 becomes 248
248.3 becomes 2483
248.31 becomes 24831

Does that help?

[mly3]

yes it does, for the values to the left of the decimal. - the value remained the same.

however, if the value is to the right of the decimal, it must be subjected to division before the final product.

Example.

initial value: 248.4
+ to the left of the decimal, 248 remains intact
+ in this case, 4 would be subjected to be divided by 2
+ remove decimal
final value: 2482

[shg]

The 4 is divided by 2 because ...?

[mly3]

a conversion from one set of rules to another.

basically a client gives us an order using their pricing system and we have a different pricing system

the discrepancy is found on the values after the decimal point. for certain contracts (the if function would be used), the values after the decimal point is divided by a certain value/variable that changes in accordance with the if function.

therefore, i need to make the value after the decimal subject to division while having the value to the left of the decimal remain intact.

[shg]

=LEFT(A1, FIND(".", A1) -1) & MID(A1, FIND(".", A1) + 1, 255) / yourIfFormula

[mly3]

=LEFT(A1, FIND(".", A1) -1) & MID(A1, FIND(".", A1) + 1, 255) / yourIfFormula

How do I put in that formula in a single cell (referring to the "&")?

For the If formula, I would say (hypothetically)
=IF(A5="Copper", [how do I make the formula recognize the values to the right of the decimal and have it divided?], [nested if, go on to next cell])

And thanks so far for the assistance!

[shg]

I think we're at the point where you need to get specific, post a workbook, and explain in context.

[mly3]

Sorry.

I have attached a workbook.

On sheet1, we have the preliminary data and we'll mostly be looking at column E "LTP_PRICE" and column G "PRODUCT"

On sheet 2, we have the conversion sheet where column G "PRODUCT" would be converted to symbols. (Eg. SH STERLNG in sheet1 becomes L (column H of sheet2))

On sheet 3, we have the final product and our final values go in column AF.

* Now, my situation is to input a formula in sheet3, column AF where it converts the price from sheet1 to a value sheet 3 would "understand"

* Specifically, Sheet1, cell E20 & G20. In G20, we see that it is a C30TBOND so the value after the decimal would be divided by .03125. In E20, the price of C30TBOND is 116.7188. Therefore the 116 would remain intact and 7188 would be divided by .03125. The final product on sheet 3 (cell AF20) would be 11623 [7188/.03125=230016]

* Only some of the products would be subjected to the conversion and this would be covered by an nested IF statement.


I hope this makes sense, again, thanks for all the help so far!

[shg]

In E20, the price of C30TBOND is 116.7188. Therefore the 116 would remain intact and 7188 would be divided by .03125. The final product on sheet 3 (cell AF20) would be 11623 [7188/.03125=230016]

I don't follow that. What is the logic that says to only use the first two digits of the division 23016?

[mly3]

Its not logic, its simply a conversion of a clients pricing system to our pricing system.

and we use only the "23" because of the the digit limit (5 characters)

thanks so far.

[hquintana]

Can some one help me remove the first three numbers from a cell that has five.

ex
have.........need
A1...............B1
81025.......810