PDA

View Full Version : Solved: Offset Function Question



mbake16
03-23-2009, 07:17 AM
Hello:

In column I of the attached file is an example of the way I'm using the Offset function in a ranking report I'm creating. The formula is to return nothing or "" if the offset cell contains nothing. If the offset cell contains a value the formula performs a division calculation. The formula works great if there is a value in the offset cell, however it returns a #VALUE! error if the offset cell is blank. Is there something I'm doing incorrectly that is causing the #VALUE! to be returned for empty cells? Any suggestions would be great!

Thanks,

Matt

Bob Phillips
03-23-2009, 07:27 AM
Does this work

=IF(OFFSET(I6,0,-7)=0,"",OFFSET(I6,0,-5)/$D$495*100+N(OFFSET(I6,-1,0)))

mbake16
03-23-2009, 09:58 AM
First of all, thank you very much. It works! I see two differences: You used "0" instead of "" for the If function logical test value. You also used the N function at the end of the formula. In your formula I changed the "0" to "" for the If function logical test and the formula still works correctly, which leads me to believe Excel did not like the 2nd half of my formula while your use of the N function made the difference. I'm trying to understand why the N function allowed the formula to work properly when referencing blank cells while my formula returned #VALUE!. Hate to push my luck here but wondering if you could help me understand? Thank you again for your help!

Bob Phillips
03-23-2009, 10:01 AM
That bit of the formula looks at the previous row. In the first data row it gets a value from the previous row, but as that is a heading it is text, so using N coerces that text to a number (0).

mbake16
03-23-2009, 12:53 PM
Awesome! I'll keep that function handy for future situations that call for it. Thank you again for your help today... and for the tip!


Matt