Hi,
I am looking for a method to simplify my function, which I have seen to work fine, but is slowing down my sheet drastically since it is used for a lot of cells.
I am using my workbook to show the Cost of Goods Sold (COGS), and basing my calculation on a data sheet containing all incoming products:
Example of the data sheet (incoming products):
Order NO - Product Name - Incoming date - Qty - Value
Order 1 - Product A - 2022;01;01 - 30 pcs. - $100
Order 2 - Product A - 2022;02;01 - 40 pcs. - $104
Order 3. - Product A - 2022;03;01 - 20 pcs. - $95
Order 4. - Product A - 2022;04;01 - 70 pcs. - $101
The function I use asks: IF("Items sold until X date"<Index("Value";Small(If("Product Name"="Product A";Row("Incoming Date"));1)))
This function is short when asking for the first order of product A, but when I ask for order no. 10 it looks like this:
=IF(9000<
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));10))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));9))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));8))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));7))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));6))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));5))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));4))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));3))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));2))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming'!$S:$S));1));10;"")
Is there any way to shorten this in any way?
Thank you in advance
Best
Morten
I am looking for a method to simplify my function, which I have seen to work fine, but is slowing down my sheet drastically since it is used for a lot of cells.
I am using my workbook to show the Cost of Goods Sold (COGS), and basing my calculation on a data sheet containing all incoming products:
Example of the data sheet (incoming products):
Order NO - Product Name - Incoming date - Qty - Value
Order 1 - Product A - 2022;01;01 - 30 pcs. - $100
Order 2 - Product A - 2022;02;01 - 40 pcs. - $104
Order 3. - Product A - 2022;03;01 - 20 pcs. - $95
Order 4. - Product A - 2022;04;01 - 70 pcs. - $101
The function I use asks: IF("Items sold until X date"<Index("Value";Small(If("Product Name"="Product A";Row("Incoming Date"));1)))
This function is short when asking for the first order of product A, but when I ask for order no. 10 it looks like this:
=IF(9000<
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));10))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));9))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));8))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));7))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));6))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));5))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));4))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));3))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming’!$S:$S));2))+
INDEX('Master incoming'!$J:$J;SMALL(IF('Master incoming'!$G:$G='Inventory overview'!APT$2;ROW('Master incoming'!$S:$S));1));10;"")
Is there any way to shorten this in any way?
Thank you in advance
Best
Morten