Hi
I'm trying to create a sum in Column C that divides column A by column B over 20 rows. Data in Column A is A1:A20 but column B increase by 4 rows everytime, e.g. B1, B5, B9 etc.
If i copy the formula down, how can i get it to add the 4 rows in B rather than add one?
thanks
Gordon
Can you give a more detailed example?
Do you mean you currently have formulas in B1, B5 and B9, but you want them in B1, B2 and B3 instead? If so, what exactly is each cell supposed to calculate from A1:A20 range?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
sorry, didn't make it clear!
i have data in A1:A20 and in B1, B5 and B9
in C1 i want formula =A1/B1
then in C2 =A1/B5
and so on
how do i get the formula to continue down adding 4 rows to B whilst only adding 1 to A?
thanks
For that pattern you describe, try in C1:
=INDEX($A$1:$A$20,ROUNDUP(ROWS($A$1:A1)/3,0))/INDEX($B$1:$B$20,4*MOD(ROW()-1,5)+1)
copied down.
Last edited by NBVC; 06-23-2011 at 09:57 AM. Reason: Slight adjustment
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks