Counting Numbers

S

Steve

Can anyone tell me how to adjust this formula so that it correctly
calculates the value.

So I have cells with 5 numbers each seperated by a hyphen (-) for
example

21-1-2-4-5

What I would like to do is calulate the total based on the formula
below

=IFERROR(CHOOSE(LEFT(FL5232,1),5,3,2,1,1),0)+IFERROR(CHOOSE(MID(FL5232,LEN(FL5232)-2,1),5,3,2,1,1),0)

However when I try in the example above instead of returning the value
of 5 it returns 8 as it looks at the first number of the first entry
21 and uses the 2 to return a value of three then uses the next number
1 to return a value of five and therefore a total of 8.

However, is there anyway to amend the formula so that it looks at the
complete number 21 and therefore returns a 0 as it is not a number
between 1 and 5 as per the formula.

Hope that makes sense and thanks in advance

Steve
 
C

Claus Busch

Hi Steve,

Am Sun, 19 Oct 2014 10:02:01 +0200 schrieb Claus Busch:
=LEN(TRIM(FL5232))-LEN(TRIM(SUBSTITUTE(FL5232,"-",)))+1
Click to expand...

TRIM is superfluous.
Try for 1 cell:

=LEN(FL5232)-LEN(SUBSTITUTE(FL5232,"-",))+1

If you want to count all numbers in the 5 cells then try:

=SUM(IF(ISBLANK(FL5232:FL5236),0,LEN(FL5232:FL5236)-LEN(SUBSTITUTE(FL5232:FL5236,"-",))+1))
and insert the array formula with CTRL+Shift+Enter


Regards
Claus B.
 
G

GS

Hi Steve,
Am Sun, 19 Oct 2014 10:02:01 +0200 schrieb Claus Busch:


TRIM is superfluous.
Try for 1 cell:

=LEN(FL5232)-LEN(SUBSTITUTE(FL5232,"-",))+1

If you want to count all numbers in the 5 cells then try:

=SUM(IF(ISBLANK(FL5232:FL5236),0,LEN(FL5232:FL5236)-LEN(SUBSTITUTE(FL5232:FL5236,"-",))+1))
and insert the array formula with CTRL+Shift+Enter


Regards
Claus B.
Click to expand...

What if...

21-22-19-32-7-45

...is the combo?


Public Function GetNumsCount&(Ptr As Range, Delim As String)
Application.Volatile
GetNumsCount = UBound(Split(Ptr.value, Delim)) + 1
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
C

Claus Busch

Hi Garry,

Am Sun, 19 Oct 2014 06:11:06 -0400 schrieb GS:
What if...

21-22-19-32-7-45

..is the combo?
Click to expand...

what do you mean? My two formulas are working with trailing or leading
spaces also.


Regards
Claus B.
 
G

GS

Hi Garry,
Am Sun, 19 Oct 2014 06:11:06 -0400 schrieb GS:


what do you mean? My two formulas are working with trailing or
leading spaces also.


Regards
Claus B.
Click to expand...

Sorry.., I posted before testing your offering! My bad!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
C

Claus Busch

Hi Garry,

Am Sun, 19 Oct 2014 06:27:02 -0400 schrieb GS:
Sorry.., I posted before testing your offering! My bad!!!
Click to expand...

never mind. But I thought you found another mistake into the formula


Regards
Claus B.
 
G

GS

Hi Garry,
Am Sun, 19 Oct 2014 06:27:02 -0400 schrieb GS:


never mind. But I thought you found another mistake into the formula


Regards
Claus B.
Click to expand...

Actually, your formula does exactly what my VBA function does and so
I've attached a note in that function so I can use your formula instead
where appropriate, and so I thank you for that. (I added
Application.Volatile for this thread, but the function is part of my
'StringFunctions' library)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
S

Steve

Thanks a lot Claus and Garry for your suggestions but I think I have
slightly misled you

The figures are in each cell as follows:

Cell FL2323 contains 21-12-1-4-5 formatted as text

The formula I was hoping to use was

=IFERROR(CHOOSE(LEFT(FL5232,1),5,3,2,1,1),0)+IFERROR(CHOOSE(MID(FL5232,LEN(FL5232)-2,1),5,3,2,1,1),0)

to give me a value for each cell.

As the numbers will be in one cell and formatted as text I need the
formula to count the first two numbers from the left only (perhaps
extended to more at a later date). So in essence in the above example
the answer in cell HA2323 would be 0 as none of the first two numbers
are 5,4,3,2 or 1

Some cells will have no entries so perhaps I need an iferror statement
although i am not sure about that.

Hope that clarifies

Thanks again

Steve
 
C

Claus Busch

Hi Steve,

Am Sun, 19 Oct 2014 12:25:02 +0100 schrieb Steve:
As the numbers will be in one cell and formatted as text I need the
formula to count the first two numbers from the left only (perhaps
extended to more at a later date). So in essence in the above example
the answer in cell HA2323 would be 0 as none of the first two numbers
are 5,4,3,2 or 1
Click to expand...

try:

=IFERROR(OR(--LEFT(FL5232,FIND("-",FL5232)-1)={1,2,3,4,5})+OR(MID(FL5232,FIND("-",FL5232)+1,FIND("#",SUBSTITUTE(FL5232,"-","#",2))-FIND("-",FL5232)-1)={1,2,3,4,5}),"")


Regards
Claus B.
 
S

Steve

Thanks Claus, I am just heading out but will check and report badck as
soon as i return

Thanks very much

Steve
 
G

GS

The following UDFs allow you to optionally pass how deep to go in the
search. Leaving this out searches the entire string.

This assumes a MaxVal is specified where the search only counts values
greater than MaxVal...


Public Function GetNumsCount2&(Ptr As Range, MaxVal&, _
Delim$, Optional MaxPos&)
Application.Volatile
Dim vTmp, n&, lPos&
vTmp = Split(Ptr, Delim)
lPos = IIf(MaxPos > 0, MaxPos - 1, UBound(vTmp))
For n = LBound(vTmp) To lPos
If vTmp(n) > MaxVal Then GetNumsCount2 = GetNumsCount2 + 1
Next 'n
End Function


This assumes a ref to a cell where a delimited list of the values to
exclude from the count are stored. This allows counting negative
numbers and zeros if not in the exclusion list...


Public Function GetNumsCount3&(Ptr As Range, Xclds As Range, _
Delim$, Optional MaxPos&)
Application.Volatile
Dim vTmp, n&, vExcl, lPos&
vTmp = Split(Ptr, Delim)
lPos = IIf(MaxPos > 0, MaxPos - 1, UBound(vTmp))
For n = LBound(vTmp) To lPos
If Not InStr(1, Xclds, vTmp(n)) > 0 Then _
GetNumsCount3 = GetNumsCount3 + 1
Next 'n
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
G

GS

This should be obvious BUT...

If your search cell will include negative numbers then you MUST NOT use
a hyphen for the delimiter if you go with GetNumsCount3. (I used a
space character in all tests because I tend to avoid using Formula
Operator symbols as standard practice)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
C

Claus Busch

Hi again,

Am Sun, 19 Oct 2014 14:04:15 +0100 schrieb Steve:
I will check and report badck as soon as i return
Click to expand...

or with a VLookUp

=IFERROR(VLOOKUP(--LEFT(A1,FIND("-",A1)-1),{1,5;2,3;3,2;4,1;5,1;6,0},2,1)+VLOOKUP(--MID(A1,FIND("-",A1)+1,FIND("#",SUBSTITUTE(A1,"-","#",2))-FIND("-",A1)-1),{1,5;2,3;3,2;4,1;5,1;6,0},2,1),"")



Regards
Claus B.
 
S

Steve

Firstly, thanks to Claus, Garry and Ron for their input, your
willinness to help is very much appreciated.

Claus, the formula above is perfect when there is five values to
calculate, however, I have done some back calculations and come across
some entries where there are less than five values to calculate. Sorry
but I didnt realise this was the case. I have upated the spreadsheet
on the OneDrive to show an example and wondered if the formula could
be amended slightly to take account of ocassions where there are less
than five values in the cell to be calculated.

https://onedrive.live.com/redir?resid=CB2CB4829572A60A!313&authkey=!AIExsNKa-O1U14M&ithint=file,xlsm

Thanks in advance

Steve
 
Z

Zaidy036

Can anyone tell me how to adjust this formula so that it correctly
calculates the value.

So I have cells with 5 numbers each seperated by a hyphen (-) for
example

21-1-2-4-5

What I would like to do is calulate the total based on the formula
below

=IFERROR(CHOOSE(LEFT(FL5232,1),5,3,2,1,1),0)+IFERROR(CHOOSE(MID(FL5232,LEN(FL5232)-2,1),5,3,2,1,1),0)

However when I try in the example above instead of returning the value
of 5 it returns 8 as it looks at the first number of the first entry
21 and uses the 2 to return a value of three then uses the next number
1 to return a value of five and therefore a total of 8.

However, is there anyway to amend the formula so that it looks at the
complete number 21 and therefore returns a 0 as it is not a number
between 1 and 5 as per the formula.

Hope that makes sense and thanks in advance

Steve
Click to expand...
why not count the hyphens and add 1 ?
 
S

Steve

Zaidy its not the hyphens I am counting its the values

21-2-3-4-5

only value 1-5 count and on the basis that a 1 counts as 5 a 2 counts
as 3 etc

Thanks for your input though

Cheers

Steve
 
C

Claus Busch

Hi Steve,

Am Mon, 20 Oct 2014 06:41:47 +0200 schrieb Claus Busch:
=IFERROR(CHOOSE(LEFT(A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2,"-","#"))=0,1,FIND("-",A2)-1)),5,3,2,1,1),0)+IFERROR(CHOOSE(MID(A2,FIND("-",A2)+1,IF(LEN(A2)-LEN(SUBSTITUTE(A2,"-",))=1,1,FIND("#",SUBSTITUTE(A2,"-","#",2))-FIND("-",A2)-1)),5,3,2,1,1),0)
or
=VLOOKUP(--LEFT(A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2,"-",))=0,1,FIND("-",A2)-1)),{1,5;2,3;3,2;4,1;5,1;6,0},2,1)+IFERROR(VLOOKUP(--MID(A2,FIND("-",A2)+1,IF(LEN(A2)-LEN(SUBSTITUTE(A2,"-",))=1,1,FIND("#",SUBSTITUTE(A2,"-","#",2))-FIND("-",A2)-1)),{1,5;2,3;3,2;4,1;5,1;6,0},2,1),0)
Click to expand...

have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "CountNumbers"
In this workbook is another suggestion with a UDF


Regards
Claus B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP Question 27
VLOOKUP Problem in EXCEL 2010 3
Excel Subtracting multiple columns from another column 3
Pull a column header from a row of numbers 1
help with vba loop 3
Counting years 2
Another Counting Question 4
Counting Cell Background Colours 18

Top