how copy formula that contains ranges so ranges do not overlap

P

Patty

I have a formula on one worksheet that finds the max value of 3 cells on
another worksheet. I want the formula below it to move to the next three
cells not overlap the rows. When I drag them it copies overlapping cells.
What I am looking for is example: =MAX(Spread!B118:B120) next row the formula
would be =MAX(Spread!B121:B123) how do you do that where you do not have to
enter each formula for each row?
thank you
 
M

Mike H

Patty,

Try this

This does B118 - B120 but dragged down 1 cell becomes B121 B123
=MAX(OFFSET(Spread!B$118,(ROWS($1:1)-1)*3,,3))

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how can i networkdays date ranges where dates overlap 1
a data validation list that contains a list of named ranges - not alist from a named range 1
Autofill cell references with a pattern of every 110 rows 3
named ranges 4
Number of days in overlapping date ranges (using array formula?) 7
CountIf previous cell formula 0
Problem with dynamic ranges 1
how do i copy the result of a formula into another worksheet as te 3

Top