Excel 2007 : Repeating Formula

Hi Again,

I am using this formula and it works but i need it follow the same pattern you can see below.

Is there an easier way to follow the same pattern without having the formula this long?

=IF(AND(C6>=J12,C6<J13),K12*C7,IF(AND(C6>=J13,C6<J14),K13*C7,IF(AND(C6>=J14,C6<J15),K14*C7,IF(AND(C6>=J15,C6<J16),K15*C7,IF(AND(C6>=J16,C6<J17),K16*C7,IF(AND(C6>=J16,C6<J17),K17*C7,IF(AND(C6>=J17,C6<J18),K18*C7,
)))))))

i still have many cells to cover using the same formula.


Thanks.

Mick

Do you mean this?

=C7*VLOOKUP(C6, $J$14:$K$18, 2)

Also, your formula has an error:


=IF(AND(C6>=J12,C6<J13),K12*C7,IF(AND(C6>=J13,C6<J14),K13*C7,IF(AND(C6>=J14,C6<J15),K14*C7,IF(AND(C6 >=J15,C6<J16),K15*C7,IF(AND(C6>=J16,C6<J17),K16*C7,IF(AND(C6>=J16,C6<J17),K17*C7,IF(AND(C6>=J17,C6<J 18),K18*C7,
)))))))

hi Mick2011,
When you select the cell with the formula it will highligh with boarder.
in the bottom right coener will be a small square.
click on hold and drag the forula down
Note if you dont want some of the cell references to change, first put a $ in from of the row or column value
eg =IF(AND(C6>=J$12,C6<$J$13),$K$12*C7,IF.......

c6 will change $j$13 wont

all got to start some where

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save