Remember Me?

#1
August 19th 20, 08:04 PM
 Junior Member First recorded activity by ExcelBanter: May 2015 Posts: 4
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..

Attached Images

#2
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
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..

+-------------------------------------------------------------------+
|Filename: Increment Help.jpg |
+-------------------------------------------------------------------+

--

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
August 20th 20, 05:56 PM
 Junior Member First recorded activity by ExcelBanter: May 2015 Posts: 4

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
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
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..

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
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
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.

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..

+-------------------------------------------------------------------+
|Filename: Increment Help.jpg |
+-------------------------------------------------------------------+

--

#6
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
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..

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
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
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.

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..

+-------------------------------------------------------------------+
|Filename: Increment Help.jpg |
+-------------------------------------------------------------------+

--

try 1.09 + 0.01

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post David Marr Excel Discussion (Misc queries) 7 April 15th 10 08:51 AM in2ition Excel Worksheet Functions 4 June 24th 08 01:30 AM Opsmgr Excel Worksheet Functions 1 January 30th 08 07:58 PM Neil Goldwasser Excel Programming 3 January 25th 06 09:07 AM JICDB Excel Worksheet Functions 2 September 16th 05 06:53 PM

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