Formula to split total amount from one cell between two other cells based on the value in another cell.

[Detectiveclem]

I am trying to find a formula which will allow the following

In cell M7 I have a sumif total based on data entered from an associated range.

The figure in M7 will increase as entries are made on the associated range.

Using the total amount in M7 I want this amount to be split into two separate cells based upon the value in another cell as follows.

M17 based on a value less than held in cell N38 and

M18 based on the value more than in cell N39.



I.E.

If M7 = £45,000 and N38 = £50,000 then M17 will display £45,000 and M18 will display £0

but if M7= £95,555 and N38 still = £50,000 then M17 will display £50,000 and M18 will display the remaining balance of £45,555



Any help would be greatly appreciated.

 

[Sufiyan97]

Try

Book2
	L	M	N	O
4
5
6
7		45000
8
9
10
11
12
13
14
15
16
17		45000
18		5000
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38			50000
39
40
Sheet1

Cell Formulas
Range	Formula
M17	M17	=MIN(M7,N38)
M18	M18	=MAX(N38,M7)-MIN(N38,M7)

 

[Detectiveclem]

sufiyan97, worked perfectly, thank you so much.

 

[Sufiyan97]

You're Welcome!