0

In Excel, I am using an IF function and using the OFFSET function to assign a value in the [value_if_true] and using a 0 for the [value_if_false]. The true OFFSET function was occasionally returning #ref! and #value! errors because it was referencing cells off the worksheet, so I re-coded it in a way that the result would be FALSE [value_if_false] if the cell referenced in the [value_if_true] offset function would be off the sheet.

It seems like Excel doesn't just ignore the [value_if_true] offset function if the value is FALSE. It is still giving me the same errors.

In an IF function, is there a way to make Excel ignore the TRUE value's function and only post the FALSE value? I know my error is coming from the TRUE value's function because when I change only the TRUE value the error goes away.

This is my formula:

=IF(($G$4+$D$18)<L54,((OFFSET($B$55,0,(L54-$G$4-$D$18),,))*$F$4*$N$4),"")
  • My formula didn't past all the way: =IF(($G$4+$D$18)<L54,((OFFSET($B$55,0,(L54-$G$4-$D$18),,))*$F$4*$N$4),"") – jason444 Oct 11 '13 at 3:13
1

Use the IFERROR function.

 =IF(($G$4+$D$18)<L54,IFERROR(((OFFSET($B$55,0,(L54-$G$4-$D$18),,))*$F$4*$N$4),""),"")

You may be able to get rid of the first IF statement using this method... I am unsure what you are trying to do.

=IFERROR((OFFSET($B$55,0,(L54-$G$4-$D$18),,))*$F$4*$N$4,"")

The function will return the result in the formula unless there is an error and then the second statement is evaluated.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.