Forum Discussion

Copper Contributor

Feb 23, 2018

Excel 2007 Problem with an IF statement . Help

Hi Folks, I'd appreciate any help and many thanks in advance for taking the time. I've excel 2007 and I'm trying to built a table . column A will contain a number of random figures from -1 t...

Formulas & Functions

IF statement excel 2007

Willy Lau

Iron Contributor

Feb 24, 2018

"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
Copper Contributor
Feb 24, 2018
Column A blank B. 1 C. 1-5 D . 5-10 E. 10-20 F. 20-30 G. 30-40 H.

3.5 3.5

1 1

10.7 10.7

2.4 2.4

22.3 22.3

35.6 35.6

Above is how I want it to look .

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 .

I hope that helps
- Willy Lau
  Iron Contributor
  Feb 24, 2018
  Column C
  
  =IF($A2 = $C$1, $A2, "")
  
  Column D to last Column
  
  =IF(AND($A2>VALUE(LEFT(D$1, FIND("-", D$1)-1)), $A2<=VALUE(MID(D$1, FIND("-", D$1)+1, 255))), $A2, "")
  - Stephen Jon
    Copper Contributor
    Feb 24, 2018
    Hi Willy thank you I really appreciate the help .
    
    For Column C nothing appears with that code
    
    For the other columns hashtag value! appears . sorry I don't have a hashtag symbol
    
    Regards
    
    Stephen