Excel 2007 Problem with an IF statement . Help

Question

Hi Folks, I'd appreciate any help and many thanks in advance for taking the time.
&nbsp;
I've excel 2007 and I'm trying to built a table .&nbsp;
column A will contain a number of random figures from -1 to 100.
&nbsp;
The table starting in&nbsp;column C through to column AB will run&nbsp;&nbsp;parallel&nbsp;
and each column being a category&nbsp; 1, 2-5, 6-10, 11-20, 21-30 and so on&nbsp;
&nbsp;
below is the formula that when placed&nbsp; in C2 will show the result of 1 if 1 is in that cell
=IF(ISNUMBER(SEARCH("1",A2)),A2,"")
&nbsp;
the problem I have is how do I formulate code where i need&nbsp; a range from 2-5 ?
so if 3.3 turns up in cell A2 3.3 will show in the category 1-5
&nbsp;
I can only do single digits and I need to be able to cover a range of numbers and those in between up to 2 decimal place
&nbsp;
&nbsp;
&nbsp;

stephen jon · Answer

I hope I've explained it correctly , if not please message so I can help clarify

willy lau · Answer

"each column being a category  1, 2-5, 6-10, 11-20, 21-30 and so on"vs"so if 3.3 turns up in cell A2 3.3 will show in the category 1-5"Is "1" a category, or "1-5" is a category?

stephen jon · Answer

Column A&nbsp; &nbsp;blank B.&nbsp; &nbsp;1 C.&nbsp; &nbsp; 1-5 D .&nbsp; 5-10 E.&nbsp; 10-20 F.&nbsp; &nbsp;20-30 G.&nbsp; &nbsp; 30-40 H.&nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; 3.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3.5
&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1
&nbsp; &nbsp; &nbsp; 10.7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.7
&nbsp; &nbsp; &nbsp; 2.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2.4
&nbsp; &nbsp; &nbsp; 22.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;22.3
&nbsp; &nbsp; &nbsp; 35.6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;35.6
&nbsp;
&nbsp; &nbsp; &nbsp;
Above is how I want it to look .&nbsp;
Column A are random numbers anything from -1 up to 100
As the random numbers are inputted to column A , it automatically appears in the table under the right category and in line with the relevant data input on the same line .
&nbsp;
I hope that helps&nbsp;
&nbsp;

willy lau · Answer

Column C
=IF($A2 = $C$1, $A2, "")
&nbsp;
Column D to last Column
=IF(AND($A2&gt;VALUE(LEFT(D$1, FIND("-", D$1)-1)), $A2&lt;=VALUE(MID(D$1, FIND("-", D$1)+1, 255))), $A2, "")
&nbsp;

stephen jon · Answer

Hi Willy thank you I really appreciate the help .
&nbsp;
For Column C&nbsp; nothing appears with that code
&nbsp;
For the other columns&nbsp;hashtag value! appears . sorry&nbsp;I don't have a hashtag symbol
&nbsp;
Regards&nbsp;
&nbsp;
Stephen

Forum Discussion

Excel 2007 Problem with an IF statement . Help

7 Replies

Resources