Excel - return a value if a cell contains a certain number 2

[dsmith910]

Hi

I need to enter a formula which basically asks if one cell contains a number (i.e. 2) then if the answer is yes returns the content of another cell. something like if A1 contains 2 then I1 else 0. Dratted if I can make it work - guess I must be missing out a comma or something - but which one? or am I no allowed to use the word 'contains' should it be something else?

Any ideas?

Doreen

 

[carrr]

Try something like this...

=IF(A1=1,D1,E1)

Basically, if A1 is equal to one, then you'll get the value found in D1, if not (else) you'll get the value found in E1. You could replace E1 in the formula with 0 or "no" or whatever suits your scenario.

 

[dsmith910]

Hi

Not quite what I need. The field would contain four figures ranging between 1 and 6, i.e. it might read 1346.
I need the formula to look to see if the cell contains say, a "4" and if it does return the value of another field.

At the moment I have 6 fields. I put a cross in the two which aren't being used then the formula is pretty much as you suggest, i.e. that if A1 = x then 0 else A2. This works ok but its a pain since I have to carefully put a cross in the cells of the numbers of don't require. It would be much quicker and easier if I could simply type in one box the valid numbers - a choice of 1-6, but this will only work if Excel can look for part of a number in a cell and return a value if its present.

Life's never easy is it!

Doreen

 

[xlbo]

so what happens if the field contains more than 1 of the numbers??
you can interrogate a cell to find a specific character (or set of characters) using the FIND function
eg if A1 contains 1346
then =find("3",A1,1)
would give a result of 2
so to TEST for a value being there, you can use
=if(ISERROR(find("3",A1,1)),"NOT FOUND","FOUND&quot

Can you give more detail on the "ideal" layout of the spreadsheet and an example of data that might appear and the result you would like to get ?? Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?

 

[carrr]

Oh...
...I think this might get you closer.

If A1 = 156497865 and B1 = "7"

then this...

=IF(ISNUMBER(FIND("8",A1)),B1,0)

will get you 7, 0 if you changed the 8 in the formula to say, a 2

 

[Hasit]

Something like this might help:

=IF((SEARCH(4,A1)>0),B1,B2)

The problem I am coming across is that if the value 4 is not found in A1, then #VALUE! pops up. So, I am working on it......

But in the mean time, that formula might get you started.

 

[dsmith910]

Hi Geoff

The spreadsheet is relatively simple - or else I wouldn't be doing it. Its purpose is to calculate points received by entrants in a series of relay races. Each team has its own sheet - a tournament might contain 24 teams. The results/points for each team are collected by a sheet at the front which returns all total values from the individual sheets.

The team sheet at the moment contains two sets of 6 columns - 2 for each member of the relay team. The first set of columns is headed 1,2,3,4,5,6 and these are used to indicate which team members ran in a certain race. At the moment a cross is entered into two columns to indicate non racers in that race.

Points are allocated according to the speed the team ran at. Therefore another column exists in which the speed is entered. A further column then shows the number of points each team member will receive for that race.

The next 6 columns are each calculated with a formula like =IF(F25="x",0,$M25) Thus the points for each team member are entered in the team members column - then each column is added to give a total no of points for that team member.

This works well but in truth with the number of races run x the number of teams racing its hard work concentrating and entering the right 'x' in the right box. It would be far easier if the team members were simply refered to as '1' or '4' or whatever and all the team numbers were entered into one cell, say 1345. The number should never be repeated since the team member will only run once and the number is used for only one team member, there are never more than 6 members to each team.

Does this make any sense - or have I managed to lose you or worse bore the socks off you?

Doreen

 

[xlbo]

Certainly sounds possible
Say your "Team Numbers" cell is A2 and contains 1345
Using your formula of:
=IF(F25="x",0,$M25), I will change to suit so it WOULD read
=IF(A2="x",0,$M25)
Now, to read the cell, you will still need 6 columns - 1 for each of the possible entrants
so, if entrants 1-6 are spread over cols C-H then in C2 enter:
=if(iserror(find("1",A2,1)),0,$M2) - or wherever you need to pick a value from
in D2:
=if(iserror(find("2",A2,1)),0,$M2)

etc etc
Does this give you the answer (or at least push you in the right direction) ?? Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?

 

[dsmith910]

Hi Geoff

Not sure whether this is exactly what's needed - am going away to try it out. Back soon.

Doreen

 

[dsmith910]

Hi Geoff

It does more than just push me in the right direction - it solves the problem! Fantastic! Can't thank you enough - it will save my poor old eyes - not to mention temper! as I enter the next lot of racing sheets.

thanks again

Doreen