Pfthb

I need to offset 1 column to the left from an Aggregate formula. Currently, the formula is returning the Kth smallest value IF a condition is met.

=AGGREGATE(15,6,$B$2:$C$1000/($A$2:$A$1000=$G$9),ROW(1:1))

Essentially if the condition is met, it divides by 1 resulting in the original value found by Aggregate. I want to obtain the value from 1 column over though and I am not sure how to do this.

Simply using:

=OFFSET(AGGREGATE(15,6,$B$2:$C$1000/($A$2:$A$1000=$G$9),ROW(1:1)),0,-1)

Does not work.

OFFSET works on a range, not a function. You need to wrap OFFSET around the range parameters of your aggregate function.

It is not clear from your question which ranges you want to offset by one column. An example to offset the parameter $G$9 by one column would be

=AGGREGATE(15,6,$B$2:$C$1000/($A$2:$A$1000=offset($G$9,0,-1)),ROW(1:1))

Note how the OFFSET applies to the range, not the function.

But if you use relative cell references for G9, you can simply copy the formula to a cell one to the left to achieve the same result.

For example, if this formula is in cell Z1 ...

=AGGREGATE(15,6,$B$2:$C$1000/($A$2:$A$1000=G9),ROW(1:1))

(note that the reference to G9 has no $ signs and thus is relative, so it changes when the formula is copied to a different cell)

... then you can have the result of the change with the Offset if you copy the formula to cell Y1.

A helper cell using this technique will be a lot faster than using OFFSET, since OFFSET is volatile and will cause a recalculation of the whole workbook whenever a single cell is changed. So, if you feel your workbook is slow, get rid of OFFSET and other volatile functions.