I have a table as shown in figure.
It is about the transaction of a few items with date, qty and rate. Column E calculates the total cost filling the column automatically as new entry is typed.
Column F sums up the item quantity and column G calculates the current rate of the item for each row. What I want is that column also gets filled with a single formula in cells F3 and G3, as in the case of column E. Can it be done? Can an array formula do the trick?
Regards, Pravin Kumar.
it's called resetting running total...
use in F3:
=INDEX(MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>"")))))>=ROW(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>"")))))*(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>"")))=TRANSPOSE(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>""))))),
INDIRECT("C3:C"&MAX(ROW(B3:B)*(B3:B<>""))), 0)), ROW(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>""))))^0))
and use in G3:
=INDEX(MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>"")))))>=ROW(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>"")))))*(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>"")))=TRANSPOSE(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>""))))),
INDIRECT("D3:D"&MAX(ROW(B3:B)*(B3:B<>""))), 0)), ROW(
INDIRECT("B3:B"&MAX(ROW(B3:B)*(B3:B<>""))))^0)/FILTER(F3:F, F3:F<>""))