Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013
    Posts
    1

    Unanswered: If Statement Wrong?

    I have a spreadsheet where I want cell b34 to tell me if the value of cell g29 is greater than 1100. Cell g29 contains the following if statement.

    =IF(G26=11,"530",
    IF(G26=12,"590",
    IF(G26=13,640,
    IF(G26=14,690,
    IF(G26=15,740,
    IF(G26=16,790,
    IF(G26=17,830,
    IF(G26=18,870,
    IF(G26=19,910,
    IF(G26=20,950,
    IF(G26=21,990,
    IF(G26=22,1030,
    IF(G26=23,1070,
    IF(G26=24,1100,
    IF(G26=25,1150,
    IF(G26=26,1190,
    IF(G26=27,1220,
    IF(G26=28,1260,
    IF(G26=29,1300,
    IF(G26=30,1340,
    IF(G26=31,1380,
    IF(G26=32,1420,
    IF(G26=33,1460,
    IF(G26=34,1510,
    IF(G26=35,1560,
    IF(G26=36,1600,"0")
    )))))))))))))))))))))))))

    What cell g29 displays is of course dependent on what cell g26 is. My issue is that g29 is being read as containing all of the values in the if statement. So even if g26 is 11 which would make the displayed value in g29 530, b34 is telling me than cell g29 is greater than 1100 since (technically) it does contain values greater than 1100. Does anyone know how I can get around this? The value display as a result of the if function is what I want the cell to go by, not all the values in the if statement.

    Any suggestions???

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I would move the potential values of G26 to another part of the sheet (or another sheet), along with their translated values, and name the table. Then I would replace the nested IF statement with a VLOOKUP statement:
    (XL < 2007)
    Code:
    IF(ISERROR(VLOOKUP(G26, TransValues, 2, False)), 0, VLOOKUP(G26, TransValues, 2, False))
    (XL >= 2007)
    Code:
    IFERROR(VLOOKUP(G26, TransValues, 2, False), 0)
    Replace "TransValues" with whatever you call the table of values. This will be easier to amend later.

    I would also check the formula in B34, as formulae are supposed to be evaluated based on the Value property of their target cells, not their Text or Formula properties.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •