I have no idea what I am doing wrong. I have a line of cells horrizontally that count down from 10. Then I have another line of cells underneath that I want to take the difference between takes the difference of the cell above it with 19 plus a modifier. If the difference plus the modifier is greater than 20, I want it to return 20. The modifier is an integer between -3 and +3 or a blank. If the modifier is blank, I want it to be treated as if it was a "0". So I wrote the formula:
=IF((19-E$42+(IFERROR($H$17,0)))>20,20,19-E$42+(IFERROR($H$17,0)))
E$42 is the horizontal line. $H$17 is the modifier. I first tried without the iferror function, but it would give me an error when the modifier was blank. I then put the iferror function in, and I still get an error when the modifier is blank. I then tried playing around with parenthesis and I still get an error. I was hoping someone could tell me if I am using the function wrong, or if I should be using another function altogether.
Thank you for your time.
Last edited by janschepens; 10-21-2011 at 05:09 PM.
The IFERROR function checks if something is an error and, if it is, it returns the specified value.
Cell H17 is *not* an error, just because it's blank, but if you try to add it to a numeric value *that* operation will return an error.
You can correct your formula by attempting to add 0 to H17, which will not change it's value if it's numeric, but will cause an error if it's blank [as the result of a formula in that cell]
So: =IF((19-E$42+(IFERROR($H$17+0,0)))>20,20,19-E$42+(IFERROR($H$17+0,0)))
Hope this helps.
Edited to add: Or, if you prefer =MIN(19-E$42+IFERROR($H$17+0,0),20)
Thank you for helping me understand and for giving me good advice on how I can make my formula better.
--
You could use
N($H$17)
N extracts any numerical value from H17, otherwise it returns 0...so a blank H17 will give you zero........also you can use MIN function to avoid the IF, i.e.
=MIN(19-E$42+N($H$17),20)
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks