1. Registered User
Join Date
Feb 2013
Posts
1

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. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
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.