Forum Discussion
Allocations in excel
Hi,
Looking for some assistance in what I can describe as 'cell allocations' in excel. Basically want to perform a simple calculation, but once a cell has been used to calculate, move onto the next available cell within a dataset.
=IFERROR(C8/SUM(INDEX(A:A,7+SUM($D$7:D7)):INDEX(A:A,6+SUM($D$7:D8))),"")You can try this formula.
3 Replies
Thanks OliverScheurich that's worked perfectly!
Just one further question within your formula, what does the '7+SUM' & '6+SUM' relate to and how that functions within the formula?=INDEX(reference, row_num, [column_num], [area_num])
'7+SUM($D$7:D7)' & '6+SUM($D$7:D8)' refer to the row_num within the syntax of INDEX.
In cell E8 we want to sum the values from range A7:A8. Cell D8 tells that the first 2 values from column A should be added.
'7+SUM($D$7:D7)' evaluates to (row) 7 in cell E8 since SUM($D$7:D7) is 0.
Then INDEX(A:A,7) returns A7.
'6+SUM($D$7:D8)' evaluates to (row) 8 in cell E8 since SUM($D$7:D8) is 2.
Then INDEX(A:A,8) returns A8.
In the next step the SUM(A7:A8) is returned.
In cell E9 the row_num are '7+SUM($D$7:D8)' & '6+SUM($D$7:D9)' since the formula is filled down. '7+SUM($D$7:D8)' & '6+SUM($D$7:D9)' now evaluate to row 9 and 12 and then to range A9:A12 with INDEX(A:A,row_num).
=IFERROR(C8/SUM(INDEX(A:A,7+SUM($D$7:D7)):INDEX(A:A,6+SUM($D$7:D8))),"")You can try this formula.
