calculate days360 given an end date

[almouchie]

salam everyone
i would like some help : stock of computer bought & sold
column C column d
1/1/05 6/1/05
3/1/05 4/1/05
7/1/05
9/1/05 10/1/05
10/1/05 11/01/05
12/01/05 15/1/05
i would like to calculate the number of days between a date(i specify each time) & the x(i specify how many each time) number of computer that have been here the longest period.
ex. i have sold 3 computers on 10/1/05. i want to calculate number of days 4 each 3 computers between 10/1/05 & ( column d isnot empty) the the oldest 3 computer in my stock
is it any clear?
i would appreciate any hlep
thank in advance
RB

 

[fairwinds]

is it any clear?

No.

...so I'll guess:

Book1
	C	D	E	F	G	H
1	Bought	Sold		Noofcomp	Days
2	2005-01-01	2005-01-06		3	9
3	2005-01-03	2005-01-04		Date	7
4	2005-01-07			2005-01-10	1
5	2005-01-09	2005-01-10
6	2005-01-10	2005-01-11
7	2005-01-12	2005-01-15
Sheet4

 

[almouchie]

DEAR fairwinds
THANK very much it was a big help
when i tried to apply formula in G3 i got #NUM!
any idea why
another does it also work if add more stock of change it
thanks

 

[fairwinds]

Re #NUM!# error:
The formula needs to be confirmed with Ctrl + shift + enter, not just with enter. Enter the formula in G2 as explained above, then just drag it down.


To expand the stock you need to expand the ranges in the formulas but apart from that it should work.

 

[almouchie]

sorry i may be dense but its not working
i copied formula & pasted it in G2 but if i press clt+shift+enter all 2gether nothing happens , i tried draggin it b4 still the same problem #NUM!

IF I WERE TO EXPAND DATA WILL I HAVE TO CHANGE THE RANGEJIN FORMULA MANUALLY?

 

[fairwinds]

Replicate my layout exactly, however use the date formats that you usually do.

Then copy this:

=IF(ROW()-ROW($G$1)>$F$2,"",LARGE(($F$4-$C$2:$C$7)*($D$2:$D$7<>""),ROW()-ROW($G$1)))

and paste it in G2. Then change $C$7 and $D$7 refs in the formula so it covers the maximum dates that you can have. It does not matter if it goes further down than your dates if the number of dates can vary.
Then instead of pressing enter, you press Ctrl + shift + enter simultaneously. If done correctly, the formula will be embraced by curly brackets as you see in my exhibit. Then drag the formula down as far as you need.

 

[almouchie]

tx it worked
but i want computer sold on same day as invoice date to be included & i want computer sold after invoice date not to be included.
make any sense?
ex. consider invoice date is 23/01/05
if computer bought on 23/01/05 & sold 23/01/05
another computer bought on 23/01/05 but sold on 30/01/05
then the first computer will be considered being bought same day as invoice date but second computer will not as it was sold at date later then invoice date.
can i adjust that in the formula
thanks

 

[fairwinds]

Maybe like this:


F4:
=MIN(F2,SUMPRODUCT(--(C2:C7<=F6),--(D2:D7<>"")))


G2:
=IF(ROW()-ROW($G$1)>$F$4,"",LARGE(($F$6-$C$2:$C$7)*($D$2:$D$7<>""),ROW()-ROW($G$1)))
Ctrl + shift + enter and drag down.

Book1
	C	D	E	F	G
1	Bought	Sold		Noofcomp	Days
2	2005-01-01	2005-01-06		6	9
3	2005-01-03	2005-01-04		notoshow	7
4	2005-01-07			4	1
5	2005-01-09	2005-01-10		Date	0
6	2005-01-10	2005-01-11		2005-01-10
7	2005-01-12	2005-01-15
Sheet3

 

[almouchie]

it seems to still consider computers that have been sold after the date of invoice
how can i exclude them in the formula
invoice date 23/01/05
computer bought 23/01/05 sold 10/02/05
should not be considered as it was sold after the invoice date
any idea

 

[fairwinds]

Yes, I was checking the "bought" dates not the "sold"

F4: =MIN(F2,SUMPRODUCT(--(D2:D7<>""),--(D2:D7<=$F$6)))

G2: =IF(ROW()-ROW($G$1)>$F$4,"",LARGE(($F$6-$C$2:$C$7)*($D$2:$D$7<>"")*($D$2:$D$7<=$F$6),ROW()-ROW($G$1)))