I have this data:
Price
11.99000000
8.35000000
71.99000000
7.99000000
1.00000000
10.00000000
10.00000000
I need to set two decimal places to the right the # 1.
So the result should be, from my example data:
11.91000000
8.31000000
71.91000000
7.91000000
1.01000000
10.01000000
10.01000000
Any nudge is greatly appreciated!
Thanks,
Chef
If the original number is in A1, this formula will make the change:
=(INT(A1*10)+0.1)/10
Use the same formatting as your original number.
I will test this, thank for the fast response.
Are these real numbers, or text that look like numbers??? What formatting is applied to the cells?
You could try this...
=IF(ISNUMBER(SEARCH(".",A1)),LEFT(A1,LEN(A1)-1)+0.01,A1+0.01)
If that's not it, post some actual data - in an excel sheet.
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Here is 30 rows of sample data. I need the Price column edited with 13.01 (as an example)
Thanks again, everyone.
Not only did I horrendously overcomplicate this... I got it wrong, too!! I assumed (incorrectly) that they were numbers formatted to a large number of dps. C'est la Vie.
This is POS (Point of Sale) data and the data is formatted like that in the Inventory table in the POS. FYI
Do tell. What do you mean?Originally Posted by Glenn Kennedy
Here is with MOD
Formula:
Please Login or Register to view this content.
Last edited by AlKey; 09-22-2017 at 01:31 PM.
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
Try my formula in you sheet. I assumed that you were working with real numbers, not text that looks like numbers. Had they been numbers, it would have been overly complicated, but did work (as per my sheet). However, being text, that LOOKS like a number, it delivered totally incorrect answers.
6SJ got it spot-on.
My sheets, all rows/columns are set to TEXT. They have to be or the data is incorrectly formatted when I paste it in from SQL. In addition, when I upload this back into the POS Software, it must be in text/csv format.
Yea. That's what you have... But it wasn't clear from the first post, which is why I went off in the wrong direction. As I said, 6SJ's formula is fine.
Here is if you want this as TEXT
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks