Forum Discussion

afleming720's avatar
afleming720
Copper Contributor
Aug 09, 2023
Solved

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 nex...
BI & Data Analysis
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Aug 09, 2023

    afleming720 

    =IFERROR(C8/SUM(INDEX(A:A,7+SUM($D$7:D7)):INDEX(A:A,6+SUM($D$7:D8))),"")

    You can try this formula. 

     

afleming720's avatar
afleming720
Copper Contributor
Aug 10, 2023

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? 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 10, 2023

    afleming720 

    =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).

     

Resources