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.
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) | |
