LinkBack URL
About LinkBacks
Find attached , expected result in on w2 needs to be 9 that I have punched manually
Find attached , expected result in on w2 needs to be 9 that I have punched manually
Why it have to be 9. What is logic or calculation?
Appreciate the help? CLICK *
Its not sold for 9 months after it sold once , but it did not even sale for another 5 months when it sold once in june
If you mean blank cells after last sale then try this array formula
=15-MAX(ISNUMBER(C3:Q3)*(COLUMN(C3:Q3)-2))
Hit Ctrl+Shift+Enter.
Thus the formula should be looking into a range c2 to q2
Wow this is it , oops this would have taken ages without the formula for stocks items over 100,000 thus ,
With this i shall exactly be able to filter all goods as dead stock for kill pricing
You are welcome. Enjoy it
AZ-XL , you saved my day , I shall be using it for stock items too many , it should be doing the magic i need
AZ-XLS , i was wondering if you can add added magic to the formula , the formula should only perform if the stock
that is s3 , can you also help me with =count(c3:q3) to count if stock is greater than 0 , i am poor at combining the if into a formula
array formula to perform if s3 is greater than 0 , otherwise leave blank
do you mean this?
=IF(S3>0,15-MAX(ISNUMBER(C3:Q3)*(COLUMN(C3:Q3)-2)),"")
AZ - XL
The formula is not functioning exactly
sales no sales blank for
REF DESCRIPTION 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 TOT QTY % COUNT COUNT COUNT
106.002.20 SOAP DISPENSER 3901 1 1 2 0 100% 2 13 3
Its counting three while there were 9 months prior that stock did not sell
This means the formula is functional on the last sell , which is not correct
I dont know how to re attach the file again thus re attaching
sales no sales blank for
REF DESCRIPTION 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 TOT QTY % COUNT COUNT COUNT
x 1 x x x x x x x x x 1 x x x 2 0 100% 2 13 3
Formula was counting after last realized sale. From your last post I couldnt get the point. Can you post sample file again with all variations and desired results?
Expected result on above raw data 3 is not correct , need 9 , this means it should count the max continous not sold , the formula is
doing the task on the last sale , instead it should look into regardless last sell be it even previous
use this to paste on the same file
x 1 x x x x x x x x x 1 x x x
quote answer with if stock is above 0 and also quote with regardless stock
i don't know to re attach , thus I have given you raw data sales 15 month sales as below
x 1 x x x x x x x x x 1 x x x
x meaning no sales , desired result 9
Maybe this?
=IF(S3>0,MAX(LARGE(IF(ISNUMBER(C3:Q3),COLUMN(C3:Q3),0),ROW(INDIRECT("1:"&SUM(--(IF(ISNUMBER(C3:Q3),COLUMN(C3:Q3),0)>0)))))-LARGE(IF(ISNUMBER(C3:Q3),COLUMN(C3:Q3),0),ROW(INDIRECT("2:"&(SUM(--(IF(ISNUMBER(C3:Q3),COLUMN(C3:Q3),0)>0))+1))))),"")
Forget the previous formula.
=IF(S3>0,MAX(FREQUENCY(IF(ISBLANK(C3:Q3),COLUMN(C3:Q3)),IF(ISNUMBER(C3:Q3),COLUMN(C3:Q3)))),"")
Yes this is it , it leaves the cells blank exactly as i need , thank you again , please quote me the same formula without the if too , may use it somewhere too
You have sorted me out very heavily , must say a double thank you
This is without if
MAX(FREQUENCY(IF(ISBLANK(C3:Q3),COLUMN(C3:Q3)),IF(ISNUMBER(C3:Q3),COLUMN(C3:Q3)))) Ctrl+Shift+Enter
Your are welcome. If your problem has solved then mark the thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks