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)

 

[jamilm]

Thank you very much Peter. this is even better and faster and does not require CTRL SHIFT ENTER

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

Spreadsheet Formulas

Cell Formula D2 =IF(A2="Sub Total","",C2-AGGREGATE(14,6,(A$2:A$17=A2)*(B$2:B$17<b2)< span="">*(C$2:C$17),1)</b2)<>) <tbody> </tbody>

<tbody>
</tbody>

 

[jamilm]

Hi Peter,

i noticed one problem that i could not fix by myself, i tried.

please see below table. while the formula works when the subsequent numbers are increasing, however if there is a decrease then only the first occurence calculate the correct negative and then the next one when there is no decrease then it again shows a duplicate negative. while it should not.

i would greatly appreciate your help on this.

Excel 2010
	A	B	C	D	E	F
1	Registration Sequence	ID	Desired Value		comment	Rank
2	James	1210	$21,140,025	$37,790		4
3	James	1215	$21,146,735	$6,710		5
4	James	1134	$21,101,735	($500)	Correct decrease because value of 1134 is 500 less than value ID 1123	2
5	James	1199	$21,101,735	($500)	Incorrect because there is no decease between 1134 and 1199 and net is zero so here should be zero	3
6	James	1123	$21,102,235	$21,102,235		1
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
Sheet1

Cell Formulas
Range	Formula
F2	=RANK(B2,$B$2:$B$6,1)
F3	=RANK(B3,$B$2:$B$6,1)
F4	=RANK(B4,$B$2:$B$6,1)
F5	=RANK(B5,$B$2:$B$6,1)
F6	=RANK(B6,$B$2:$B$6,1)
D2	=IF(A2="Sub Total","",C2-AGGREGATE(14,6,(A$2:A$17=A2)*(B$2:B$17)*(C$2:C$17),1))
D3	=IF(A3="Sub Total","",C3-AGGREGATE(14,6,(A$2:A$17=A3)*(B$2:B$17)*(C$2:C$17),1))
D4	=IF(A4="Sub Total","",C4-AGGREGATE(14,6,(A$2:A$17=A4)*(B$2:B$17)*(C$2:C$17),1))
D5	=IF(A5="Sub Total","",C5-AGGREGATE(14,6,(A$2:A$17=A5)*(B$2:B$17)*(C$2:C$17),1))
D6	=IF(A6="Sub Total","",C6-AGGREGATE(14,6,(A$2:A$17=A6)*(B$2:B$17)*(C$2:C$17),1))
D7	=IF(A7="Sub Total","",C7-AGGREGATE(14,6,(A$2:A$17=A7)*(B$2:B$17)*(C$2:C$17),1))
D8	=IF(A8="Sub Total","",C8-AGGREGATE(14,6,(A$2:A$17=A8)*(B$2:B$17)*(C$2:C$17),1))
D9	=IF(A9="Sub Total","",C9-AGGREGATE(14,6,(A$2:A$17=A9)*(B$2:B$17)*(C$2:C$17),1))
D10	=IF(A10="Sub Total","",C10-AGGREGATE(14,6,(A$2:A$17=A10)*(B$2:B$17)*(C$2:C$17),1))
D11	=IF(A11="Sub Total","",C11-AGGREGATE(14,6,(A$2:A$17=A11)*(B$2:B$17)*(C$2:C$17),1))
D12	=IF(A12="Sub Total","",C12-AGGREGATE(14,6,(A$2:A$17=A12)*(B$2:B$17)*(C$2:C$17),1))
D13	=IF(A13="Sub Total","",C13-AGGREGATE(14,6,(A$2:A$17=A13)*(B$2:B$17)*(C$2:C$17),1))
D14	=IF(A14="Sub Total","",C14-AGGREGATE(14,6,(A$2:A$17=A14)*(B$2:B$17)*(C$2:C$17),1))
D15	=IF(A15="Sub Total","",C15-AGGREGATE(14,6,(A$2:A$17=A15)*(B$2:B$17)*(C$2:C$17),1))
D16	=IF(A16="Sub Total","",C16-AGGREGATE(14,6,(A$2:A$17=A16)*(B$2:B$17)*(C$2:C$17),1))
D17	=IF(A17="Sub Total","",C17-AGGREGATE(14,6,(A$2:A$17=A17)*(B$2:B$17)*(C$2:C$17),1))

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

Rank & Diff

	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

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

Spreadsheet Formulas

Cell Formula D2 =IF(A2="Sub Total","",C2-AGGREGATE(14,6,(A$2:A$17=A2)*(B$2:B$17<b2)< span="">*(C$2:C$17),1)</b2)<>) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Firstly, two general posting hints:
1. A reminder about quoting posts. Here you quoted an earlier post of mine, but quoting it does nothing to add to your description of the new requirement, it just makes another screen length that readers have to scroll through to get to the bottom of your post.
2. Investigate the options when using the HTML Maker to post your own screen shots. Here you have posted every formula relating to your screen shot when the D2 and F2 formulas would have done since the rest are just copied down. Again it just adds several inches more of scrolling without adding anything of value. Have a look at the options in the HTML Maker for "First Cell In Each Column", "User Defined Selection" etc.


Now to your actual question, which I must admit I found a little confusing.
a) You have a heading "Desired Value" in C1, but I have deduced that you really meant column D for that heading?
b) You have pointed out a wrong value in row 5 but it appears to me the very first value (D2) is also incorrect but was not mentioned. If your D2 value is correct, then please explain why.


Anyway, this is my attempt at what I think you want.
Formula in D2 copied down (note: only showing 1 formula, not all )

Excel Workbook
	A	B	C	D
1	Registration Sequence	ID		Desired Value?
2	James	1210	$21,140,025	$38,290
3	James	1215	$21,146,735	$6,710
4	James	1134	$21,101,735	($500)
5	James	1199	$21,101,735	$0
6	James	1123	$21,102,235	$21,102,235
7	Sub total		$105,608,675	$105,608,675
8	Richard	1652	$881,243	$881,243
9	Richard	1687	$1,492,078	$610,835
10	Sub total		$2,373,321	$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	$6,438,062
Rank & Diff (2)

On re-reading the thread I noticed that earlier you said that you didn't really want/need the Sub Total rows. If that is the case, the formula gets a little simpler.

Excel Workbook
	A	B	C	D
1	Registration Sequence	ID		Desired Value?
2	James	1210	$21,140,025	$38,290
3	James	1215	$21,146,735	$6,710
4	James	1134	$21,101,735	($500)
5	James	1199	$21,101,735	$0
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)
Rank & Diff (3)

 

[Peter_SSs]

If it is possible that two people could have the same ID in column B (eg If James and Robert both had a row with ID = 1215), then my formula will return some incorrect results. If that is possible, post back & I will re-think options.

 

[jamilm]

Hi Peter,
thank you very much. your formula quoted worked perfectly. thanks alot.

your comments are well noted, I apologies if I caused any inconvenience to you.<o></o>

=C2-IFERROR(INDEX(C$2:C$14,MATCH(AGGREGATE(14,6,(A$2:A$14=A2)*(B$2:B$14<B2)*(B$2:B$14),1),B$2:B$14,0)),0)

 

[jamilm]

thank you Peter. luckly the IDs can never be duplicate, for example James and Robert can never have 1215, becuase the IDs are always unique values.

so you rformula serves my need perfectly

i wish you a wonderful day.

If it is possible that two people could have the same ID in column B (eg If James and Robert both had a row with ID = 1215), then my formula will return some incorrect results. If that is possible, post back & I will re-think options.

 

[Peter_SSs]

Glad that worked for you.

BTW, nice quotes.