Thread: If Statement Wrong?
02-28-13, 16:06 #1Registered User
- Join Date
- Feb 2013
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.
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.
03-01-13, 05:52 #2Grumpy old man (training)
Provided Answers: 10
- Join Date
- Sep 2006
- Surrey, UK
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)
IF(ISERROR(VLOOKUP(G26, TransValues, 2, False)), 0, VLOOKUP(G26, TransValues, 2, False))
IFERROR(VLOOKUP(G26, TransValues, 2, False), 0)
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.
Beers earned: 1