Having trouble with OFFSET Function

Hi there,

I'm new to this forum so forgive me if I'm not clear on protocols. I'm having problems with the OFFSET function, I think--Excel won't give me a REF! or VALUE#! Error, it just keeps erasing my function.

I have a data sheet called "colored data" with measurement locations across Row 1 and dates in Column A. For each location there are two columns--one with real data, one with modeled data. For example, for the first location, the real data is in Column C and the modeled data is in Column D. What I'm trying to do is figure out the percent difference between the modeled and real at the peak (max of modeled data) during specific periods (eg, nov-feb 1982). I've written the formula below in a new sheet in the same book, but it's certainly not working. If any one can help i'd be extremely grateful.

=((MAX('Colored Data'!D2710:D2801))-(OFFSET((MAX('Colored Data'!D2710:D2801)),0,-1,1,1)))/(MAX('Colored Data'!D2710:D2801))

I figured out the root of my problem with the OFFSET function:

I want Excel to find the maximum value in a range and then use that cell as a reference in the OFFSET function. However, the function MAX returns a value, not a cell reference. Any ideas?

Hello brady,

you don't need OFFSET for this, try:

=1-INDEX('Colored Data'!C2710:C2801,MATCH(MAX('Colored Data'!D2710:D2801),'Colored Data'!D2710:D2801,0))/MAX('Colored Data'!D2710:D2801)

Hey there, thanks that does work! I tried to puzzle it out but had virtually given up...