Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 19th 20, 08:04 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2015
Posts: 4
Question 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.

Ive attached a screenshot to illustrate what Im 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
Attached Images
 

  #2   Report Post  
Old August 20th 20, 04:55 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2020
Posts: 1
Default 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 UTC-10, 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   Report Post  
Old August 20th 20, 05:56 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2015
Posts: 4
Default

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:
Originally Posted by Miles Baidack View Post
On Wednesday, August 19, 2020 at 9:49:08 AM UTC-10, 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
  #4   Report Post  
Old August 20th 20, 09:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 516
Default 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 UTC-10, 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   Report Post  
Old September 4th 20, 10:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2020
Posts: 3
Default 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 UTC-5, 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   Report Post  
Old September 4th 20, 10:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2020
Posts: 3
Default 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 UTC-5, Auric__ wrote:
Miles Baidack wrote:

On Wednesday, August 19, 2020 at 9:49:08 AM UTC-10, 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   Report Post  
Old September 14th 20, 09:30 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2020
Posts: 1
Default How can we increment 1.9+0.1 and display 1.10 NOT 2.0 using formula

Op 4-9-2020 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 UTC-5, 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Increment value in a Formula David Marr Excel Discussion (Misc queries) 7 April 15th 10 08:51 AM
formula increment in2ition Excel Worksheet Functions 4 June 24th 08 01:30 AM
Am trying to autofill a formula and it will not increment down? Opsmgr Excel Worksheet Functions 1 January 30th 08 07:58 PM
Increment/Increment letter in alphabetical order Neil Goldwasser Excel Programming 3 January 25th 06 09:07 AM
How do I increment a formula? JICDB Excel Worksheet Functions 2 September 16th 05 06:53 PM


All times are GMT +1. The time now is 07:41 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017