excel help please

assuming your first t is in cell A2, copy this formula into cell B2

=IF(A1="",1,IF(A2="t",B1+1,""))

then, copy and paste that into all cells that may or may not have a t in them.

the result is:

t 1

t 1
t 2

t 1
t 2
t 3

t 1
t 2
t 3
t 4

not quite what you're after but you get the right result.

There is no doubt a smarter way but this will work in simple form.

in column B in every cell - =IF(A3="t",(1+B2),0) (drag down to copy in each cell in column B).

now in column C in every cell - =IF(B3B4,"",B3) (again drag down to copy).

you can hide column B if you wish to.

the betfair forum does not like the "less than" operator. the second formula should read.

=IF(B3^B4,"",B3)

where the ^ symbol is replaced with the same thing turned 270 degrees, ie shift+comma

the formula is much the same thing as dibble above except it provides blanks if it is not a max count. if the blanks do not matter to you then use the one above for simplicity.

thanks guys but may be i did not explain properly, i have the t's in the column and i want the no.s in the next coulmn to be put there by the formula applied to the t column....in other words the colums with no.s is the answer i am looking for..

thanks again

i aint got a scooby

so in column A put your t's

in column B and C put the formulas above

then hide column B, (right click at top)

This should do what you are asking for.

follow the instructions from me or Jim and you have your answer.

jim your formuls gives value! errore when 2 consecutive t's appear...dibble your formula just counts the t's and dose not give the subtotal....sorry to be a pain but the solution has to look exact...thanks once again.....i have been trying to sole thus for the last three days...cheers

sorry. mine assumes your ist starts at row 3. if it starts at row 1, then change any cell refs to 1 and then copy downwards.

if you put your email up I will send it you done.

and tell me how many rows (roughly) that you want it over.

about 100 rows, as long as there are no other values apart from the subtotal that will be great....cheers jim

Where do you want me to send it?

Timbuktu - he was only kidding ;)

i would rather not give out my e-mail, as i receive enough spam

hey ho

right last go. change the ^ for a less than & the # for a greater than

- put your data list in column A starting at A2
- in cell B2 input the following =IF(A2="t",(1+B1),"")
- in cell c2 input the following =IF(B2^B3,"",B2)
- in cell d2 input the following =IF(C2#0,C2,"")
- copy all these cells by dragging down to row 100
- hide columns B & C
- hide row 1

good luck

you may need to ensure that your data in column A is formatted to text. depends on the version of XL. if this is the case just experiment.

formulas, cell references, subtotals........ all this excel talk is giving me the horn

=IF(SOCGAY,"nap",false)

;-)

hi jim,

was not working until i put 0 adjancent to the blanks in column A....i was messing about and the formula in b2 changed to this wiorks =IF(A2="t",(1+B1),)...ithus i am very happy....I can't thank you enough....thank you...much appreciated

jimmy

hi jim,

i am having problems doing a simply sum of the no.s...if i type in the no.s in a column and do sum it works fine but it does not work when i try to sum the calculated figures....any ideas

My formulas on the betting forum will sum.

I guess this is the same sheet?

if there are gaps in the "list" then just pressing he sum button wont work properly. highlight ALL the data you want to sum and press the button, alternatively type in

=sum(d2:d102)

where the cell refs are the top and bottom of your list.


It may also be that you have text values within the list, in which case you need to either remove them or do an IF statement to transpose only numbers (greater than 0.1) into another column and sum that.

compound i can't get your formula to work i get the following

A B AA
t 1 1

t 2
t 2 2
0

t 26 #VALUE!

t #VALUE!
t #VALUE!
t 26 #VALUE!

t 26 #VALUE!
0
0

t #VALUE!
t #VALUE!
t #VALUE!
t 26 #VALUE!
0

t 26 #VALUE!
0

t 26 #VALUE!
0

t 26 #VALUE!
0

t #VALUE!
t #VALUE!
t 26 #VALUE!

t #VALUE!
t 26 #VALUE!
0

t #VALUE!
t #VALUE!
t #VALUE!
t #VALUE!
t 26 #VALUE!

t #VALUE!
t #VALUE!
t #VALUE!
t 26 #VALUE!

could you have look please

tjm yes same sheet...no text values....sum wants to perform a sum along the row as opposed to the column in the calculated values
a b
t 1

t
t 2


t 1

t
t
t 3

t 1



t
t
t
t 4

i want to sum column b....i can do this if i type in the no.s in the column...but it won't sum when the numbers have been claculated using jim's formula...its very weird....i must be missing something simple

It works fine for me, as does compounds' formula. sum is the simplest of things in excel, and if it is not working it is usually something else you have done usually formatting or rogue values in the number strings. A forum is unlikely to help you pick it out tbh. either go back and apply either of the forumula exactly in a brand new worksheet, or get a basic book and look at where you are going wrong. It could be one of many things that have caused your data not to sum, and no one is going to be able to second guess easily over the internet I'm afraid.

Try a brand new sheet, and input the formula alongside a set of manually input "t's". If this works, copy the formula over to your worksheet and if it does not work there then the error, (as I suspect), is in your base data and or formatting.

make sure all your numbers are set to numbers format. they should also work set to general, but wont for text iirc.


as above open a brand new sheet and get this bit right then cut and paste it into your sheet, where I suspect you have gotten allsorts of formatting and possible rogue data going on.

alternatively, using column A use

=COUNTIF(A2:A100,"t")


this will do the same thing.

tjm thank you...i have got it to work...you have all been a great help much appreciated

hi guys....looking for a little more help...is there any way (new problem) I can get rid of the blanks in the column so that it just shows the no.'s in consecutive rows

e.g
1

2


3

7

2

to show as
1
2
3
7
2

any help much appreciated

click on the column letter with the numbers and blank spaces you want to hide, select data-filter-autofilter

then in the drop down filter that appears scroll to the bottom and select nonblanks

To think this time next year, you will have confirmed that your "super-hunch-cannot-fail-gambling-system" does in fact not work, but you will be able to use excel quite well and can catalogue your CD's whilst reminiscing about the system that almost worked. ;-)

Only jesting, good luck.

thanks six of clubs..that partially sorts out the problem, but it filters out all the information from the other columns....ideally it would just filter that column...maybe need to do it in another worksheet and link it....

yes jim I have this fail safe system "lol".....its a great way of learning excel...just to give you an idea of the challenge i face...i am at column JD and only 50% complete...regarding rows...1000's...

thanks for your help...if i do get the holy grail you won't be forgotten!!

lol good luck.

cut and paste the data out into a new sheet. sort, then paste back.

this may disrupt some forumula though as it sounds like you have data running alongside the list that will be affected.

If so, move the list to a different tab and set links to that tab. you may need to look up the use of $ prefixes, (f4). hard to say step by step as can't see it, but I think this is where you need to be looking/thinking.

There's some clever fkkers on this forum. I have trouble changing a lightbulb :(

thanks jim will try that...

Don't you still have gas lamps in your neck of the woods ;-)

Just a thought, you may do well to step out of your work and go play with a few more advanced features. Handling data that size is prone to errors imo.

custom views, custom drop downs, tracking and auditing will probly be useful. try and learn some of this in a smaller sheet, and think about using it to simplify the big task. Usually you would want to avoid this stuff and keep it simple, but I think that amount of data will either lead to data errors, or most likely hitting a brick wall when your sheet design becomes too rigid for your next step.

Also make regular version changes and notes of where you are upto. you will likely quit this and return to it at some point in the future and those notes and versions will be very useful. Try to keep data sets seperate and stage various parts of the operation on diff tabs so as to allow flexibility down the line to do revisions.

PS - a good size excel sheet is when it is full and you have to load another book ;-)

filter first like Six of Clubs instructed, copy whilst filtered entire column from first occurrence
then turn the filter off and then paste special, values only.