Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array formula
Hi,
I wonder if someone can help me i have the following formula: =IF($P20=1,IF($D20="",MAX('C:\unzipped\cycle\[cycle counts sep2005.xls]Recounted SKU''s'!$F$2:$F$400*IF('C:\unzipped\cycle\[cycle counts sep2005.xls]Recounted SKU''s'!$A$2:$A$400=B20,1,0)))) the purpose of the formula above is to bring back the last qty of a sku that has been counted, however this only works if the last qty is more than the previous, so for example: 1)SKU 125672 qty 120 units date 4/10/2005 2)SKU 126666 qty 110 date 5/10/2005 3)SKU 125672 qty 110 units date 6/10/2005 so in the example above excel would bring back number 1 as number 3 has less units A sku can be counted numerous times with the date changing dependant when it was counted. I therefore have a formula which brings back the last time the SKU was counted but i also want to bring back the qty. many thanks Gordon |
#2
|
|||
|
|||
Array formula
gordo wrote...
.... =IF($P20=1,IF($D20="",MAX('C:\unzipped\cycle\[cycle counts sep2005.xls]Recounted SKU''s'!$F$2:$F$400*IF('C:\unzipped\cycle\[cycle counts sep2005.xls]Recounted SKU''s'!$A$2:$A$400=B20,1,0)))) the purpose of the formula above is to bring back the last qty of a sku that has been counted, however this only works if the last qty is more than the previous, so for example: .... If you want the last quantity for a given SKU, you need a lookup of some sort rather than a MAX call. If your table were sorted in ascending order by date, SKUs were in column A and units sold in column F, then it could look something like =IF($P20=1,IF($D20="",LOOKUP(2,1/('<whatever'!$A$2:$A$400=B20), '<whatever'!$F$2:$F$400),""),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula | Excel Discussion (Misc queries) | |||
#VALUE! On An Array Formula Referencing a Range Outside The Workbo | Excel Discussion (Misc queries) | |||
problem with Array Formula | Excel Worksheet Functions | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |