Calculate a price for a item which was rented on different day's

[ROMANVR]

I Have a item " spoon "
Price is €0.07 per day
One customer who has ordered 50 spoons on 1.11.20 ( and still has not returned them).
On the 7.11.20 he orders another 40 spoons.
On the 14.11.20 another 20.

On the 21.11.20 he returned 30 spoons ( I would charge him 21 days at € 0.07)
On the 28.11.20 he returned 30 spoons (I would charge him 20 spoons for 28 days and 10 spoons for 21 days) .
Basically I want to be able to calculate the price for a item withe a different start day.
Hope it makes sense and someone can help))

 

[maabadi]

Use this Formulas:
For first Value You can Use different formula, For others use G7 Formula

data validation list - Bkisley - 1.xlsm
	A	B	C	D	E	F	G	H
1			Item Number		Total
2	Date	Item Name	Ordered	Returned	Ordered	Returned
3	11/1/2020	Spoon	50		50	0	0
4	11/7/2020	Spoon	40		90	0	0
5	11/14/2020	Spoon	20		110	0	0
6	11/21/2020	Spoon	40	30	150	30	44.1
7	11/28/2020	Spoon		30	150	60	54.6
8	12/5/2020	Spoon		60	150	120	102.2
9
Sheet3

Cell Formulas
Range	Formula
E3:E8	E3	=SUM($C$3:C3)
F3:F8	F3	=SUM($D$3:D3)
G3:G6	G3	=((F3-IFNA(INDEX($A$3:$F$8,MATCH(F3,$E$3:$E$8,1),5),0))*(A3+1-INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1)+1,1),1))+IF(D3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0)<0,0,IF(D3-((F3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0)))>=INDEX($A$3:$F$8,MATCH(F3,$E$3:$E$8,1),3),INDEX($A$3:$F$8,MATCH(F3,$E$3:$E$8,1),3),D3-((F3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0)))))*(A3+1-INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),1))+IF(D3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0)<0,0,IF(D3-((F3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0)))-INDEX($A$3:$F$8,MATCH(F3,$E$3:$E$8,1),3)<0,0,(D3-(F3-IFNA(INDEX($A$3:$F$8,MATCH(F3,$E$3:$E$8,1),5),0))-INDEX($A$3:$F$8,MATCH(F3,$E$3:$E$8,1),3)))*(A3+1-INDEX($A$3:$F$8,MATCH(F3,$E$3:$E$8,1)-1,1))))*0.07
G7:G8	G7	=((F7-IFNA(INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),5),0))*(A7+1-INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1)+1,1))+IF(D7-((F7-IFNA(INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),5),0)))>=INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),3),INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),3),D7-((F7-IFNA(INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),5),0))))*(A7+1-INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),1))+IF(D7-((F7-IFNA(INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),5),0)))-INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),3)<0,0,(D7-(F7-IFNA(INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),5),0))-INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1),3))*(A7+1-INDEX($A$3:$F$8,MATCH(F7,$E$3:$E$8,1)-1,1))))*0.07

 

[maabadi]

Also You can Use this formula at G3 and Drag it Down until last row.

=(IF(F3<IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0),D3,(F3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0)))*(A3+1-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1)+1,1),1),0))+IF(F3<IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0),0,IF(D3-((F3-IFNA(INDEX($A$3:$F$8,MATCH(F3,$E$3:$E$8,1),5),0)))>=INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),0),3),INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),0),3),D3-((F3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0)))))*(A3+1-INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),1))+IF(F3<IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0),0,IF(D3-((F3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),0),5),0)))-INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),3)<0,0,(D3-(F3-IFNA(INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),5),0))-INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1),1),3))*(A3+1-INDEX($A$3:$F$8,IFNA(MATCH(F3,$E$3:$E$8,1)-1,1),1)))))*0.07

 

[Dave Patton]

To calculate the balance owing, you can consider the number of units out per day
You could calculate the units per day * rate and then sum that column.
or use a formula

T202012c.xlsm
	A	B	C	D	E	F
1	Customer	Item	Rate	Number	Date	Cumulative
2		spoon	0.07	50	1-Nov-20	50
3		spoon		40	7-Nov-20	90
4		spoon		20	14-Nov-20	110
5		spoon		-30	21-Nov-20	80
6				-30	28-Nov-20	50
7					1-Dec-20	50
8
9				50	30	168.70
10		Less payments				98.00
11		Balance				70.70
12
13		Daily charge		50		3.50
2b

Cell Formulas
Range	Formula
F2:F6	F2	=SUM($D$2:D2)
D9	D9	=SUM(D2:D7)
E9	E9	=E7-E2
F9	F9	=SUMPRODUCT(F2:F6,E3:E7-E2:E6)*rSpoon
F11	F11	=F9-F10
D13	D13	=D9
F13	F13	=rSpoon*D13

Named Ranges
Name	Refers To	Cells
rSpoon	='2b'!$C$2	F13, F9