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

jamilm

Well-known Member
Joined
Jul 21, 2011
Messages
740
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
ABCDEFG
1Person NameRegistration SequenceValueDesired ValueRank function
2James1210$21,140,0254th Smallest seq for Jamesfor 4th deduct the from previous one see formula$27,7904
3James1215$21,146,7355th Smallest seq for Jamessimilar as others deduct the previous smallest$6,7105
4James1134$21,107,4452nd smallest seq for Jamesfor second smallest decline meaning deduct the previous amount in first smallest$5,2102
5James1199$21,112,2353rd smallest seq for Jamesfor the third next smallest decline the previous one meaning deduct from 2nd$4,7903
6James1123$21,102,2351st smallest seq for Jamesfirst smallest return the whole amount$21,102,2351
7Sub total$105,608,675$21,146,735
8Richard1652$881,243$881,2431
9Richard1687$1,492,078$610,8352
10Sub total$2,373,321$1,492,078
11Robert469$1,055,874$1,055,8741
12Robert3011$1,082,895$8,0004
13Robert1976$1,057,418$1,5442
14Robert2939$1,074,895$17,4773
15Robert3359$1,083,740$8455
16Robert3646$1,083,240($500)6
17Sub total$6,438,062$1,083,240
Sheet1
Cell Formulas
RangeFormula
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)
 
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
CellFormula
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>

 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
ABCDEF
1Registration SequenceIDDesired ValuecommentRank
2James1210$21,140,025$37,7904
3James1215$21,146,735$6,7105
4James1134$21,101,735($500)Correct decrease because value of 1134 is 500 less than value ID 11232
5James1199$21,101,735($500)Incorrect because there is no decease between 1134 and 1199 and net is zero so here should be zero3
6James1123$21,102,235$21,102,2351
7Sub total$105,608,675 
8Richard1652$881,243$881,243
9Richard1687$1,492,078$610,835
10Sub total$2,373,321 
11Robert469$1,055,874$1,055,874
12Robert3011$1,082,895$8,000
13Robert1976$1,057,418$1,544
14Robert2939$1,074,895$17,477
15Robert3359$1,083,740$845
16Robert3646$1,083,240($500)
17Sub total$6,438,062 
Sheet1
Cell Formulas
RangeFormula
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

ABCD
Person Name
James
James
James
James
James
Sub total
Richard
Richard
Sub total
Robert
Robert
Robert
Robert
Robert
Robert
Sub total

<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>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]Registration Sequence[/TD]
[TD="align: right"]Value[/TD]
[TD="align: right"]Desired Value[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]1210[/TD]
[TD="align: right"]$21,140,025 [/TD]
[TD="align: right"]$27,790 [/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]1215[/TD]
[TD="align: right"]$21,146,735 [/TD]
[TD="align: right"]$6,710 [/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]1134[/TD]
[TD="align: right"]$21,107,445 [/TD]
[TD="align: right"]$5,210 [/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]1199[/TD]
[TD="align: right"]$21,112,235 [/TD]
[TD="align: right"]$4,790 [/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]1123[/TD]
[TD="align: right"]$21,102,235 [/TD]
[TD="align: right"]$21,102,235 [/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"]$105,608,675 [/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: right"]1652[/TD]
[TD="align: right"]$881,243 [/TD]
[TD="align: right"]$881,243 [/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: right"]1687[/TD]
[TD="align: right"]$1,492,078 [/TD]
[TD="align: right"]$610,835 [/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: right"]$2,373,321 [/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: right"]469[/TD]
[TD="align: right"]$1,055,874 [/TD]
[TD="align: right"]$1,055,874 [/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="align: right"]3011[/TD]
[TD="align: right"]$1,082,895 [/TD]
[TD="align: right"]$8,000 [/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: right"]1976[/TD]
[TD="align: right"]$1,057,418 [/TD]
[TD="align: right"]$1,544 [/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="align: right"]2939[/TD]
[TD="align: right"]$1,074,895 [/TD]
[TD="align: right"]$17,477 [/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="align: right"]3359[/TD]
[TD="align: right"]$1,083,740 [/TD]
[TD="align: right"]$845 [/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]

[TD="align: right"]3646[/TD]
[TD="align: right"]$1,083,240 [/TD]
[TD="align: right"]($500)[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]

[TD="align: right"]$6,438,062 [/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
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
ABCD
1Registration SequenceIDDesired Value?
2James1210$21,140,025$38,290
3James1215$21,146,735$6,710
4James1134$21,101,735($500)
5James1199$21,101,735$0
6James1123$21,102,235$21,102,235
7Sub total$105,608,675$105,608,675
8Richard1652$881,243$881,243
9Richard1687$1,492,078$610,835
10Sub total$2,373,321$2,373,321
11Robert469$1,055,874$1,055,874
12Robert3011$1,082,895$8,000
13Robert1976$1,057,418$1,544
14Robert2939$1,074,895$17,477
15Robert3359$1,083,740$845
16Robert3646$1,083,240($500)
17Sub 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
ABCD
1Registration SequenceIDDesired Value?
2James1210$21,140,025$38,290
3James1215$21,146,735$6,710
4James1134$21,101,735($500)
5James1199$21,101,735$0
6James1123$21,102,235$21,102,235
7Richard1652$881,243$881,243
8Richard1687$1,492,078$610,835
9Robert469$1,055,874$1,055,874
10Robert3011$1,082,895$8,000
11Robert1976$1,057,418$1,544
12Robert2939$1,074,895$17,477
13Robert3359$1,083,740$845
14Robert3646$1,083,240($500)
Rank & Diff (3)
 
Upvote 0
:warning: 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.
 
Upvote 0
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:p></o:p>



=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)
 
Upvote 0
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.


:warning: 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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top