Home 
Search 
Today's Posts 
#1




How can we increment 1.9+0.1 and display 1.10 NOT 2.0 using formula
Hello,
I'm trying to increment using two criteria; Category (Primary #) and Question(Secondary #). Categories will be 1, 2, 3, and the questions that fall under each category will increment by 0.1 (i.e. 1.1, 1.2, etc..). I'm using the formula B2+0.1. It works great up until it reaches 1.9, but after that, it jumps to 2. It should display display it as 1.10. I’ve attached a screenshot to illustrate what I’m trying to do. One more thing (Sorry lol), I've also noticed that 1.10 will display as 1.1 creating a duplicate. I'll be using these numbers as IDs, so it's important that they're unique.. Thanks in advance for all your help 
#2




How can we increment 1.9+0.1 and display 1.10 NOT 2.0 using formula
On Wednesday, August 19, 2020 at 9:49:08 AM UTC10, ymadi wrote:
Hello, I'm trying to increment using two criteria; Category (Primary #) and Question(Secondary #). Categories will be 1, 2, 3, and the questions that fall under each category will increment by 0.1 (i.e. 1.1, 1.2, etc..). I'm using the formula B2+0.1. It works great up until it reaches 1.9, but after that, it jumps to 2. It should display display it as 1.10. Iâ€™ve attached a screenshot to illustrate what Iâ€™m trying to do. One more thing (Sorry lol), I've also noticed that 1.10 will display as 1.1 creating a duplicate. I'll be using these numbers as IDs, so it's important that they're unique.. Thanks in advance for all your help ++ Filename: Increment Help.jpg  Download: http://www.excelbanter.com/attachment.php?attachmentid=1069 ++  ymadi Your spreadsheet is calculating correctly based on the data you are inputting. Row 3 in your range already has the 1.1 value you want returned in Row 12. As you note, there is no difference in the numbers 1.1 and 1.10 so your calc would create a duplicate number anyway. Maybe you want to work in true hundreths instead of tens: 1.10, 1.11, 1.12, 1.13, 1.14.... 2.10, 2.11, 2.12, 2.13, etc. HTH 
#3




Hi Miles!
Thanks for responding! You're absolutely right and it did worked I was so focused on making the logic smarter, that I did think about this simple mathematical solution lol. Again, thanks for all your help. Yasser Quote:

#4




How can we increment 1.9+0.1 and display 1.10 NOT 2.0 using formula
Miles Baidack wrote:
On Wednesday, August 19, 2020 at 9:49:08 AM UTC10, ymadi wrote: Hello, I'm trying to increment using two criteria; Category (Primary #) and Question(Secondary #). Categories will be 1, 2, 3, and the questions that fall under each category will increment by 0.1 (i.e. 1.1, 1.2, etc..). I'm using the formula B2+0.1. It works great up until it reaches 1.9, but after that, it jumps to 2. It should display display it as 1.10. I've attached a screenshot to illustrate what I'm trying to do. One more thing (Sorry lol), I've also noticed that 1.10 will display as 1.1 creating a duplicate. I'll be using these numbers as IDs, so it's important that they're unique.. Thanks in advance for all your help Your spreadsheet is calculating correctly based on the data you are inputting. Row 3 in your range already has the 1.1 value you want returned in Row 12. As you note, there is no difference in the numbers 1.1 and 1.10 so your calc would create a duplicate number anyway. Maybe you want to work in true hundreths instead of tens: 1.10, 1.11, 1.12, 1.13, 1.14.... 2.10, 2.11, 2.12, 2.13, etc. HTH You'll have to treat the data as strings (watch the word wrap): =IFERROR(LEFT(A1,FIND(".",A1))&MID(A1,FIND(".",A1) +1,LEN(A1)FIND(".",A1))+ 1,A1) There's probably better ways to do it, but this works for me.  Your mileage may vary. Results not guaranteed. Warranty void in the United States, England, and the planet Earth. 
#5




How can we increment 1.9+0.1 and display 1.10 NOT 2.0 using formula
My solution would be to create a cell for each category number and a cell for each question number, then in the incremented cells use a formula to combine the text in your category and question cells.
Where A2 is your category number and B2 is your question number, your formula would read: =A2&"."&B2 Make sure to include the quotations around the period or whatever divider you choose. Also, you can use an absolute reference for your category ($A$2) so you can copy the formula down your column/row. Using this method you will end up with an extra column or row of data with your category & question numbers but you can choose to hide those cells if you wish. I hope this helps! Blythe On Wednesday, August 19, 2020 at 2:49:08 PM UTC5, ymadi wrote: Hello, I'm trying to increment using two criteria; Category (Primary #) and Question(Secondary #). Categories will be 1, 2, 3, and the questions that fall under each category will increment by 0.1 (i.e. 1.1, 1.2, etc..). I'm using the formula B2+0.1. It works great up until it reaches 1.9, but after that, it jumps to 2. It should display display it as 1.10. Iâ€™ve attached a screenshot to illustrate what Iâ€™m trying to do. One more thing (Sorry lol), I've also noticed that 1.10 will display as 1.1 creating a duplicate. I'll be using these numbers as IDs, so it's important that they're unique.. Thanks in advance for all your help ++ Filename: Increment Help.jpg  Download: http://www.excelbanter.com/attachment.php?attachmentid=1069 ++  ymadi 
#6




How can we increment 1.9+0.1 and display 1.10 NOT 2.0 using formula
On Thursday, August 20, 2020 at 3:49:50 PM UTC5, Auric__ wrote:
Miles Baidack wrote: On Wednesday, August 19, 2020 at 9:49:08 AM UTC10, ymadi wrote: Hello, I'm trying to increment using two criteria; Category (Primary #) and Question(Secondary #). Categories will be 1, 2, 3, and the questions that fall under each category will increment by 0.1 (i.e. 1.1, 1.2, etc..). I'm using the formula B2+0.1. It works great up until it reaches 1.9, but after that, it jumps to 2. It should display display it as 1.10. I've attached a screenshot to illustrate what I'm trying to do. One more thing (Sorry lol), I've also noticed that 1.10 will display as 1.1 creating a duplicate. I'll be using these numbers as IDs, so it's important that they're unique.. Thanks in advance for all your help Your spreadsheet is calculating correctly based on the data you are inputting. Row 3 in your range already has the 1.1 value you want returned in Row 12. As you note, there is no difference in the numbers 1.1 and 1.10 so your calc would create a duplicate number anyway. Maybe you want to work in true hundreths instead of tens: 1.10, 1.11, 1.12, 1.13, 1.14.... 2.10, 2.11, 2.12, 2.13, etc. HTH You'll have to treat the data as strings (watch the word wrap): =IFERROR(LEFT(A1,FIND(".",A1))&MID(A1,FIND(".",A1) +1,LEN(A1)FIND(".",A1))+ 1,A1) There's probably better ways to do it, but this works for me.  Your mileage may vary. Results not guaranteed. Warranty void in the United States, England, and the planet Earth. 
#7




How can we increment 1.9+0.1 and display 1.10 NOT 2.0 using formula
Op 492020 om 23:48 schreef Blythe Richardson Smith:
My solution would be to create a cell for each category number and a cell for each question number, then in the incremented cells use a formula to combine the text in your category and question cells. Where A2 is your category number and B2 is your question number, your formula would read: =A2&"."&B2 Make sure to include the quotations around the period or whatever divider you choose. Also, you can use an absolute reference for your category ($A$2) so you can copy the formula down your column/row. Using this method you will end up with an extra column or row of data with your category & question numbers but you can choose to hide those cells if you wish. I hope this helps! Blythe On Wednesday, August 19, 2020 at 2:49:08 PM UTC5, ymadi wrote: Hello, I'm trying to increment using two criteria; Category (Primary #) and Question(Secondary #). Categories will be 1, 2, 3, and the questions that fall under each category will increment by 0.1 (i.e. 1.1, 1.2, etc..). I'm using the formula B2+0.1. It works great up until it reaches 1.9, but after that, it jumps to 2. It should display display it as 1.10. Iâ€™ve attached a screenshot to illustrate what Iâ€™m trying to do. One more thing (Sorry lol), I've also noticed that 1.10 will display as 1.1 creating a duplicate. I'll be using these numbers as IDs, so it's important that they're unique.. Thanks in advance for all your help ++ Filename: Increment Help.jpg  Download: http://www.excelbanter.com/attachment.php?attachmentid=1069 ++  ymadi try 1.09 + 0.01 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Increment value in a Formula  Excel Discussion (Misc queries)  
formula increment  Excel Worksheet Functions  
Am trying to autofill a formula and it will not increment down?  Excel Worksheet Functions  
Increment/Increment letter in alphabetical order  Excel Programming  
How do I increment a formula?  Excel Worksheet Functions 