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

[Shales]

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

 

[DILIPandey]

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

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

Regards,
DILIPandey

 

[Gerald Higgins]

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]

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]

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

 

[Eric W]

There seems to be some difference of opinion as to what you're actually looking for, so I'll throw in my version:

	L	M	N	O
1	Rank	Count		Lookup
2	1	1		1.1
3	2	1		2.1
4	3	1		3.1
5	4	1		4.1
6	5	11		5.1
7	6	0		5.2
8	7	0		5.3
9	8	0		5.4
10	9	0		5.5
11	10	0		5.6
12	11	0		5.7
13	12	0		5.8
14	13	0		5.9
15	14	0		5.10
16	15	0		5.11
17	16	1		16.1
18	17	1		17.1
19	18	1		18.1

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

Sheet1​

Worksheet Formulas Cell Formula 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]

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]

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!

 

[Eric W]

Glad we could help.