Counting Numbers

  • Thread starter Thread starter Steve
  • Start date Start date
Good morning Claus and thanks again for the suggested formula and UDF.

The formula

=IFERROR(CHOOSE(LEFT(JY5540,IF(LEN(JY5540)-LEN(SUBSTITUTE(JY5540,"-",))=0,1,FIND("-",JY5540)-1)),5,3,2,1,1),0)+IFERROR(IF(LEN(JY5540)>1,CHOOSE(MID(JY5540,FIND("-",JY5540)+1,IF(LEN(JY5540)-LEN(SUBSTITUTE(JY5540,"-",))=1,1,FIND("#",SUBSTITUTE(JY5540,"-","#",2))-FIND("-",JY5540)-1)),5,3,2,1,1),0),0)

Seems to work perfectly until it comes across numbers greater than 5 .
For example, if the number was as follows:

12-12 - it returns a value of 10 having taken the first number from
each string as a 1 and counting that as a five twice. In actual fact
12-12 should return a value of zero.

Would it be possible to advise how to amend the formula to ignore any
value above 5 and any other texts values that may appear in the
string, for example PU, F, UR etc all may appear at some point in the
future.

I have updated the spreadsheet with examples of this and hope you can
help.

https://onedrive.live.com/redir?resid=CB2CB4829572A60A!316&authkey=!ACtAogKt7FPYPjY&ithint=file,xlsm

Thanks in advance


Steve
 
Hi Steve,

Am Mon, 20 Oct 2014 10:31:45 +0100 schrieb Steve:
Seems to work perfectly until it comes across numbers greater than 5 .
Click to expand...

have another look in OneDrive


Regards
Claus B.
 
Hi Claus, thanks for the quick reply.

That seems to work perfectly with the only exception being where there
is only one value in the cell for example

14

12
29

etc

In that case the UDF shows a #VALUE error and the formula calculates
using the first numebr so in the example above that would be
calculated as

14 = 5

12 = 5

29 = 3

I have updated the spreadsheet with examples

https://onedrive.live.com/redir?resid=CB2CB4829572A60A!318&authkey=!AC07Sa7i3nIJuTs&ithint=file,xlsm

Thanks for your patience Claus !

Kind regards


Steve
 
Hi Steve,

Am Mon, 20 Oct 2014 11:43:42 +0100 schrieb Steve:
That seems to work perfectly with the only exception being where there
is only one value in the cell for example
Click to expand...

I fixed that issue
Have another look.


Regards
Claus B.
 
Hi Steve,

Am Mon, 20 Oct 2014 12:21:58 +0100 schrieb Steve:
Hi Claus, again the UDF works perfectly but for one minor instance and
that is where there is a single number in the cell between 6 and 9
which throws up a #value error.
Click to expand...

have another look ;-)


Regards
Claus B.
 
Back
Top