Count where the sequence goes from 5.9 to 5.10 to 5.11 etc

Shales

Board Regular
Joined
Aug 8, 2006
Messages
168
Hello everyone,

Really struggling with a piece of work that hopefully one of you can kindly help with.

I am basically look for a type of count that will not jump up a whole number when it reaches .9. For example, I am looking for my count to go from;

5.8
5.9
5.10
5.11
5.12

Here is a sample of my data;

rank(L) count(M) Lookup(O)
1 1 1.1
2 1 2.1
3 1 3.1
4 1 4.1
5 11 5.1
6 0 5.2
7 0 5.3
8 0 5.4
9 0 5.5
10 0 5.6
11 0 5.7
12 0 5.8
13 0 5.9
14 0 6
15 0 6.1
16 1 16.1
17 1 17.1
18 1 18.1

my data is showing that there are 11 counts of "5", so therefore I require my "lookup" field formula to display 5.1 to 5.11. Instead it is displaying 5.1 to 6.1. The formula I am currently using is;

=(IF(M9>0,L9&"."&"1",O8+0.1))

Any suggestions or is what I am after not even possible?

Thanks
Gavin
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
Hi Gavin,

What if 0 is not there against 6 ? let say 1 is there, then where 5.2 should appear ?

rank(L) count(M)Lookup(O)
111.1
212.1
313.1
414.1
5115.1
605.2
705.3
805.4
905.5
1005.6
1105.7
1205.8
1305.9
1406
1506.1
16116.1
17117.1
18118.1

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Regards,
DILIPandey
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Can we use this logic ?

IF count (M) = 0, continue the count from the previous row, for example 5.9 to 5.10, and so on.
IF count (M) = [any number other than 0], reset the count, e.g. from 5.X to 6.1

Have I got it right ?

If yes, then we can probably do something with formulas to reflect this.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

=IF(M9>0,L9&"."&"01",TEXT(O8+0.01,"0.00"))

That way 5.1 isn't the same as 5.10 and your data will sort correctly if you ever need to do it.
 

Shales

Board Regular
Joined
Aug 8, 2006
Messages
168

ADVERTISEMENT

To Gerald

IF Count (M) = 0 then it is correctly taking the number from L and adding .1 to make it 1.1
IF Count (M) = 11 (for example) then I require it to take the number in (L), which is 5, and counting it 11 times. eg from 5.1 to 5.11

Hope this makes sense. Apologies if I m not clearly explaining this, my brain is currently dripping out of my ears!!
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617
There seems to be some difference of opinion as to what you're actually looking for, so I'll throw in my version:

LMNO
1RankCountLookup
2111.1
3212.1
4313.1
5414.1
65115.1
7605.2
8705.3
9805.4
10905.5
111005.6
121105.7
131205.8
141305.9
151405.10
161505.11
1716116.1
1817117.1
1918118.1

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
O2=IF(M2>0,L2&".1",LEFT(O1,FIND(".",O1))&MID(O1,FIND(".",O1)+1,99)+1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Copy the formula down from O2. Let us know if any of these help.
 

Shales

Board Regular
Joined
Aug 8, 2006
Messages
168

ADVERTISEMENT

To DILIPandey

Rank(L) is just a running count but it's purpose is to mimic a "Rank" formula in another sheet. In this case, numbers 6 through to 16 will always be zero because "5" has a count of 11. The next time a number will be in use is 16.
 

Shales

Board Regular
Joined
Aug 8, 2006
Messages
168
To Eric,

This works exactly how I needed it to! Thank you, and thanks to everyone for taking their time out to help me.

Regards
Gavin

Actually, I had to change the "." to "-" in all occurrences because with a "." it will treat 5.1 and 5.10 the same. But this is great!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top