Define Average Range with Variable, Syntax Issue?

Hi,
In my macro, I am trying to insert an "IF" function into a cell that will average 2 rows if the difference between two cells is "> -2" and average a different two rows if the difference is not ">-2". The cells for which the difference is calculated as well as the ranges that need to be averaged are specified by variables within the macro. The pertinent portion of my macro is:

Please Login or Register  to view this content.

where,

LastRowWithData is an Integer
TimeClosestToCoolDownLocation is a String (address of cell)
CoolDownStartTimeSecondsLocation is a String (address of cell)
RowClosestToCoolDownTimeInSec is a String (this is a row number determined using an array function earlier in the macro)

Right now, as the macro is written, the following gets inserted into the cell:

=IF(R120C2 - R84C2>-2,AVERAGE("R84C2:R83C2"),AVERAGE("R83C2:R82C2") , which is close to what I need (i.e., the cell addresses and ranges are correct), except for the extra quotes around the two ranges within the AVERAGE function. Because of the extra quotes, I get a "#VALUE!" error in the cell the function is inserted into.

I realize that this is because I have double quotes around the ranges in the actual macro vba code, but if I use only single quotes around the ranges, I get a "Compile error: Expected: end of statement" at the first R (for Row) in the first AVERAGE function. If I remove the quotes all together from around the ranges in the AVERAGE functions, the macro runs, but I get a "Run-Time error '1004': Application-defined or object-defined error".

If anybody can help show me what I am doing wrong, it would be greatly appreciated.

Thank you for the help.

Hi shg,
Wow! Thank you for the quick reply. I cut n' pasted your code into my macro, but I still get a Run time error '1004': Application-defined or object-defined error with the changes.

A

Hi Shg,
Unfortunately, when I enter the formula (your version from above) as a string, it does not work. The following gets inserted:

IF($B$120 - $B$84>-2,AVERAGE(R84C2:R83C2),AVERAGE(R83C2:R82C2));

When I add the equal sign in front of the "IF" I receive the "The formula you typed contains an error." pop-up window because of the "$B$120 - $B$84". If I change the "$B$120 - $B$84" within the equation to "R120C2 - R84C2", the equation works perfectly!

For simplicity, I have attached a copy of the excel file I am using to write the macro (macro embedded). This portion of the macro is at the very end under the comment "'CALCULATING 30s MEANS AT END OF TEST."

Thanks again.

VO2 File.xlsm

Hi JosephP,
That was it!!!!!!! Thank you!!!!!