Need help with formula Rank and CountIF to decline the previous value

[jamilm]

Gurus,

there might be a very simple solution but looks very tricky.

please see the attached file https://app.box.com/s/o8elz68uudeszbjj5jrm

I have names in the Column A and then the Registration Sequence in column B which is always numbers. in column C i have extracted values from SQL and and since these values are wrong becuase it does not decline the previous registration seq amount. so what i need is that on the next sequence for the same person, it must decline the previous amount and only put the difference either increase or decrese from the previous value. please see the excel file and the desired value column. please let me know, if this is unclear and further elaboration is needed.

thank you very much for your help as usual.

Excel 2010
	A	B	C	D	E	F	G
1	Person Name	Registration Sequence	Value			Desired Value	Rank function
2	James	1210	$21,140,025	4th Smallest seq for James	for 4th deduct the from previous one see formula	$27,790	4
3	James	1215	$21,146,735	5th Smallest seq for James	similar as others deduct the previous smallest	$6,710	5
4	James	1134	$21,107,445	2nd smallest seq for James	for second smallest decline meaning deduct the previous amount in first smallest	$5,210	2
5	James	1199	$21,112,235	3rd smallest seq for James	for the third next smallest decline the previous one meaning deduct from 2nd	$4,790	3
6	James	1123	$21,102,235	1st smallest seq for James	first smallest return the whole amount	$21,102,235	1
7	Sub total		$105,608,675			$21,146,735
8	Richard	1652	$881,243			$881,243	1
9	Richard	1687	$1,492,078			$610,835	2
10	Sub total		$2,373,321			$1,492,078
11	Robert	469	$1,055,874			$1,055,874	1
12	Robert	3011	$1,082,895			$8,000	4
13	Robert	1976	$1,057,418			$1,544	2
14	Robert	2939	$1,074,895			$17,477	3
15	Robert	3359	$1,083,740			$845	5
16	Robert	3646	$1,083,240			($500)	6
17	Sub total		$6,438,062			$1,083,240
Sheet1

Cell Formulas
Range	Formula
C7	=SUM(C2:C6)
C10	=SUM(C8:C9)
C17	=SUM(C11:C16)
G8	=RANK(B8,$B$8:$B$9,1)
G9	=RANK(B9,$B$8:$B$9,1)
G11	=RANK(B11,$B$11:$B$16,1)
G12	=RANK(B12,$B$11:$B$16,1)
G13	=RANK(B13,$B$11:$B$16,1)
G14	=RANK(B14,$B$11:$B$16,1)
G15	=RANK(B15,$B$11:$B$16,1)
G16	=RANK(B16,$B$11:$B$16,1)
G2	=RANK(B2,$B$2:$B$6,1)
G3	=RANK(B3,$B$2:$B$6,1)
G4	=RANK(B4,$B$2:$B$6,1)
G5	=RANK(B5,$B$2:$B$6,1)
G6	=RANK(B6,$B$2:$B$6,1)
F2	=C2-C5
F3	=C3-C2
F4	=C4-C6
F5	=C5-C4
F6	=C6
F7	=SUM(F2:F6)
F8	=C8
F9	=C9-C8
F10	=SUM(F8:F9)
F11	=C11
F12	=C12-C14
F13	=C13-C11
F14	=C14-C13
F15	=C15-C12
F16	=C16-C15
F17	=SUM(F11:F16)

 

[Peter_SSs]

Is this sufficient for your needs?
Both formulas copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	Person Name	Registration Sequence	Value			Desired Value	Rank function
2	James	1210	$21,140,025			$27,790	James|4
3	James	1215	$21,146,735			$6,710	James|5
4	James	1134	$21,107,445			$5,210	James|2
5	James	1199	$21,112,235			$4,790	James|3
6	James	1123	$21,102,235			$21,102,235	James|1
7	Sub total		$105,608,675			$21,146,735
8	Richard	1652	$881,243			$881,243	Richard|1
9	Richard	1687	$1,492,078			$610,835	Richard|2
10	Sub total		$2,373,321			$1,492,078
11	Robert	469	$1,055,874			$1,055,874	Robert|1
12	Robert	3011	$1,082,895			$8,000	Robert|4
13	Robert	1976	$1,057,418			$1,544	Robert|2
14	Robert	2939	$1,074,895			$17,477	Robert|3
15	Robert	3359	$1,083,740			$845	Robert|5
16	Robert	3646	$1,083,240			($500)	Robert|6
17	Sub total		$6,438,062			$1,083,240
Rank & Diff

 

[jamilm]

Dear Peter,

this is perfect and works perfectly. thanks a billion. very much appreciated.

just one more thing. i do not need the rank column of G. can somehow we merge this two formula in one so that i only put the formula in F column and i would get my result?

i also do not need subtotals. the reason i previously put column G and subtotal. so that it would make my question clear in forum. otherwise my actual data is just like this shown in screenshot below.

Excel 2010
	A	B	C	D
1	Person Name	Registration Sequence	Value	Desired Value
2	James	1210	$21,140,025	$27,790
3	James	1215	$21,146,735	$6,710
4	James	1134	$21,107,445	$5,210
5	James	1199	$21,112,235	$4,790
6	James	1123	$21,102,235	$21,102,235
7	Richard	1652	$881,243	$881,243
8	Richard	1687	$1,492,078	$610,835
9	Robert	469	$1,055,874	$1,055,874
10	Robert	3011	$1,082,895	$8,000
11	Robert	1976	$1,057,418	$1,544
12	Robert	2939	$1,074,895	$17,477
13	Robert	3359	$1,083,740	$845
14	Robert	3646	$1,083,240	($500)
Sheet1

Is this sufficient for your needs?
Both formulas copied down.

Rank & Diff

*	A	B	C	D	E	F	G
1	Person Name	Registration Sequence	Value	*	*	Desired Value	Rank function
2	James	1210	$21,140,025	*	*	$27,790	James|4
3	James	1215	$21,146,735	*	*	$6,710	James|5
4	James	1134	$21,107,445	*	*	$5,210	James|2
5	James	1199	$21,112,235	*	*	$4,790	James|3
6	James	1123	$21,102,235	*	*	$21,102,235	James|1
7	Sub total	*	$105,608,675	*	*	$21,146,735	*
8	Richard	1652	$881,243	*	*	$881,243	Richard|1
9	Richard	1687	$1,492,078	*	*	$610,835	Richard|2
10	Sub total	*	$2,373,321	*	*	$1,492,078	*
11	Robert	469	$1,055,874	*	*	$1,055,874	Robert|1
12	Robert	3011	$1,082,895	*	*	$8,000	Robert|4
13	Robert	1976	$1,057,418	*	*	$1,544	Robert|2
14	Robert	2939	$1,074,895	*	*	$17,477	Robert|3
15	Robert	3359	$1,083,740	*	*	$845	Robert|5
16	Robert	3646	$1,083,240	*	*	($500)	Robert|6
17	Sub total	*	$6,438,062	*	*	$1,083,240	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 106px;"><col style="width: 155px;"><col style="width: 105px;"><col style="width: 25px;"><col style="width: 26px;"><col style="width: 112px;"><col style="width: 112px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 =IF(G2="",SUMIF(A$1:A1,A1,F$1:F1),C2-IF(RIGHT(G2,2)="|1",0,INDEX(C$2:C$17,MATCH(A2&"|"&REPLACE(G2,1,FIND("|",G2),"")-1,G$2:G$17,0)))) G2 =IF(A2="Sub Total","",A2&"|"&SUMPRODUCT((A$2:A$17=A2)*(B2>B$2:B$17))+1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

i do not need the rank column of G. can somehow we merge this two formula in one so that i only put the formula in F column and i would get my result?

i also do not need subtotals.

It can be done without the helper column - see my formula in D2 below. However, this is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down. Array formulas tend to use more resources than standard formulas and if you edit them you have to remember to re-commit them with Ctrl+Shift+Enter.

An alternative would be to use the helper column, thereby keeping the formulas a little more straight-forward, and just hide the helper column after you have populated it with the formulas. If considering this approach I have posted the modified formula to leave out the subtotals.

Excel Workbook
	A	B	C	D	E	F	G
1	Person Name	Registration Sequence	Value	Desired Value		Desired Value	Rank function
2	James	1210	$21,140,025	$27,790		$27,790	James|4
3	James	1215	$21,146,735	$6,710		$6,710	James|5
4	James	1134	$21,107,445	$5,210		$5,210	James|2
5	James	1199	$21,112,235	$4,790		$4,790	James|3
6	James	1123	$21,102,235	$21,102,235		$21,102,235	James|1
7	Sub total		$105,608,675
8	Richard	1652	$881,243	$881,243		$881,243	Richard|1
9	Richard	1687	$1,492,078	$610,835		$610,835	Richard|2
10	Sub total		$2,373,321
11	Robert	469	$1,055,874	$1,055,874		$1,055,874	Robert|1
12	Robert	3011	$1,082,895	$8,000		$8,000	Robert|4
13	Robert	1976	$1,057,418	$1,544		$1,544	Robert|2
14	Robert	2939	$1,074,895	$17,477		$17,477	Robert|3
15	Robert	3359	$1,083,740	$845		$845	Robert|5
16	Robert	3646	$1,083,240	($500)		($500)	Robert|6
17	Sub total		$6,438,062
Rank & Diff

BTW, best not to fully quote full posts, especially if you are not referring to anything specific in them, as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.

 

[jamilm]

OK. understood. will not qoute full posts.

BTW, best not to fully quote full posts, especially if you are not referring to anything specific in them, as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.

 

[jamilm]

Peter, thanks very much i liked the updated array formula =IF(A2="Sub Total","",C2-IFERROR(INDEX(C$2:C$17,MATCH(MAX(IF(A$2:A$17=A2,IF(B$2:B$17<B2,B$2:B$17))),IF(A$2:A$17=A2,B$2:B$17),0)),0))

although, i finished watching Mike's DVD videos CONTROL SHIFT ENTER which is very good tutorials of Array Formula. but still i could not figure out this sophisticated formula as you did.

I owe you a bottle of champagne.

 

[jamilm]

a big like to this post of Peter. very helpful formula.

 

[Peter_SSs]

I owe you a bottle of champagne.

I'll collect next time I'm in Manhattan.

 

[Peter_SSs]

Reconsidering, since you appear to be using Excel 2010 you could use this normal entry formula directly ..

Excel Workbook
	A	B	C	D
1	Person Name	Registration Sequence	Value	Desired Value
2	James	1210	$21,140,025	$27,790
3	James	1215	$21,146,735	$6,710
4	James	1134	$21,107,445	$5,210
5	James	1199	$21,112,235	$4,790
6	James	1123	$21,102,235	$21,102,235
7	Sub total		$105,608,675
8	Richard	1652	$881,243	$881,243
9	Richard	1687	$1,492,078	$610,835
10	Sub total		$2,373,321
11	Robert	469	$1,055,874	$1,055,874
12	Robert	3011	$1,082,895	$8,000
13	Robert	1976	$1,057,418	$1,544
14	Robert	2939	$1,074,895	$17,477
15	Robert	3359	$1,083,740	$845
16	Robert	3646	$1,083,240	($500)
17	Sub total		$6,438,062
Rank & Diff

 

[jamilm]

Sure. let me know please, when you are in Manhattan

I'll collect next time I'm in Manhattan.