Excel Questions

[Treasurer]

Right, in a list I want it to count y, I have tried to do count (y) and a few others but it wont work, I blame user error  . I up graded to 2007 a while ago and a few things have changed

Use countif, ie countif(range,criteria), put the criteria in inverted commas if it's text you're looking at, eg

=COUNTIF(A1:A6,"dog")

So it's =countif(A1:A25,"y") or =countif(A1:A25,"*y*").  That'll count the number of cells with a y in it.  It won't count the total number of ys in the range though (if there are more than two in any cell).

OOh good catch, I wasn't thinking of "y" being part of a piece text.

(I'm easily entertained really)

[Billys Boots]

Yeah, but how would you count the number of 'y's in the text in one or a number of cells, if that was your inclination?

[Treasurer]

Yeah, but how would you count the number of 'y's in the text in one or a number of cells, if that was your inclination?

I give up!  Grrrrrrrr

[Billys Boots]

I dunno either T, I was just asking.  I guess it can be done if it needed to be.

[Treasurer]

I dunno either T, I was just asking.  I guess it can be done if it needed to be.

I could do it a very long-winded way with vba but not as a straightforward excel function.

I found a long solution online that used functions I've never touched, but I couldn't even get that to work. 

[Billys Boots]

I'm teaching myself vba at the moment - any tips??

[Treasurer]

I'm teaching myself vba at the moment - any tips??

None! I only know bits and pieces I picked up over the years with access.  I went on a forum once looking for assistance and was taken under the wing of an access/vba pro who gave me lots of bits of code etc to use and modify, but I'd be very weak at it really.  Always meant to get deeper into it, but don't really have the same need for it any more so the push isn't there to do it.

[thebigfella]

I dunno either T, I was just asking.  I guess it can be done if it needed to be.

I could do it a very long-winded way with vba but not as a straightforward excel function.

I found a long solution online that used functions I've never touched, but I couldn't even get that to work.

Not very efficient, write a function to return the number of y's for a given cell & then use the newly defined function in a subroutine (or a function that returns a total count) that iterates though all cells in a given range updating a count variable after every iteration.

Probably easier ways within excel itself and cleverer search algorithms to use but it's probably around 5 lines of code.

[thebigfella]

Try this, subsituting the range you want to search,

=SUMPRODUCT((LEN(A14:F18)-(LEN(SUBSTITUTE(A14:F18,"y",""))))/LEN("y"))

Think that should be right or close enough.

[donelli]

I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.

Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...

[LeoMc]

I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.

Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...

Don't have Excel on this computer but off  the top of my head a COUNTIF function could probably do this.  It could get quite "wordy" though.

[bud]

hello...hope someone can help me here

I'm creating a pivot table on the data below

Date              County               Number
01/01            Armagh                   1
01/01            Armagh                   1
01/01            Armagh                   1
01/01            Down                     2
01/01            Down                     2
02/01            Armagh                   3
02/01            Armagh                   3

I want the pivot table to look like this below with the number in the middle, however line 1,2 and 3 above are duplciates and i only want them included once, same with line 4 and 5...can this be done?

                   County
Date

[tyssam5]

Where the hell has the solver function gone in Excel 2007? just upgraded to that the other day, parts of it are good, but the getting used to it process is highly frustrating.

[Treasurer]

I am involved with the clubs lotto. I have all the details of our annual lotto members on excel (about 300).
we pay out for match 3/4 and 4 also.

Looking advice on functions for a spreadsheet that would pick out our the match 3s or 4s after entering the 4 numbers of that week, and perhaps highlight the winners..
appreciate any help to make this a bit easier...

I have exactly that.  There's probably a shorter way of doing it than how I did it, but it works.  Will dig it out for you tomorrow.

[Caid]

hello...hope someone can help me here

I'm creating a pivot table on the data below

Date              County               Number
01/01            Armagh                   1
01/01            Armagh                   1
01/01            Armagh                   1
01/01            Down                     2
01/01            Down                     2
02/01            Armagh                   3
02/01            Armagh                   3

I want the pivot table to look like this below with the number in the middle, however line 1,2 and 3 above are duplciates and i only want them included once, same with line 4 and 5...can this be done?

                   County
Date

If you go into the pivot table wizard you will see a section where you can drag the data types (date, county, number) into either the column, row or data sections.  You need date in the column, county in the row and drag/drop the numnber into the data field.  Double ckick on the data field and select average (although min or max will work in this instance).  When you click ok you may need to drag the county column up and drop on the row toolbar.

Sorry this might not be clear.  HArd to explain